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@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
