From: Tom Lane <tgl@sss.pgh.pa.us>
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 17:37:57 -0400
Message-ID: <2005.1188596277@sss.pgh.pa.us> (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>
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
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: tgl@sss.pgh.pa.us, rray@mstc.state.ms.us
Subject: Re: How to influence the planner
In-Reply-To: <2005.1188596277@sss.pgh.pa.us>
* 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