Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 50A911337BD9 for ; Fri, 14 Jan 2011 07:39:08 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 95146-01-7 for ; Fri, 14 Jan 2011 11:39:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id E12691337BC3 for ; Fri, 14 Jan 2011 07:38:59 -0400 (AST) Received: by bwz15 with SMTP id 15so2431987bwz.19 for ; Fri, 14 Jan 2011 03:38:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:message-id:date:from:user-agent:mime-version:to :subject:references:in-reply-to:content-type :content-transfer-encoding; bh=U2scjBxXMc1caOrB/VR0kKikQn9yeAMwgMOiN2ioziY=; b=nkvsHqcjUNHDRPGCBEI7b9xcwnp8GMixrGt6j0gfEztdL1iM8QPDq4nUENwDR//ap0 f9NmDQBPHlyJP13OvScTMcmOd3z/5wf08TTmbhj9Nv8/n+wGK8aHX0E+8J26HNC+uGUe DmARCA0OPsMujKwvQ4zl1YpaEYuGKTkbXs0i4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding; b=oyt3rABAdTOwa3wVb7GzJgCrFBH0KAdjp41r65Nne2jWJulRRFIMeQQgepdJqynF/D 5wPQ6v9OpcPaND4jGgaDp22vnQylWYuyE+PON5QZ5ReI2JjttDqnVUl73kvx3U4cqw+0 GwKOJyiDHTr0m9vAlxicXHOfXZl2x83p/ep5Q= Received: by 10.204.116.79 with SMTP id l15mr527352bkq.122.1295005139120; Fri, 14 Jan 2011 03:38:59 -0800 (PST) Received: from [10.1.1.57] (skuns.zoral.com.ua [91.193.166.194]) by mx.google.com with ESMTPS id j11sm1241582bka.0.2011.01.14.03.38.56 (version=TLSv1/SSLv3 cipher=RC4-MD5); Fri, 14 Jan 2011 03:38:57 -0800 (PST) Message-ID: <4D3035D8.8030800@gmail.com> Date: Fri, 14 Jan 2011 13:39:04 +0200 From: Vitalii Tymchyshyn User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: queries with lots of UNIONed relations References: <19302.1294938802@sss.pgh.pa.us> <4879.1294949144@sss.pgh.pa.us> <11563.1294957582@sss.pgh.pa.us> In-Reply-To: <11563.1294957582@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.036 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201101/97 X-Sequence-Number: 41884 14.01.11 00:26, Tom Lane написав(ла): > Robert Haas writes: >> On Thu, Jan 13, 2011 at 3:12 PM, Jon Nelson wrote: >>> I still think that having UNION do de-duplication of each contributory >>> relation is a beneficial thing to consider -- especially if postgresql >>> thinks the uniqueness is not very high. >> This might be worth a TODO. > 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. > > How about cases when individual relations are already sorted? This will mean that they can be deduplicated fast and in streaming manner. Even partial sort order may help because you will need to deduplicate only groups with equal sorted fields, and this will take much less memory and be much more streaming. And if all individual deduplications are streaming and are sorted in one way - you can simply do a merge on top. Best regards, Vitalii Tymchyshyn.