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