Re: [GENERAL] Query plan question

[Maksim Likharev]

Thanks Tom,
works, have to test performance....


-----Original Message-----
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 7:36 AM
To: Maksim Likharev
Cc: GENERAL
Subject: Re: [GENERAL] Query plan question 


"Maksim Likharev" <[EMAIL PROTECTED]> writes:
> basically I complaining that PG does not do what I told to do or 
> was hoping to do.

Okay, now I get the point: you want to prevent the "pt" sub-select from
being flattened into the outer query.

7.3.1 through 7.3.3 will actually do what you want (they won't flatten a
sub-select that has any sub-selects in its output list) but we got a lot
of flak for that and 7.4 will go back to the prior behavior.  In most
scenarios it's a win for the planner to flatten wherever possible.

Probably the easiest way to handle it is to insert a DISTINCT or LIMIT
clause in the sub-select; that will unconditionally keep the planner
from flattening the sub-select.  For example,

                ...
                        FROM prod.t_p AS p INNER JOIN t_temp AS t 
                                ON p.did = t.did 
                        LEFT OUTER JOIN prod.t_pinv AS pi
                                ON p.kid = pi.kid AND pi.orderid = 'S' 
                        -- hack to keep this separate from outer plan:
                        OFFSET 0
                ) AS pt
                
                        LEFT OUTER JOIN prod.t_dmp AS pdb
                                ON pt.kid = pdb.kid
                ...

I don't foresee any future planner changes that would be likely to
bypass a LIMIT/OFFSET clause.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend