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.