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. +
