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

List:       pgsql-performance
Subject:    Re: [PERFORM] Can Postgres use an INDEX over an OR?
From:       <tivv00 () gmail ! com>
Date:       2009-07-27 14:33:32
Message-ID: 331e40660907270733g4faee764oe1666d2190684c39 () mail ! gmail ! com
[Download RAW message or body]

27 липня 2009 р. 17:18 Tom Lane <tgl@sss.pgh.pa.us> написав:

> =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com> writes:
> > Actually what I am talking about is to make OR with UNION (or UNION-like
> > because it's a little different depending on input rows uniqueness) as an
> > option. All of OR parts can use/not use different strategies (including
> > multiple different idexes or hash joins).
>
> AFAICS you're proposing re-inventing the old implementation of OR'd
> indexscans.  We took that out when we added bitmap scans because it
> didn't have any performance advantage over BitmapOr.
>

It's not tied to indexscans at all. Different parts can do (as in UNION)
totally different strategy - e.g. perform two hash joins or perform merge
join for one part and nested loop for another or ...

As of performance - see above in this thread. UNION now often provides much
better performance when different parts of OR expression involve different
additional tables.

[Attachment #3 (text/html)]

<br><br><div class="gmail_quote">27 липня 2009 р. 17:18 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="im">=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= &lt;<a \
href="mailto:tivv00@gmail.com">tivv00@gmail.com</a>&gt; writes:<br> &gt; Actually \
what I am talking about is to make OR with UNION (or UNION-like<br> &gt; because \
it&#39;s a little different depending on input rows uniqueness) as an<br> &gt; \
option. All of OR parts can use/not use different strategies (including<br> &gt; \
multiple different idexes or hash joins).<br> <br>
</div>AFAICS you&#39;re proposing re-inventing the old implementation of OR&#39;d<br>
indexscans.  We took that out when we added bitmap scans because it<br>
didn&#39;t have any performance advantage over BitmapOr.<br>
</blockquote><div><br>It&#39;s not tied to indexscans at all. Different parts can do \
(as in UNION) totally different strategy - e.g. perform two hash joins or perform \
merge join for one part and nested loop for another or ... <br> <br>As of performance \
- see above in this thread. UNION now often provides much better performance when \
different parts of OR expression involve different additional \
tables.<br></div></div><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]