Re: How to influence the planner

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