Re: Intersect/Union X AND/OR

Thiago Godoi wrote:
> 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.

Well, there are usually several ways to execute a query internally,
intsersect is using a different, and faster, method.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +