On Fri, 31 Aug 2007, Tom Lane wrote:
Richard Ray <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
