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 911945343E for ; Wed, 6 Apr 2005 19:09:32 +0100 (BST) 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 46003-04 for ; Wed, 6 Apr 2005 18:09:29 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7DA8E533E6 for ; Wed, 6 Apr 2005 19:09:29 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j36I93CW028761; Wed, 6 Apr 2005 14:09:03 -0400 (EDT) To: "Dave Held" Cc: "performance pgsql" , Arjen van der Meijden Subject: Re: Plan for relatively simple query seems to be very inefficient In-reply-to: <49E94D0CFCD4DB43AFBA928DDD20C8F9026184A0@asg002.asg.local> References: <49E94D0CFCD4DB43AFBA928DDD20C8F9026184A0@asg002.asg.local> Comments: In-reply-to "Dave Held" message dated "Wed, 06 Apr 2005 12:45:31 -0500" Date: Wed, 06 Apr 2005 14:09:03 -0400 Message-ID: <28760.1112810943@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/113 X-Sequence-Number: 11510 "Dave Held" writes: > My completely amateur guess is that the planner is able to use > Merge Join and Hash Join on your contrived queries because you > are only trying to join one field to a single value (i.e.: > operator=). But the BETWEEN clause is what forces the Nested > Loop. You can see that here: Yeah --- both merge and hash join are only usable for equality joins. (Thinking about it, it seems possible that mergejoin could be extended to work for range joins, but we're certainly far from being able to do that today.) So the basic alternatives the planner has are nestloops with either postcode on the outside, or data_main on the outside. The postcode-on-the-outside case would be plausible with an index on data_main.range, but Arjen didn't have one. The data_main-on-the-outside case could only use an index if the index was range-query-capable, which a 2-column btree index isn't. Given the small size of the postcodes table it's not real clear that an index probe would be much of a win anyway over a simple sequential scan. Comparing the nestloop case to the hash case does make one think that there's an awful lot of overhead somewhere, though. Two int2 comparisons ought not take very long :-(. Arjen, are you interested in getting a gprof profile of what the backend is doing in the nestloop -with-materialize plan? Or if you don't want to mess with it, please send me the data off-list and I'll run a profile. regards, tom lane