Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

On Wed, Feb 10, 2010 at 8:52 PM, Bryce Nesbitt <bryce2(at)obviously(dot)com> wrote:
> If you guys succeed in making this class of query perform, you'll have beat
> out the professional consulting firm we hired, which was all but useless!
> The query is usually slow, but particular combinations of words seem to make
> it obscenely slow.

Heh heh heh professional consulting firm.

> production=# EXPLAIN ANALYZE SELECT context_key FROM article_words
> WHERE word_key = 3675;
> -------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using article_words_wc on article_words  (cost=0.00..21433.53
> rows=11309 width=4) (actual time=0.025..7.579 rows=4003 loops=1)
>    Index Cond: (word_key = 3675)
>  Total runtime: 11.704 ms

That's surprisingly inaccurate. Since this table is large:

> production=# explain analyze select count(*) from article_words;
> Aggregate  (cost=263831.63..263831.64 rows=1 width=0) (actual
> time=35851.654..35851.655 rows=1 loops=1)
>    ->  Seq Scan on words  (cost=0.00..229311.30 rows=13808130 width=0)
> (actual time=0.043..21281.124 rows=13808184 loops=1)
>  Total runtime: 35851.723 ms

...you may need to crank up the statistics target. I would probably
try cranking it all the way up to the max, though there is a risk that
might backfire, in which case you'll need to decrease it again.

ALTER TABLE article_words ALTER COLUMN word_key SET STATISTICS 1000;

That's probably not going to fix your whole problem, but it should be
interesting to see whether it makes things better or worse and by how
much.

...Robert