X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 036A4D1B1CD for ; Fri, 30 Jul 2004 13:49:37 -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 19057-08 for ; Fri, 30 Jul 2004 16:49:32 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0B1F4D1B199 for ; Fri, 30 Jul 2004 13:49:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i6UGnUKa024078; Fri, 30 Jul 2004 12:49:30 -0400 (EDT) To: Stan Bielski Cc: pgsql-performance@postgresql.org Subject: Re: Optimizer refuses to hash join In-reply-to: References: Comments: In-reply-to Stan Bielski message dated "Thu, 29 Jul 2004 14:49:55 -0400" Date: Fri, 30 Jul 2004 12:49:30 -0400 Message-ID: <24077.1091206170@sss.pgh.pa.us> From: Tom Lane 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/240 X-Sequence-Number: 7631 Stan Bielski writes: > On Thu, 29 Jul 2004, Tom Lane wrote: >> Are you sure the join condition is hashjoinable? You didn't say >> anything about the datatypes involved ... > My apologies. The columns that I want to join are both type 'inet'. > Shouldn't that be hashjoinable? Depends on your PG version. The raw type isn't hashjoinable, because its '=' operator ignores the inet-vs-cidr flag. Before 7.4 the operator was (correctly) marked not hashjoinable. In 7.4 it was (incorrectly) marked hashjoinable, due no doubt to momentary brain fade on my part. For 7.5 it is hashjoinable and the join will actually work, because we added a specialized hash function that also ignores the inet-vs-cidr flag. If you are joining data that is all inet or all cidr (no mixtures), then 7.4 works okay, which is why we didn't notice the bug right away. If that's good enough for now, you could emulate the 7.4 behavior in earlier releases by setting the oprcanhash flag in pg_operator for the inet equality operator. regards, tom lane