From: | Rob Wultsch <wultsch(at)gmail(dot)com> |
---|---|
To: | Björn Lindqvist <bjourne(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Completely wrong row estimates |
Date: | 2010-04-05 01:58:35 |
Message-ID: | l2u2c5ef4e31004041858zfc64922egf9870bf98ba3889b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2010/4/4 Björn Lindqvist <bjourne(at)gmail(dot)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(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Wang, Mary Y | 2010-04-05 02:35:21 | Null vs. Empty String in Postgres 8.3.8 |
Previous Message | Nilesh Govindarajan | 2010-04-05 01:58:22 | Re: Completely wrong row estimates |