On Wed, Feb 10, 2010 at 8:52 PM, Bryce Nesbitt <bryce2@xxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
