Re: Profiling tool for postgres under win32

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.html

http://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