X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8C5639FB40C for ; Mon, 21 Aug 2006 15:29:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 52999-03-3 for ; Mon, 21 Aug 2006 15:29:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id CC8859FB422 for ; Mon, 21 Aug 2006 15:26:21 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k7LIQJvG005600; Mon, 21 Aug 2006 14:26:19 -0400 (EDT) To: Scott Matseas cc: pgsql-performance@postgresql.org Subject: Re: Index usage In-reply-to: <44E9EEF9.7050901@intrusic.com> References: <44E9EEF9.7050901@intrusic.com> Comments: In-reply-to Scott Matseas message dated "Mon, 21 Aug 2006 13:35:53 -0400" Date: Mon, 21 Aug 2006 14:26:19 -0400 Message-ID: <5599.1156184779@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.163 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/315 X-Sequence-Number: 20500 Scott Matseas writes: > If I enable sequential scan the Index Cond in > question gets replaced with a Seq scan. What other planner parameters have you been fooling with? With no data in the tables, I get a reasonably sane-looking plan, so I'm thinking you've chosen bad values for something or other (starting with enable_seqscan = off ;-)) explain SELECT * FROM last_summarized ls JOIN tableA s ON s.table_idA > ls.summarized_id AND s.table_idA <= ls.max_session_id LEFT JOIN tableB sf ON s.table_idA = sf.table_idA AND sf.direction = 'a'::char LEFT JOIN tableB sfb ON s.table_idA = sfb.table_idA AND sfb.direction = 'b'::char WHERE ls.summary_name::text = 'summary'::text ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=6.16..54.51 rows=216 width=116) -> Nested Loop Left Join (cost=6.16..42.05 rows=216 width=95) -> Nested Loop (cost=6.16..29.58 rows=216 width=74) -> Index Scan using last_summarized_pk on last_summarized ls (cost=0.00..8.02 rows=1 width=66) Index Cond: ((summary_name)::text = 'summary'::text) -> Bitmap Heap Scan on tablea s (cost=6.16..18.32 rows=216 width=8) Recheck Cond: ((s.table_ida > ls.summarized_id) AND (s.table_ida <= ls.max_session_id)) -> Bitmap Index Scan on table_ida_pk (cost=0.00..6.16 rows=216 width=0) Index Cond: ((s.table_ida > ls.summarized_id) AND (s.table_ida <= ls.max_session_id)) -> Index Scan using tableb_unq on tableb sfb (cost=0.00..0.05 rows=1 width=21) Index Cond: ((s.table_ida = sfb.table_ida) AND (sfb.direction = 'b'::bpchar)) -> Index Scan using tableb_unq on tableb sf (cost=0.00..0.05 rows=1 width=21) Index Cond: ((s.table_ida = sf.table_ida) AND (sf.direction = 'a'::bpchar)) (13 rows) regards, tom lane