Re: Using IN with subselect - Mailing list pgsql-general
From | Dave Smith |
---|---|
Subject | Re: Using IN with subselect |
Date | |
Msg-id | 1101403163.7960.38.camel@playpen.candata.com Whole thread Raw |
In response to | Re: Using IN with subselect (Martijn van Oosterhout <kleptog@svana.org>) |
Responses |
Re: Using IN with subselect
Re: Using IN with subselect |
List | pgsql-general |
Well here is explain. I would guess that it is executed each time .. function any different? HashAggregate (cost=288.32..288.32 rows=1 width=32) -> Hash IN Join (cost=288.18..288.31 rows=1 width=32) Hash Cond: (("outer".gl_num)::text = lpad(ltrim(("inner".account_num)::text, '0'::text), 9, ' '::text)) -> Subquery Scan journal_all (cost=282.36..282.45 rows=2 width=64) -> Unique (cost=282.36..282.43 rows=2 width=159) -> Sort (cost=282.36..282.36 rows=2 width=159) Sort Key: objectid, owner_oid, source_code, posting_date, control_num, reference, gl_num, gl_amt, distributed_amt, "comment", operator_id, branch_id, company_id -> Append (cost=0.00..282.35 rows=2 width=159) -> Subquery Scan "*SELECT* 1" (cost=0.00..265.24 rows=1 width=159) -> Index Scan using journal_9 on journal (cost=0.00..265.23 rows=1 width=159) Index Cond: (company_id = 1000) Filter: ((posting_date >= '2004-01-01'::date) AND (posting_date <= '2004-01-31'::date)) -> Subquery Scan "*SELECT* 2" (cost=0.00..17.10 rows=1 width=159) -> Index Scan using journal_hist_7 on journal_hist (cost=0.00..17.09 rows=1 width=159) Index Cond: (company_id = 1000) Filter: ((posting_date >= '2004-01-01'::date) AND (posting_date <= '2004-01-31'::date)) -> Hash (cost=5.83..5.83 rows=1 width=13) -> Index Scan using glmast_index3 on glmast (cost=0.00..5.83 rows=1 width=13) Index Cond: ((company_id = 1000) AND ((control_type)::text = 'F'::text)) On Thu, 2004-11-25 at 12:11, Martijn van Oosterhout wrote: > Running EXPLAIN over the query will tell you... > > On Thu, Nov 25, 2004 at 11:49:06AM -0500, Dave Smith wrote: > > I have a query with an in subquery like > > > > where x in (select x from y); > > > > Now the subquery is not related to the outer query so it always returns > > the same set. Is this subselect executed each time or just once? If it > > is executed each time, if I create a function would that then be only > > executed once? > > > > -- > > Dave Smith > > CANdata Systems Ltd > > 416-493-9020 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org -- Dave Smith CANdata Systems Ltd 416-493-9020
pgsql-general by date: