Re: Using IN with subselect

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