Message-ID: <4B1389AE.2060305@postnewspapers.com.au>
Date: Mon, 30 Nov 2009 17:00:30 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB;
	rv:1.9.1.5) Gecko/20091121 Thunderbird/3.0
MIME-Version: 1.0
To: aymen marouani <marouani.aymen@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Profiling tool for postgres under win32
References: <bebe9cb90911260148l3ce1084ay296e6c59374761c6@mail.gmail.com>	
	<4B0E6F03.4060902@postnewspapers.com.au>
	<bebe9cb90911300042k4691e35j73397d25b884bcc5@mail.gmail.com>
In-Reply-To: <bebe9cb90911300042k4691e35j73397d25b884bcc5@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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