From: Craig Ringer <craig@postnewspapers.com.au>
To: aymen marouani <marouani.aymen@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Profiling tool for postgres under win32
Date: Mon, 30 Nov 2009 17:00:30 +0800
Message-ID: <4B1389AE.2060305@postnewspapers.com.au> (raw)
In-Reply-To: <bebe9cb90911300042k4691e35j73397d25b884bcc5@mail.gmail.com>
References: <bebe9cb90911260148l3ce1084ay296e6c59374761c6@mail.gmail.com>
<4B0E6F03.4060902@postnewspapers.com.au>
<bebe9cb90911300042k4691e35j73397d25b884bcc5@mail.gmail.com>
On 30/11/2009 4:42 PM, aymen marouani wrote:
> Hi,
> I'm sorry and thanks for the help, concerning your question :
>
> "Out of interest, how are you talking to PostgreSQL? Hand-written SQL via
> JDBC? Hibernate / TopLink / some other ORM (possibly via JPA)?"
>
> I'm using JPA/Toplink to talk to the PostgresSQL, and I asked my
> question about profiling because of a slow simple query
>
> "SELECT i FROM Item i"
>
> which takes 4s to execute.
>
> Cordially and best regards.
In my off-list reply, where I suggested that you follow up on the list
instead, I pointed you to the EXPLAIN command. Also, the initial reply I
made pointed you to the logging options like log_min_duration.
You might want to use those tools to see what's going on. Start with:
EXPLAIN ANALYZE SELECT i FROM Item i;
... but I'd expect to see just a normal sequential scan of a table with
lots of entries. If that's the case, options to make it faster include:
- don't do it if you don't have to, it's always going to be expensive
- Make sure your tables aren't bloated. See:
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.htmlhttp://wiki.postgresql.org/wiki/Introduction_to_VACUUM%2C_ANALYZE%2C_EXPLAIN%2C_and_COUNT
... and use proper autovacuuming.
- Don't have tables that are too "wide", ie with too many fields. While
they're supported fine, they can be slower to scan because there's just
more data there. If you need crazy-fast sequential scans of the whole
table for just a few points of data, consider splitting the table into
two tables with a one-to-one relationship - but understand that that'll
slow other things down. A materialized view is another alternative.
- Write your app to deal with the latency. Sometimes queries are slow,
especially over slow links. Do your work in a background worker thread,
and keep the UI responsive. (Doesn't make sense for web apps, but is
important for normal GUI apps).
- Get faster disks, more RAM for caching, etc.
--
Craig Ringer
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: pgsql-sql@postgresql.org
Cc: craig@postnewspapers.com.au, marouani.aymen@gmail.com
Subject: Re: Profiling tool for postgres under win32
In-Reply-To: <4B1389AE.2060305@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox