From: Frank van Vugt <ftm.van.vugt@foxi.nl>
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
Message-ID: <200805061727.41210.ftm.van.vugt@foxi.nl> (raw)
In-Reply-To: <13551.1210083430@sss.pgh.pa.us>
References: <200805061021.43448.ftm.van.vugt@foxi.nl>
<13551.1210083430@sss.pgh.pa.us>
> > 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ftm.van.vugt@foxi.nl, tgl@sss.pgh.pa.us
Subject: Re: plan difference between set-returning function with ROWS within IN() and a plain join
In-Reply-To: <200805061727.41210.ftm.van.vugt@foxi.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox