Subject: Completely wrong row estimates
Hello everybody,
Here is the EXPLAIN ANALYZE output for a simple query in my database
running on postgres 8.3.9:
EXPLAIN ANALYZE
SELECT * FROM word w JOIN video_words vw ON w.id = vw.word_id
WHERE w.word = 'tagtext';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=18.89..2711.16 rows=95 width=24) (actual
time=19.266..131.255 rows=43374 loops=1)
-> Index Scan using word_word_key on word w (cost=0.00..8.28
rows=1 width=12) (actual time=0.029..0.034 rows=1 loops=1)
Index Cond: ((word)::text = 'tagtext'::text)
-> Bitmap Heap Scan on video_words vw (cost=18.89..2693.31
rows=766 width=12) (actual time=19.227..77.809 rows=43374 loops=1)
Recheck Cond: (vw.word_id = w.id)
-> Bitmap Index Scan on video_words_word_id_key
(cost=0.00..18.70 rows=766 width=0) (actual time=12.662..12.662
rows=43374 loops=1)
Index Cond: (vw.word_id = w.id)
Total runtime: 154.215 ms
Note how the planner estimates that there are 766 rows in the table
that matches the word 'tagtext'. In reality 43374 does. I've tried to
get postgres to refresh the statistics by running with
enable_statistics_target=100, running VACUUM, VACUUM FULL, VACUUM FULL
ANALYZE etc but nothing works. Postgres seem stuck with its bad
statistics and unwilling to change them. There are many other strings
that also matches tens of thousands of rows in the table which
postgres only thinks matches 766.
Is this a bug in postgres?
--
mvh Björn