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.







--
Thiago Godoi