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

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