pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Bryce Nesbitt <bryce2@obviously.com>
To: pgsql-performance@postgresql.org
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Subject: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Date: Fri, 12 Feb 2010 23:45:23 -0800
Message-ID: <4B765893.9080600@obviously.com> (raw )
In-Reply-To: <4B751AD5020000250002F248@gw.wicourts.gov >
References: <4B723042.7090406@obviously.com >
<4B751AD5020000250002F248@gw.wicourts.gov >
EXPLAIN ANALYZE
SELECT contexts.context_key
FROM contexts
JOIN articles ON (articles.context_key = contexts.context_key)
JOIN matview_82034 ON (matview_82034.context_key =
contexts.context_key)
WHERE EXISTS
(
SELECT *
FROM article_words
JOIN words using (word_key)
WHERE context_key = contexts.context_key
AND word = 'insider'
)
AND EXISTS
(
SELECT *
FROM article_words
JOIN words using (word_key)
WHERE context_key = contexts.context_key
AND word = 'trading'
)
AND EXISTS
(
SELECT *
FROM virtual_ancestors a
JOIN bp_categories ON (bp_categories.context_key =
a.ancestor_key)
WHERE a.context_key = contexts.context_key
AND lower(bp_categories.category) = 'law'
)
AND articles.indexed
;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=13511.95..13662023.05 rows=19282 width=4) (actual time=3070.834..225973.159 rows=622 loops=1)
-> Hash Join (cost=13511.95..356938.38 rows=177937 width=8) (actual time=1106.242..7520.756 rows=351337 loops=1)
Hash Cond: (articles.context_key = matview_82034.context_key)
-> Seq Scan on articles (cost=0.00..334502.90 rows=386266 width=4) (actual time=0.030..4031.203 rows=355626 loops=1)
Filter: indexed
-> Hash (cost=6322.20..6322.20 rows=438220 width=4) (actual time=1105.663..1105.663 rows=438220 loops=1)
-> Seq Scan on matview_82034 (cost=0.00..6322.20 rows=438220 width=4) (actual time=7.105..544.072 rows=438220 loops=1)
-> Index Scan using contexts_pkey on contexts (cost=0.00..74.76 rows=1 width=4) (actual time=0.619..0.619 rows=0 loops=351337)
Index Cond: (contexts.context_key = articles.context_key)
Filter: ((subplan) AND (subplan) AND (subplan))
SubPlan
-> Nested Loop (cost=0.00..30.54 rows=1 width=17) (actual time=6.119..6.119 rows=1 loops=983)
-> Index Scan using words_word on words (cost=0.00..5.50 rows=1 width=13) (actual time=0.028..0.029 rows=1 loops=983)
Index Cond: ((word)::text = 'trading'::text)
-> Index Scan using article_words_cw on article_words (cost=0.00..25.02 rows=1 width=8) (actual time=6.082..6.082 rows=1 loops=983)
Index Cond: ((public.article_words.context_key = $0) AND (public.article_words.word_key = public.words.word_key))
-> Nested Loop (cost=0.00..30.54 rows=1 width=17) (actual time=6.196..6.196 rows=0 loops=26494)
-> Index Scan using words_word on words (cost=0.00..5.50 rows=1 width=13) (actual time=0.022..0.024 rows=1 loops=26494)
Index Cond: ((word)::text = 'insider'::text)
-> Index Scan using article_words_cw on article_words (cost=0.00..25.02 rows=1 width=8) (actual time=6.165..6.165 rows=0 loops=26494)
Index Cond: ((public.article_words.context_key = $0) AND (public.article_words.word_key = public.words.word_key))
-> Nested Loop (cost=0.00..38.38 rows=3 width=29) (actual time=0.122..0.122 rows=0 loops=351337)
-> Index Scan using virtual_context_key_idx on virtual_ancestors a (cost=0.00..7.35 rows=5 width=10) (actual time=0.074..0.085 rows=5 loops=351337)
Index Cond: (context_key = $0)
-> Index Scan using bp_categories_context_key_idx on bp_categories (cost=0.00..6.19 rows=1 width=19) (actual time=0.004..0.004 rows=0 loops=1740050)
Index Cond: (bp_categories.context_key = a.ancestor_key)
Filter: (lower(bp_categories.category) = 'law'::text)
Total runtime: 225976.046 ms
(28 rows)
# select * from version();
PostgreSQL 8.3.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)
Attachments:
Grittner_qplan.txt (text/plain, 4.1K) download
EXPLAIN ANALYZE
SELECT contexts.context_key
FROM contexts
JOIN articles ON (articles.context_key = contexts.context_key)
JOIN matview_82034 ON (matview_82034.context_key =
contexts.context_key)
WHERE EXISTS
(
SELECT *
FROM article_words
JOIN words using (word_key)
WHERE context_key = contexts.context_key
AND word = 'insider'
)
AND EXISTS
(
SELECT *
FROM article_words
JOIN words using (word_key)
WHERE context_key = contexts.context_key
AND word = 'trading'
)
AND EXISTS
(
SELECT *
FROM virtual_ancestors a
JOIN bp_categories ON (bp_categories.context_key =
a.ancestor_key)
WHERE a.context_key = contexts.context_key
AND lower(bp_categories.category) = 'law'
)
AND articles.indexed
;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=13511.95..13662023.05 rows=19282 width=4) (actual time=3070.834..225973.159 rows=622 loops=1)
-> Hash Join (cost=13511.95..356938.38 rows=177937 width=8) (actual time=1106.242..7520.756 rows=351337 loops=1)
Hash Cond: (articles.context_key = matview_82034.context_key)
-> Seq Scan on articles (cost=0.00..334502.90 rows=386266 width=4) (actual time=0.030..4031.203 rows=355626 loops=1)
Filter: indexed
-> Hash (cost=6322.20..6322.20 rows=438220 width=4) (actual time=1105.663..1105.663 rows=438220 loops=1)
-> Seq Scan on matview_82034 (cost=0.00..6322.20 rows=438220 width=4) (actual time=7.105..544.072 rows=438220 loops=1)
-> Index Scan using contexts_pkey on contexts (cost=0.00..74.76 rows=1 width=4) (actual time=0.619..0.619 rows=0 loops=351337)
Index Cond: (contexts.context_key = articles.context_key)
Filter: ((subplan) AND (subplan) AND (subplan))
SubPlan
-> Nested Loop (cost=0.00..30.54 rows=1 width=17) (actual time=6.119..6.119 rows=1 loops=983)
-> Index Scan using words_word on words (cost=0.00..5.50 rows=1 width=13) (actual time=0.028..0.029 rows=1 loops=983)
Index Cond: ((word)::text = 'trading'::text)
-> Index Scan using article_words_cw on article_words (cost=0.00..25.02 rows=1 width=8) (actual time=6.082..6.082 rows=1 loops=983)
Index Cond: ((public.article_words.context_key = $0) AND (public.article_words.word_key = public.words.word_key))
-> Nested Loop (cost=0.00..30.54 rows=1 width=17) (actual time=6.196..6.196 rows=0 loops=26494)
-> Index Scan using words_word on words (cost=0.00..5.50 rows=1 width=13) (actual time=0.022..0.024 rows=1 loops=26494)
Index Cond: ((word)::text = 'insider'::text)
-> Index Scan using article_words_cw on article_words (cost=0.00..25.02 rows=1 width=8) (actual time=6.165..6.165 rows=0 loops=26494)
Index Cond: ((public.article_words.context_key = $0) AND (public.article_words.word_key = public.words.word_key))
-> Nested Loop (cost=0.00..38.38 rows=3 width=29) (actual time=0.122..0.122 rows=0 loops=351337)
-> Index Scan using virtual_context_key_idx on virtual_ancestors a (cost=0.00..7.35 rows=5 width=10) (actual time=0.074..0.085 rows=5 loops=351337)
Index Cond: (context_key = $0)
-> Index Scan using bp_categories_context_key_idx on bp_categories (cost=0.00..6.19 rows=1 width=19) (actual time=0.004..0.004 rows=0 loops=1740050)
Index Cond: (bp_categories.context_key = a.ancestor_key)
Filter: (lower(bp_categories.category) = 'law'::text)
Total runtime: 225976.046 ms
(28 rows)
# select * from version();
PostgreSQL 8.3.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)
view thread (20+ messages) latest in thread
Message-ID: <4B765893.9080600@obviously.com>
Permalink: ../4B765893.9080600@obviously.com/
Also on: postgresql.org/message-id/4B765893.9080600@obviously.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bryce2@obviously.com, Kevin.Grittner@wicourts.gov
Subject: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
In-Reply-To: <4B765893.9080600@obviously.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox