From: Richard Ray <rray@mstc.state.ms.us>
To: pgsql-sql@postgresql.org
Subject: Re: How to influence the planner
Date: Fri, 31 Aug 2007 16:07:17 -0500 (CDT)
Message-ID: <Pine.LNX.4.64.0708311557140.17749@rray.drdc.mstc.ms.gov> (raw)
In-Reply-To: <373D2F52-E9CD-485A-9BF1-B019349FA5EC@seespotcode.net>
References: <Pine.LNX.4.64.0708311116000.17749@rray.drdc.mstc.ms.gov>
<373D2F52-E9CD-485A-9BF1-B019349FA5EC@seespotcode.net>
On Fri, 31 Aug 2007, Michael Glaesemann wrote:
>
> On Aug 31, 2007, at 13:32 , Richard Ray wrote:
>
>> "select * from t1 where length(bar) = 0;" runs about 2 minutes
>> "select * from t1 where length(bar) = 0 order by foo ;" ran until I
>> stopped it after about 20 minutes
>
>
> 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)
mda=#
The index for foo on t1 is the primary index t1_pkey
Why is it slower using the index
> Have you recently ANALYZEd t1?
I run vacuum analyze nightly
> 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
I'm running PostgreSQL 8.1.0 on Fedora Core 6
>
> You might want to ask on the performance list as well, as this is right up
> their alley.
>
> Hope this gets you started on the right track.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
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: rray@mstc.state.ms.us
Subject: Re: How to influence the planner
In-Reply-To: <Pine.LNX.4.64.0708311557140.17749@rray.drdc.mstc.ms.gov>
* 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