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>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <F01EA480-832E-44E4-956E-1E740C48F622@seespotcode.net>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: How to influence the planner
Date: Fri, 31 Aug 2007 16:38:20 -0500
To: Richard Ray <rray@mstc.state.ms.us>


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