Search Postgresql Archives

Re: Subselects - recursion problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux