Re: plan difference between set-returning function with ROWS within IN() and a plain join

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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance