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) ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend