2010/4/4 Björn Lindqvist <bjourne@xxxxxxxxx>: > 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@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
