Re: Simple SQL too slow

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 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?

2017-07-01 18:17 GMT-03:00 Julien Rouhaud <julien(dot)rouhaud(at)dalibo(dot)com>:

> On 01/07/2017 22:58, Tomas Vondra wrote:
> > After that, you need to determine what is the bottleneck. Perhaps the
> > resources are saturated by something else running on the system - other
> > queries, maybe something else running next to PostgreSQL. Look at top
> > and iotop while running the queries, and other system tools.
> >
>
> Another explanation would be network issue. Are they stored in
> different locations? And dhoes
>
> EXPLAIN ANALYZE select * from MINHATABELA
>
> has similar timings on both environment?
>
> Also, I didn't see any indication about how exactly were the tests
> performed. Was it using psql, pgAdmin or something else ?
>
> --
> Julien Rouhaud
> http://dalibo.com - http://dalibo.org
>

--
Atenciosamente
Daviramos Roussenq Fortunato