2010/4/4 Björn Lindqvist <bjourne@gmail.com>:
> 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
>
You probably want to run "analyze" or "vacuum analyze" to update
statistics. Do you have auto vacuum setup?
--
Rob Wultsch
wultsch@gmail.com