From: | "Maksim Likharev" <mlikharev(at)aurigin(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "GENERAL" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query plan question |
Date: | 2003-06-27 01:14:13 |
Message-ID: | 56510AAEF435D240958D1CE8C6B1770A014A0D0E@mailc03.aurigin.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks Tom,
works, have to test performance....
-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, June 26, 2003 7:36 AM
To: Maksim Likharev
Cc: GENERAL
Subject: Re: [GENERAL] Query plan question
"Maksim Likharev" <mlikharev(at)aurigin(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-06-27 02:31:24 | Re: deleting procs |
Previous Message | Milet Maricuelo | 2003-06-27 00:49:27 | column to row |