Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 434A49FB61D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 31 Aug 2007 18:44:23 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 94098-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 31 Aug 2007 18:44:14 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 953669FB5D7
	for <pgsql-sql@postgresql.org>; Fri, 31 Aug 2007 18:44:19 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l7VLbvbu002006;
	Fri, 31 Aug 2007 17:37:57 -0400 (EDT)
To: Richard Ray <rray@mstc.state.ms.us>
cc: pgsql-sql@postgresql.org
Subject: Re: How to influence the planner 
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>
Comments: In-reply-to Richard Ray <rray@mstc.state.ms.us>
	message dated "Fri, 31 Aug 2007 16:07:17 -0500"
Date: Fri, 31 Aug 2007 17:37:57 -0400
Message-ID: <2005.1188596277@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/273
X-Sequence-Number: 29211

Richard Ray <rray@mstc.state.ms.us> writes:
> On Fri, 31 Aug 2007, Michael Glaesemann wrote:
>> EXPLAIN ANALYZE will help you see what the planner is doing to produce the 
>> results.

> mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0 order by foo;
>                                                     QUERY PLAN
> ---------------------------------------------------------------
>   Index Scan using t1_pkey on t1  (cost=0.00..46698478.18 
> rows=60038 width=334) (actual time=4612.740..2349582.520 rows=32705 
> loops=1)
>     Filter: (length(bar) = 0)
>   Total runtime: 2349614.258 ms
> (3 rows)

> mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0;
>                                                     QUERY PLAN
> ---------------------------------------------------------------
>   Seq Scan on t1  (cost=100000000.00..102020349.17 rows=60038 
> width=334) (actual time=39.065..108645.233 rows=32705 loops=1)
>     Filter: (length(bar) = 0)
>   Total runtime: 108677.759 ms
> (3 rows)

The problem here is you've got enable_seqscan = off.  Don't do that.
That will make it use an index if it possibly can, whether using one
is a good idea or not.  In this case, since the useful condition on
length(bar) is not indexable, the best available index-using scan
uses the index to implement order by foo ... which is pointless here
in terms of saving runtime.

> I'm running PostgreSQL 8.1.0 on Fedora Core 6

Please update.  There are a *lot* of bugs fixed in the 8.1.x series
since then.

			regards, tom lane