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 44406329E77 for ; Wed, 22 Sep 2004 18:56:45 +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 71500-04 for ; Wed, 22 Sep 2004 17:56:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4387B329E63 for ; Wed, 22 Sep 2004 18:56:40 +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 i8MHuY1p014107; Wed, 22 Sep 2004 13:56:34 -0400 (EDT) To: Greg Stark Cc: Dennis Bjorklund , Gaetano Mendola , pgsql-performance@postgresql.org Subject: Re: 7.4 vs 7.3 ( hash join issue ) In-reply-to: <87sm9acih3.fsf@stark.xeocode.com> References: <87sm9acih3.fsf@stark.xeocode.com> Comments: In-reply-to Greg Stark message dated "22 Sep 2004 13:38:00 -0400" Date: Wed, 22 Sep 2004 13:56:34 -0400 Message-ID: <14106.1095875794@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: 200409/218 X-Sequence-Number: 8346 Greg Stark writes: > No, postgres didn't do things in reverse order. It hashed the empty table and > then went ahead and checked every record of the non-empty table against the > empty hash table. > Reading the code there's no check for this, and it seems like it would be a > useful low-cost little optimization. Yeah, I was just looking at doing that. It would also be interesting to prefetch one row from the outer table and fall out immediately (without building the hash table) if the outer table is empty. This seems to require some contortion of the code though :-( > I think postgres normally hashes the table it thinks is smaller, Right, it will prefer to put the physically smaller table (estimated width*rows) on the inside. regards, tom lane