On 4/2/07, Rickard Sjöström <[EMAIL PROTECTED]> wrote:
Hi!
This post is related to the post "Performance of views" but this is another
problem now.
Problem: PostgreSQL seems to not use my index.
My postgres is 7.4.
I started all over again and got my query really fast on one database (~40
seconds became 150 ms). BUT when trying on another db (same postgres server but
with approx. 4 times as much data) it was really slow again (say 10 seconds).
Then I run the EXPLAIN ANALYSE of a sub-query of my query which I realize was
the problem.
It seems that it does not make use of the index in the slower database!?
fast db:
-------------------------
-> Index Scan using testcase_b_bid_index on testcase (cost=0.00..1656.82 rows=426
width=38) (never executed)"
-------------------------
slow db:
-------------------------
-> Seq Scan on testcase (cost=0.00..2896.42 rows=33242 width=64) (actual
time=77.027..791.014 rows=37093 loops=1)"
-------------------------
rows=37093 is all of the existing rows of table testcase!
(why does it say "never executed"?)
Hi,
a full index scan is even more expensive than a sequential scan.
So the query optimizer works fine.
Regards
Federico
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
