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@xxxxxxxxx>
On Fri, Dec 2, 2011 at 1:49 PM, Bruce Momjian <bruce@xxxxxxxxxx> wrote:I've seen this as well. Also boolean set EXCEPT is useful as well in
> 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.
the occasional oddball case.
merlin
--
Thiago Godoi
