Re: Simple SQL too slow

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(at)a-kretschmer(dot)de>:

> On 30 June 2017 20:14:33 GMT+01:00, Daviramos Roussenq Fortunato <
> daviramosrf(at)gmail(dot)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
>

--
Atenciosamente
Daviramos Roussenq Fortunato