public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daviramos Roussenq Fortunato <[email protected]>
To: [email protected]
Subject: Re: Simple SQL too slow
Date: Sat, 1 Jul 2017 13:56:13 -0300
Message-ID: <CABFgOvrcpt1d5OEC5+zqiRQyy2kfvioKHXGY4Qz2kmz2AtSsLg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CABFgOvotE9gMEkUrBwMPRs60jDiBaX5TXBiVY9jUnjHmYVRXFw@mail.gmail.com>
	<[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

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 <[email protected]>:

> On 30 June 2017 20:14:33 GMT+01:00, Daviramos Roussenq Fortunato <
> [email protected]> 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

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]
  Subject: Re: Simple SQL too slow
  In-Reply-To: <CABFgOvrcpt1d5OEC5+zqiRQyy2kfvioKHXGY4Qz2kmz2AtSsLg@mail.gmail.com>

* 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