Re: How to influence the planner

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