[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] PostgreSQL OR performance
From:       "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <tivv00 () gmail ! com>
Date:       2008-11-17 9:52:21
Message-ID: 331e40660811170152v219b5327hc1564da48dde2143 () mail ! gmail ! com
[Download RAW message or body]

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.

[Attachment #3 (text/html)]

<br><br><div class="gmail_quote">2008/11/15 Tom Lane <span dir="ltr">&lt;<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt;</span><br><blockquote \
class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt \
0pt 0.8ex; padding-left: 1ex;"> <div \
class="Ih2E3d">&quot;=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=&quot; &lt;<a \
href="mailto:tivv00@gmail.com">tivv00@gmail.com</a>&gt; writes:<br> </div><div \
class="Ih2E3d">&gt; I am not. I can&#39;t see how materialize can multiply number of \
rows it gets<br> &gt; from sort by 100.<br>
<br>
</div>Is it the right-hand input of a merge join? &nbsp;If so you&#39;re looking \
at<br> mark/restore rescans, ie, repeated fetches of the same tuples. &nbsp;There<br>
must be a huge number of duplicate join keys in that relation to make<br>
for such an increase though. &nbsp;Normally the planner avoids putting a<br>
table with lots of duplicates as the RHS of a merge, but if it doesn&#39;t<br>
have good statistics for the join key then it might not realize the<br>
problem.<br>
</blockquote><div><br>OK, thanks for cleaning-up some mystery. <br>But, returning to \
original Q: Do anyone known why does it choose plan from <b>OR-plan.txt</b> instead \
of <b>union-plan.txt</b>? 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.<br> </div></div><br><span style="display: \
none; visibility: hidden;" id="wikEdSetupFlag"></span><span style="display: none; \
visibility: hidden;" id="wikEdDiffSetupFlag"></span>



[prev in list] [next in list] [prev in thread] [next in thread]