X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0655A9FB1E7 for ; Wed, 16 Aug 2006 17:46:23 -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 95438-04 for ; Wed, 16 Aug 2006 17:46:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pow.actarg.com (host25.actarg.com [209.180.91.25]) by postgresql.org (Postfix) with ESMTP id 87BCB9FB1E6 for ; Wed, 16 Aug 2006 17:46:12 -0300 (ADT) Received: from [192.168.2.40] (pal.actarg.com [192.168.2.40]) (authenticated bits=0) by pow.actarg.com (8.13.1/8.13.1) with ESMTP id k7GKk6tb019032 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Wed, 16 Aug 2006 14:46:10 -0600 Message-ID: <44E3840E.4080502@actarg.com> Date: Wed, 16 Aug 2006 14:46:06 -0600 From: Kyle Bateman User-Agent: Mozilla Thunderbird 1.0.8-1.1.fc4 (X11/20060501) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@postgresql.org Subject: Re: Using bitmap index scans-more efficient References: <44DF6582.4000806@actarg.com> <82mza77rch.fsf@mid.bfk.de> <44E11E1A.8020507@actarg.com> <27367.1155606817@sss.pgh.pa.us> <44E349BA.4070205@actarg.com> <26676.1155747492@sss.pgh.pa.us> In-Reply-To: <26676.1155747492@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV 0.88/1656/Sun Aug 13 21:24:35 2006 on pow.actarg.com X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.025 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200608/128 X-Sequence-Number: 26086 Tom Lane wrote: >Kyle Bateman writes: > > >>I'm wondering if this might expose a weakness in the optimizer having to >>do with left joins. >> >> > >Before 8.2 the optimizer has no ability to rearrange the order of outer >joins. Do you have time to try your test case against CVS HEAD? > > OK, I figured it out--grabbed the latest snapshot (hope that is what you need). My results are similar: select l.* from ledg_v1 l, proj p where l.proj = p.proj_id and 5 = p.par; (24 msec) Nested Loop (cost=0.00..1991.93 rows=480 width=23) -> Nested Loop (cost=0.00..4.68 rows=6 width=8) -> Seq Scan on acct a (cost=0.00..1.12 rows=1 width=4) Filter: ((code)::text = 'ap'::text) -> Index Scan using i_proj_par on proj p (cost=0.00..3.49 rows=6 width=4) Index Cond: (5 = par) -> Index Scan using i_ledg_proj on ledg l (cost=0.00..330.17 rows=83 width=19) Index Cond: (l.proj = "outer".proj_id) select l.* from ledg_v2 l, proj p where l.proj = p.proj_id and 5 = p.par; (1.25 sec) Hash Join (cost=4.63..16768.43 rows=480 width=23) Hash Cond: ("outer".proj = "inner".proj_id) -> Nested Loop Left Join (cost=1.13..14760.13 rows=400000 width=23) -> Seq Scan on ledg l (cost=0.00..6759.00 rows=400000 width=19) -> Materialize (cost=1.13..1.14 rows=1 width=4) -> Seq Scan on acct a (cost=0.00..1.12 rows=1 width=4) Filter: ((code)::text = 'ap'::text) -> Hash (cost=3.49..3.49 rows=6 width=4) -> Index Scan using i_proj_par on proj p (cost=0.00..3.49 rows=6 width=4) Index Cond: (5 = par)