On Wed, Feb 10, 2010 at 8:52 PM, Bryce Nesbitt <bryce2@obviously.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
