Re: [PERFORM] Simple SQL too slow

On 1 July 2017 17:56:13 GMT+01:00, Daviramos Roussenq Fortunato <daviramosrf@gmail.com> wrote:
>Debian:
>
>SELECT reltuples::numeric FROM pg_class WHERE oid =
>'mytable'::regclass;
>retuples=1883770
> --31ms
>
>SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname
>=
>'mytable';
>pg_relation_filepath=base/1003173/1204921
>relpages=30452
>--31ms
>
>
>Windows
>
>SELECT reltuples::numeric FROM pg_class WHERE oid =
>'mytable'::regclass;
>retuples=1883970
>--15ms
>
>SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname
>=
>'mytable';
>pg_relation_filepath=base/24576/205166
>relpages=30449
>--16ms
>
>2017-06-30 16:50 GMT-03:00 Andreas Kretschmer
><andreas@a-kretschmer.de>:
>
>> On 30 June 2017 20:14:33 GMT+01:00, Daviramos Roussenq Fortunato <
>> daviramosrf@gmail.com> wrote:
>> >Hi List,
>> >
>> >I have a Server where a simple SQL is taking a long time to return
>the
>> >results the Server settings are as follows:
>> >
>> >Debian GNU/Linux 7 (wheezy)
>> >CPU: Intel(R) Xeon(R) CPU           E5405  @ 2.00GHz
>> >Mem: 16GB
>> >HD: SSG 120 GB
>> >Postgresql 9.2
>> >
>> >postgresql.conf
>> >shared_buffers = 1536MB
>> >work_mem = 32MB
>> >maintenance_work_mem = 960MB
>> >effective_cache_size = 4864MB
>> >
>> >I did a test with the following SQL:
>> >
>> >select * from MINHATABELA
>> >
>> >
>> >It took 7 minutes to return the result.
>> >
>> >
>> >I did the same test on a Server:
>> >
>> >Windows Server 2012 Standard
>> >CPU: Intel(R) Xeon(R) CPU           E5-2450  @ 2.10GHz
>> >Mem: 24GB
>> >HD: HD 500 GB
>> >Postgresql 9.2
>> >
>> >
>> >postgresql.conf Default settings that come with the installation
>> >
>> >The same SQL returned in 3 minutes.
>> >
>> >The test in both Servers were done bench.
>> >
>> >This table has 1888240 records whose size is 458 MB
>> >
>> >I believe that in both Servers the response time of this SQL is very
>> >high,
>> >but the main thing in LINUX Server has something very wrong, I think
>it
>> >is
>> >something in the settings.
>> >
>> >What can I be checking?
>>
>> The query needs a full table scan, so it mainly depends on the speed
>of
>> your disk. Maybe you have s bloated table. Please check reltuples and
>> relpages from pg_class on both servers and compare.
>>
>>
>> --
>> 2ndQuadrant - The PostgreSQL Support Company
>>

Hrm. Settings seems okay (you can increase shared buffers up to 4-6 GB, and also effective_cache_size to 75% of ram,
buti think that's not the reason for the bad performance. 
Windows contains 50% more ram, maybe better/more caching. But i'm not sure if this can be the reason. The pg_class -
queriesare also slower, so i think there is something wrong on os-level. Hard to guess what. 

Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company