From: | Dave Smith <dave(dot)smith(at)candata(dot)com> |
---|---|
To: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Using IN with subselect |
Date: | 2004-11-25 17:19:23 |
Message-ID: | 1101403163.7960.38.camel@playpen.candata.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Julian Scarfe | 2004-11-25 17:23:35 | How can I change a cast from explicit only to implicit? |
Previous Message | Martijn van Oosterhout | 2004-11-25 17:11:12 | Re: Using IN with subselect |