From: Michael Glaesemann <grzm@seespotcode.net>
To: Richard Ray <rray@mstc.state.ms.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to influence the planner
Date: Fri, 31 Aug 2007 16:38:20 -0500
Message-ID: <F01EA480-832E-44E4-956E-1E740C48F622@seespotcode.net> (raw)
In-Reply-To: <Pine.LNX.4.64.0708311557140.17749@rray.drdc.mstc.ms.gov>
References: <Pine.LNX.4.64.0708311116000.17749@rray.drdc.mstc.ms.gov>
<373D2F52-E9CD-485A-9BF1-B019349FA5EC@seespotcode.net>
<Pine.LNX.4.64.0708311557140.17749@rray.drdc.mstc.ms.gov>
On Aug 31, 2007, at 16:07 , Richard Ray wrote:
> Total runtime: 2349614.258 ms
> (3 rows)
Wow. Nearly 40 minutes! What are your work_mem set at? You may want
to increase work_mem, as it might help with the sort.
> The index for foo on t1 is the primary index t1_pkey
> Why is it slower using the index
Using an index requires first scanning the index and then looking up
the value in the table, so depending on the number of rows that need
to be returned, using an index might have more overhead than just
reading every row of the table (i.e., a sequential scan).
>> Have you recently ANALYZEd t1?
>
> I run vacuum analyze nightly
That might not be often enough. Looking at the number of rows in the
cost estimate (60K rows) and the actual number of rows (~30K rows),
it looks like there's a factor of two difference.
>> If length(bar) = 0 is a common operation on this table, you might
>> consider using an expression index on t1:
>>
>> create index t1_length_bar_idx on t1 (length(bar));
>
> This is a one time procedure to fix some data but I've had this
> problem before
Depending on the time it takes to build the index, it might prove
worthwhile even for a one-off query. You're pretty much doing this by
using a temporary table though.
> I'm running PostgreSQL 8.1.0 on Fedora Core 6
You should upgrade 8.1.9, the latest in the 8.1.x series. This may
not help your performance issues, but there have been 9 point
releases since the version you're running which include bug and
security fixes. Even better, upgrade to 8.2.4, as there may very well
be performance improvements in 8.2 which help you. You could look
through the 8.2 release notes to see if any might apply.
Hope this helps.
Michael Glaesemann
grzm seespotcode net
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: grzm@seespotcode.net, rray@mstc.state.ms.us
Subject: Re: How to influence the planner
In-Reply-To: <F01EA480-832E-44E4-956E-1E740C48F622@seespotcode.net>
* 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