Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id DBEE764FD24 for ; Mon, 17 Nov 2008 05:52:32 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 94291-05 for ; Mon, 17 Nov 2008 05:52:25 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.28]) by mail.postgresql.org (Postfix) with ESMTP id C241664FD01 for ; Mon, 17 Nov 2008 05:52:24 -0400 (AST) Received: by yx-out-2324.google.com with SMTP id 8so948032yxb.73 for ; Mon, 17 Nov 2008 01:52:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=Czj/e9kOqxAAu3PEhI2h2OMHxf/zHcEk7H6uy6wMZEE=; b=QzLoD9MBmwPTaYAQUnzOT2Anj00iosfWHuK6ntEr2leL4CFG2L/sAZcFouBkDMUJnT 07Pwd53mjLC7GqemLKQ+n5GQcOhHxafGefaOZpNzSpSrPW4Bbfix/XmHcd7PWyRbMSVe A7YB0CcXBh9cDzvbMmrKdKkeV1UrMdfLFr5qk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=GwU3ueCo30a9739ZBvTGXNj/fiKc8Rj54FADpsZtGl+n76UXB4mp53lN7kyRkp4QeC MwN4wRZFnbBBdg2Oombj89s648Oj1InyxAO46vzJkZwCGcgJnMlvK06ctAkh5quoYEix YkxGQ5R12CuE6EEu47zzAefuP/Ostll7YAksU= Received: by 10.65.112.18 with SMTP id p18mr3540967qbm.87.1226915542005; Mon, 17 Nov 2008 01:52:22 -0800 (PST) Received: by 10.65.22.1 with HTTP; Mon, 17 Nov 2008 01:52:21 -0800 (PST) Message-ID: <331e40660811170152v219b5327hc1564da48dde2143@mail.gmail.com> Date: Mon, 17 Nov 2008 11:52:21 +0200 From: "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" To: "Tom Lane" Subject: Re: PostgreSQL OR performance Cc: pgsql-performance@postgresql.org In-Reply-To: <7426.1226768851@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_40109_15651492.1226915541984" References: <331e40660811050312u552cab53ifa88e493e1c4f7ac@mail.gmail.com> <331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com> <29e3942f0811060426k5fe34b1av401a2dba62e4ae3c@mail.gmail.com> <331e40660811060833l49bf837em41ca88d4b2061ea1@mail.gmail.com> <49131ED5.80307@archonet.com> <331e40660811060937q460e6f9fvf336455200d31f56@mail.gmail.com> <331e40660811070114rd70cf0dy194020a9f9ea8802@mail.gmail.com> <49141C5E.2010404@archonet.com> <331e40660811150557s50e54c54m974645e31e30a612@mail.gmail.com> <7426.1226768851@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200811/143 X-Sequence-Number: 31874 ------=_Part_40109_15651492.1226915541984 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 2008/11/15 Tom Lane > "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" writes: > > I am not. I can't see how materialize can multiply number of rows it gets > > from sort by 100. > > Is it the right-hand input of a merge join? If so you're looking at > mark/restore rescans, ie, repeated fetches of the same tuples. There > must be a huge number of duplicate join keys in that relation to make > for such an increase though. Normally the planner avoids putting a > table with lots of duplicates as the RHS of a merge, but if it doesn't > have good statistics for the join key then it might not realize the > problem. > OK, thanks for cleaning-up some mystery. But, returning to original Q: Do anyone known why does it choose plan from * OR-plan.txt* instead of *union-plan.txt*? The first is cost=4588.13..960900482668.95, the latter is cost=266348.42..272953.14 according to statistics postgres have, so I suppose planner would select it if it could evaluate it. ------=_Part_40109_15651492.1226915541984 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

2008/11/15 Tom Lane <tgl@sss.pgh.pa.us>
"=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <tivv00@gmail.com> writes:
> I am not. I can't see how materialize can multiply number of rows it gets
> from sort by 100.

Is it the right-hand input of a merge join?  If so you're looking at
mark/restore rescans, ie, repeated fetches of the same tuples.  There
must be a huge number of duplicate join keys in that relation to make
for such an increase though.  Normally the planner avoids putting a
table with lots of duplicates as the RHS of a merge, but if it doesn't
have good statistics for the join key then it might not realize the
problem.

OK, thanks for cleaning-up some mystery.
But, returning to original Q: Do anyone known why does it choose plan from OR-plan.txt instead of union-plan.txt? The first is cost=4588.13..960900482668.95, the latter is cost=266348.42..272953.14 according to statistics postgres have, so I suppose planner would select it if it could evaluate it.

------=_Part_40109_15651492.1226915541984--