Re: Simple SQL too slow

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).

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services