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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
