Thread: Query plan discrepancies

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
Scanusing medline_abstract_tokens_pmid on
 
medline_abstract_tokens  (cost=0.00..6.67 rows=196 width=8)              Index Cond: (pmid = 7968456)        ->  Index
Scanusing 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)

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)