X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 88885D1B289 for ; Tue, 13 Jul 2004 09:59:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16711-05 for ; Tue, 13 Jul 2004 12:59:41 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id B08A5D1B269 for ; Tue, 13 Jul 2004 09:59:32 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i6DCxbi7052187 for ; Tue, 13 Jul 2004 12:59:37 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i6DCtvWg051785 for pgsql-sql@postgresql.org; Tue, 13 Jul 2004 12:55:57 GMT From: martian.bob@gmail.com (Bob Arens) X-Newsgroups: comp.databases.postgresql.sql Subject: Query plan discrepancies Date: 13 Jul 2004 05:56:00 -0700 Organization: http://groups.google.com Lines: 45 Message-ID: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/103 X-Sequence-Number: 18355 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)