Re: Simple SQL too slow

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