Date: Fri, 31 Aug 2007 19:03:39 -0500 (CDT)
From: Richard Ray <rray@mstc.state.ms.us>
To: pgsql-sql@postgresql.org
Subject: Re: How to influence the planner 
In-Reply-To: <2005.1188596277@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.64.0708311856550.7540@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>
	<2005.1188596277@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Fri, 31 Aug 2007, Tom Lane wrote:

> 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.

Changing to enable_seqscan = on does solve this problem, thanks
Is there some method of crafting a query that will assert my wishes to the planner

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


When is enable_seqscan = off appropriate

>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>