Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Pgsql Performance > Re: plan differ...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 5 of 6 Topic 4051 of 4352
Post > Topic >>

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

by ftm.van.vugt@[EMAIL PROTECTED] (Frank van Vugt) May 6, 2008 at 05:27 PM

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




 6 Posts in Topic:
plan difference between set-returning function with ROWS within
ftm.van.vugt@[EMAIL PROTE  2008-05-06 10:21:43 
Re: plan difference between set-returning function with ROWS wit
lists@[EMAIL PROTECTED]   2008-05-06 11:53:17 
Re: plan difference between set-returning function with ROWS wit
ftm.van.vugt@[EMAIL PROTE  2008-05-06 13:55:52 
Re: plan difference between set-returning function with ROWS wit
tgl@[EMAIL PROTECTED] (T  2008-05-06 10:17:10 
Re: plan difference between set-returning function with ROWS wit
ftm.van.vugt@[EMAIL PROTE  2008-05-06 17:27:40 
Re: plan difference between set-returning function with ROWS wit
mmoncure@[EMAIL PROTECTED  2008-05-10 07:42:28 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Sun Oct 12 9:18:41 CDT 2008.