From: Frank van Vugt <ftm.van.vugt@foxi.nl>
Organization: Foxi Consultants in Technology BV
To: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: plan difference between set-returning function with ROWS within
	IN() and a plain join
Date: Tue, 6 May 2008 17:27:40 +0200
User-Agent: KMail/1.9.5
References: <200805061021.43448.ftm.van.vugt@foxi.nl>
	<13551.1210083430@sss.pgh.pa.us>
In-Reply-To: <13551.1210083430@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200805061727.41210.ftm.van.vugt@foxi.nl>

> > db=# explain analyse
> > 	select sum(base_total_val)
> > 	from sales_invoice
> > 	where id in (select id from si_credit_tree(80500007));
>
> Did you check whether this query even gives the right answer?

You knew the right answer to that already ;)

> I think you forgot the alias foo(id) in the subselect and it's
> actually reducing to "where id in (id)", ie, TRUE.

Tricky, but completely obvious once pointed out, that's _exactly_ what was 
happening.


db=# explain analyse
	select sum(base_total_val)
	from sales_invoice
	where id in (select id from si_credit_tree(80500007) foo(id));
                                                                     QUERY 
PLAN                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=42.79..42.80 rows=1 width=8) (actual time=0.440..0.441 
rows=1 loops=1)
   ->  Nested Loop  (cost=1.31..42.77 rows=5 width=8) (actual 
time=0.346..0.413 rows=5 loops=1)
         ->  HashAggregate  (cost=1.31..1.36 rows=5 width=4) (actual 
time=0.327..0.335 rows=5 loops=1)
               ->  Function Scan on si_credit_tree foo  (cost=0.00..1.30 
rows=5 width=4) (actual time=0.300..0.306 rows=5 loops=1)
         ->  Index Scan using sales_invoice_pkey on sales_invoice  
(cost=0.00..8.27 rows=1 width=12) (actual time=0.006..0.008 rows=1 loops=5)
               Index Cond: (sales_invoice.id = foo.id)

Total runtime: 0.559 ms


Thanks for the replies!


-- 
Best,


Frank.