Re: [PERFORM] Simple SQL too slow

Le 2 juillet 2017 10:39:09 GMT+02:00, Tomas Vondra <tomas.vondra@2ndquadrant.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é.