pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery plan discrepancies
2+ messages / 2 participants
[nested] [flat]
* Query plan discrepancies
@ 2004-07-13 12:56 Bob Arens <martian.bob@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: martian.bob@gmail.com @ 2004-07-13 12:56 UTC (permalink / raw)
To: pgsql-sql
Hi, I have two databases that were created with identical schemas and
both filled in the exact same way, with the same indices etc., yet
they give different query paths for identical SELECTs. Normally this
wouldn't bug me, but one DB returns the select in relatively short
order, while the other one will hang for 15 minutes or so before I get
annoyed enough to kill it. Here's the kicker - yes, the table sizes in
the DBs is different, but the _larger_ database is the one that's
returning! This confuses me; thoughts?
- Bob
The statement:
select norm,count(norm) from medline_abstract_tokens where
pmid=7968456 and norm in (select norm_token from word_stats_base)
group by norm;
EXPLAIN from DB 1 (comes back):
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=3282.48..3282.48 rows=1 width=8)
-> Nested Loop IN Join (cost=0.00..3282.35 rows=25 width=8)
-> Index Scan using medline_abstract_tokens_pmid on
medline_abstract_tokens (cost=0.00..6.67 rows=196 width=8)
Index Cond: (pmid = 7968456)
-> Index Scan using word_stats_base_norm on word_stats_base
(cost=0.00..3317.65 rows=1083 width=146)
Index Cond: (("outer".norm)::text =
(word_stats_base.norm_token)::text)
(6 rows)
EXPLAIN from DB 2 (doesn't come back):
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=7763.55..7763.56 rows=1 width=8)
-> Nested Loop (cost=4363.86..7763.55 rows=1 width=8)
-> HashAggregate (cost=4363.86..4363.86 rows=200 width=146)
-> Seq Scan on word_stats_base (cost=0.00..4126.09
rows=95109 width=146)
-> Index Scan using medline_abstract_tokens_norm on
medline_abstract_tokens (cost=0.00..16.99 rows=1 width=8)
Index Cond: ((medline_abstract_tokens.norm)::text =
("outer".norm_token)::text)
Filter: (pmid = 7968456)
(7 rows)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Query plan discrepancies
@ 2004-07-17 02:28 Rod Taylor <pg@rbt.ca>
parent: Bob Arens <martian.bob@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Rod Taylor @ 2004-07-17 02:28 UTC (permalink / raw)
To: Bob Arens <martian.bob@gmail.com>; +Cc: pgsql-sql
Have you run ANALYZE recently?
Please send back EXPLAIN ANALYZE for the below query.
> EXPLAIN from DB 2 (doesn't come back):
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------
> HashAggregate (cost=7763.55..7763.56 rows=1 width=8)
> -> Nested Loop (cost=4363.86..7763.55 rows=1 width=8)
> -> HashAggregate (cost=4363.86..4363.86 rows=200 width=146)
> -> Seq Scan on word_stats_base (cost=0.00..4126.09
> rows=95109 width=146)
> -> Index Scan using medline_abstract_tokens_norm on
> medline_abstract_tokens (cost=0.00..16.99 rows=1 width=8)
> Index Cond: ((medline_abstract_tokens.norm)::text =
> ("outer".norm_token)::text)
> Filter: (pmid = 7968456)
> (7 rows)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-07-17 02:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-13 12:56 Query plan discrepancies Bob Arens <martian.bob@gmail.com>
2004-07-17 02:28 ` Rod Taylor <pg@rbt.ca>
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