Re: Subselects - recursion problem - Mailing list pgsql-general

From Philip Rhoades
Subject Re: Subselects - recursion problem
Date
Msg-id 1117449135.13881.56.camel@phil.chu.com.au
Whole thread Raw
In response to Re: Subselects - recursion problem  (Martijn van Oosterhout <kleptog@svana.org>)
Responses Re: Subselects - recursion problem
List pgsql-general
Martijn,


On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote:
> On Mon, May 30, 2005 at 07:33:04PM +1000, Philip Rhoades wrote:
> > People,
> >
> > The following script works (I have confirmed it by doing two separate
> > views and doing a select on them) - but I don't understand why there
> > isn't a recursion problem with c1.policy and c2.policy - is there some
> > sort of trick happening?
>
> "recursion problem" ? It's called a correlated subquery. SQL is
> declarative, you state what you want and the database figures out how
> to get the answer for you. I think you need to go and read up on the
> basics of SQL.


Declarative or not, it looks strange having the output of the first
select dependent on a second select, which is dependent on the output of
the first select . .


> If you want to see *how* the database is working out the answer, use
> explain and it'll display the query plan.


 Sort  (cost=402711.95..402711.96 rows=1 width=20)
   Sort Key: loc, lob, policy
   ->  Index Scan using crec_9 on crec c1  (cost=0.00..402711.94 rows=1
width=20)
         Index Cond: (t_diss = '2'::bpchar)
         Filter: ((t_type = '1'::bpchar) AND (recon = 'Y'::bpchar) AND
(policy = (subplan)))
         SubPlan
           ->  Index Scan using crec_1 on crec c2  (cost=0.00..6.01
rows=1 width=10)
                 Index Cond: (($0 = loc) AND ($1 = lob) AND ($2 =
policy))
                 Filter: ((t_type = '1'::bpchar) AND (t_diss =
'0'::bpchar) AND (recon = 'N'::bpchar))


I can see that PG doesn't use "policy" in the filter of the subplan at
all but that doesn't help very much . .

Thanks anyway.

Phil.
--
Philip Rhoades

Pricom Pty Limited  (ACN 003 252 275  ABN 91 003 252 275)
GPO Box 3411
Sydney NSW      2001
Australia
Mobile:  +61:0411-185-652
Fax:  +61:2:8923-5363
E-mail:  phil@chu.com.au



pgsql-general by date:

Previous
From: Martijn van Oosterhout
Date:
Subject: Re: Subselects - recursion problem
Next
From: Martijn van Oosterhout
Date:
Subject: Re: Subselects - recursion problem