Re: Simple SQL too slow

Le 2 juillet 2017 10:39:09 GMT+02:00, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> a écrit :
>
>
>On 07/02/2017 03:26 AM, Daviramos Roussenq Fortunato wrote:
>> I am using pgAdmin for SQL test.
>>
>> Linux:
>>
>> EXPLAIN ANALYZE select * from
>> "Seq Scan on lancamentosteste (cost=0.00..49289.74 rows=1883774
>> width=92) (actual time=0.016..1194.453 rows=1883699 loops=1)"
>> "Total runtime: 2139.067 ms"
>>
>> Windows:
>> "Seq Scan on lancamentosteste (cost=0.00..49288.67 rows=1883967
>> width=92) (actual time=0.036..745.409 rows=1883699 loops=1)"
>> "Total runtime: 797.159 ms"
>>
>
>I'm really, really confused. In the first message you claimed the
>queries take 7 and 3 minutes, yet here we see the queries taking just a
>
>few seconds.
>
>>
>>
>> I did some test reading the disk and monitored with iotop.
>>
>> #hdparm -t /dev/sdc
>>
>> /dev/sdc:
>> Timing buffered disk reads: 730 MB in 3.01 seconds = 242.65 MB/sec
>>
>> #hdparm -T /dev/sdc
>>
>> /dev/sdc:
>> Timing cached reads: 9392 MB in 2.00 seconds = 4706.06 MB/sec
>> #time sh -c "dd if=/dev/zero of=ddfile bs=8k count=250000 && sync";
>rm
>> ddfile
>> 250000+0 registros de entrada
>> 250000+0 registros de saída
>> 2048000000 bytes (2,0 GB) copiados, 5,84926 s, 350 MB/s
>>
>> real 0m9.488s
>> user 0m0.068s
>> sys 0m5.488s
>>
>>
>> In the tests monitoring the disk by iotop, it kept constant the
>reading
>> between 100MB/s to 350MB/s
>>
>> By doing the same monitoring on iotop and running SELECT, the disk
>> reading does not exceed 100kb/s, I have the impression that some
>> configuration of LINUX or Postgres is limiting the use of the total
>> capacity of DISCO.
>>
>> Does anyone know if there is any setting for this?
>>
>
>There is no such setting. But it's possible that the network is very
>slow, so transferring the results from the server to the client takes
>very long. Or that formatting the results in the client takes a lot of
>time (I'm not sure why there'd be a difference between Windows and
>Linux
>though).

Could it be that you are doing your queries on pgadmin that is remote from the Linux server, and local to the windows server, hence the difference in perceived performance?

Nicolas
>
>regards

--
Envoyé de mon appareil Android avec K-9 Mail. Veuillez excuser ma brièveté.