X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 59E43475EEE for ; Wed, 16 Apr 2003 20:03:08 -0400 (EDT) Received: from spunge-bob.discern.com (moore.discerncomm.com [130.107.25.153]) by postgresql.org (Postfix) with ESMTP id 79792475ECE for ; Wed, 16 Apr 2003 20:03:07 -0400 (EDT) Received: by spunge-bob.discern.com (Postfix, from userid 1000) id 4CA5B27C88; Tue, 15 Apr 2003 23:58:28 -0700 (PDT) Subject: dum query plan From: Jonathan Moore To: pgsql-performance@postgresql.org Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.5 Date: 15 Apr 2003 23:58:28 -0700 Message-Id: <1050476308.20872.19.camel@spunge-bob> Mime-Version: 1.0 X-Spam-Status: No, hits=-12.0 required=5.0 tests=BAYES_10,DATE_IN_PAST_12_24,USER_AGENT_XIMIAN autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/147 X-Sequence-Number: 1653 I am wondering why it uses the O(n^2) nested loop when there is a O(N) methoud using btree indexes for a merg join. I am using 7.2.1 would upgrading fix my problime or is it somthing else? Given the schema: drop table Entry_Pairs; create table Entry_Pairs ( left_entry int REFERENCES Entry ON DELETE RESTRICT, right_entry int REFERENCES Entry ON DELETE RESTRICT, relation int NOT NULL , subtract bool NOT NULL , comment int NULL REFERENCES Comment ON DELETE SET NULL, UNIQUE (left_entry, right_entry, relation) ); CREATE INDEX entry_pairs_left_index ON entry_pairs (left_entry); CREATE INDEX entry_pairs_right_index ON entry_pairs (right_entry); -- You get this" dblex=> explain select A.left_entry from entry_pairs A, entry_pairs B where A.right_entry != B.left_entry; NOTICE: QUERY PLAN: Nested Loop (cost=100000000.00..102876671.17 rows=97545252 width=12) -> Seq Scan on entry_pairs a (cost=0.00..167.77 rows=9877 width=8) -> Seq Scan on entry_pairs b (cost=0.00..167.77 rows=9877 width=4) EXPLAIN That is dum. If you just walk both B-Tree indexes there is a O(n) search. I tryed to turn off netsed loops but it still did it. (the reason the cost is 100000000.00 is a artifact from turing off loops) -Jonathan