public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nicolas CHARLES <[email protected]>
To: [email protected]
To: Tomas Vondra <[email protected]>
Subject: Re: Simple SQL too slow
Date: Sun, 02 Jul 2017 12:01:44 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CABFgOvotE9gMEkUrBwMPRs60jDiBaX5TXBiVY9jUnjHmYVRXFw@mail.gmail.com>
<[email protected]>
<CABFgOvrcpt1d5OEC5+zqiRQyy2kfvioKHXGY4Qz2kmz2AtSsLg@mail.gmail.com>
<[email protected]>
<CABFgOvoJ4EsSfCxOCm0i8+Re3V6Q9MnKR4Rj307Pjas7nCbg9Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CABFgOvo1H2WpOJd_YwAUcSEsroQDGnQuWTuJtmJSigDNOj1=2Q@mail.gmail.com>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
Le 2 juillet 2017 10:39:09 GMT+02:00, Tomas Vondra <[email protected]> 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é.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Simple SQL too slow
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox