Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1AE801337B96 for ; Thu, 13 Jan 2011 19:05:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 44413-05 for ; Thu, 13 Jan 2011 23:05:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id B641D1337BA8 for ; Thu, 13 Jan 2011 19:05:07 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p0DN55it018302; Thu, 13 Jan 2011 18:05:05 -0500 (EST) To: Robert Haas cc: Jon Nelson , pgsql-performance@postgresql.org Subject: Re: queries with lots of UNIONed relations In-reply-to: References: <19302.1294938802@sss.pgh.pa.us> <4879.1294949144@sss.pgh.pa.us> <11563.1294957582@sss.pgh.pa.us> Comments: In-reply-to Robert Haas message dated "Thu, 13 Jan 2011 17:41:40 -0500" Date: Thu, 13 Jan 2011 18:05:04 -0500 Message-ID: <18301.1294959904@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/91 X-Sequence-Number: 41878 Robert Haas writes: > On Thu, Jan 13, 2011 at 5:26 PM, Tom Lane wrote: >> I don't believe there is any case where hashing each individual relation >> is a win compared to hashing them all together.  If the optimizer were >> smart enough to be considering the situation as a whole, it would always >> do the latter. > You might be right, but I'm not sure. Suppose that there are 100 > inheritance children, and each has 10,000 distinct values, but none of > them are common between the tables. In that situation, de-duplicating > each individual table requires a hash table that can hold 10,000 > entries. But deduplicating everything at once requires a hash table > that can hold 1,000,000 entries. > Or am I all wet? If you have enough memory to de-dup them individually, you surely have enough to de-dup all at once. It is not possible for a single hashtable to have worse memory consumption than N hashtables followed by a union hashtable, and in fact if there are no common values then the latter eats twice as much space because every value appears twice in two different hashtables. regards, tom lane