Re: Intersect/Union X AND/OR

Thanks for the answers.

I found one of these cases , but I'm trying to understand this. Why the
performance is better? The number of tuples is making the difference?

My original query :

select table1.id
from table1, (select function(12345) id) table2
where table1.kind = 1234
and table1.id = table2.id

"Nested Loop (cost=0.00..6.68 rows=1 width=12)"
" Join Filter: ()"
" -> Seq Scan on recorte (cost=0.00..6.39 rows=1 width=159)"
" Filter: (id = 616)"
" -> Result (cost=0.00..0.26 rows=1 width=0)"

-- function() returns a resultset

I tryed with explicit join and "in" , but the plan is the same.

When I changed the query to use intersect :

(select table1.id from table1 where table1.kind = 1234)
Intersect
(select function(12345) id)

The new plan is :

"HashSetOp Intersect (cost=0.00..6.67 rows=1 width=80)"
" -> Append (cost=0.00..6.67 rows=2 width=80)"
" -> Subquery Scan on "*SELECT* 1" (cost=0.00..6.40 rows=1
width=159)"
" -> Seq Scan on recorte (cost=0.00..6.39 rows=1 width=159)"
" Filter: (id = 616)"
" -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.27 rows=1
width=0)"
" -> Result (cost=0.00..0.26 rows=1 width=0)"

The second plan is about 10 times faster than the first one.

2011/12/2 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Fri, Dec 2, 2011 at 1:49 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Thiago Godoi wrote:
> >> Hi all,
> >>
> >> I found this presentation from B. Momjian:
> >>
> >> http://momjian.us/main/writings/pgsql/performance.pdf
> >>
> >> I'm interested in what he said about " Intersect/Union X AND/OR " , Can
> I
> >> find a transcription or a video of this presentation? Can anyone
> explain it
> >> to me?
> >
> > Well, there is a recording of the webcast on the EnterpriseDB web site,
> > but I am afraid they only allow viewing of 3+ hour webcasts by
> > EnterpriseDB customers.
> >
> > The idea is that a query that uses an OR can be rewritten as two SELECTs
> > with a UNION between them. I have seen rare cases where this is a win,
> > so I mentioned it in that talk. Intersection is similarly possible for
> > AND in WHERE clauses.
>
> I've seen this as well. Also boolean set EXCEPT is useful as well in
> the occasional oddball case.
>
> merlin
>

--
Thiago Godoi