Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-26 Thread Tom Lane
Gregory Stark <[EMAIL PROTECTED]> writes: > "Tom Lane" <[EMAIL PROTECTED]> writes: >> It might still be interesting sometime to have a more bespoke >> representation for a merged variable, but I guess we don't need >> it just for this. > It might have an advantage if you're doing a three-way outer

Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-26 Thread Gregory Stark
"Tom Lane" <[EMAIL PROTECTED]> writes: > I wrote: >> Gregory Stark <[EMAIL PROTECTED]> writes: >>> as a path key, though we would have to create an equivalence class >>> and add COALESCE(id2,id1) to it as well I think. > >> No, because those two expressions are not equivalent. (Hmm ... squint >>

Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-26 Thread Tom Lane
I wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: >> as a path key, though we would have to create an equivalence class >> and add COALESCE(id2,id1) to it as well I think. > No, because those two expressions are not equivalent. (Hmm ... squint > ... but full merge join is pretty much symmetric

Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-26 Thread Tom Lane
Simon Riggs <[EMAIL PROTECTED]> writes: > On Tue, 2008-02-26 at 10:34 -0500, Tom Lane wrote: > and add COALESCE(id2,id1) to it as well I think. >> No, because those two expressions are not equivalent. (Hmm ... squint >> ... but full merge join is pretty much symmetric, so it's not clear >> why it

Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-26 Thread Simon Riggs
On Tue, 2008-02-26 at 10:34 -0500, Tom Lane wrote: > > and add COALESCE(id2,id1) to it as well I think. > > No, because those two expressions are not equivalent. (Hmm ... squint > ... but full merge join is pretty much symmetric, so it's not clear > why it should matter which side is left or righ

Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-26 Thread Tom Lane
Gregory Stark <[EMAIL PROTECTED]> writes: > "Alexey A. Nalbat" <[EMAIL PROTECTED]> writes: >> Yes. But may be the FULL MERGE JOIN could be improved, because it >> is ordered, it actually has "outer path's path key": "coalesce(id1,id2)". No, it does not have the outer path's path key. The outer pa

Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-26 Thread Gregory Stark
"Alexey A. Nalbat" <[EMAIL PROTECTED]> writes: > Yes. But may be the FULL MERGE JOIN could be improved, because it > is ordered, it actually has "outer path's path key": "coalesce(id1,id2)". Hm, there's more going on here than meets the eye. If you changed the query slightly Postgres would actua

Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-26 Thread Alexey A. Nalbat
> I found comment in src/backend/optimizer/path/pathkeys.c: > * EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as > * having the outer path's path keys, because null lefthand rows may be > * inserted at random points. It must be treated as unsorted. > > How can I get rid of this so

Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-24 Thread Simon Riggs
On Sat, 2008-02-23 at 14:49 -0600, Decibel! wrote: > On Feb 21, 2008, at 4:08 AM, Alexey Nalbat wrote: > > I found comment in src/backend/optimizer/path/pathkeys.c: > > * EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as > > * having the outer path's path keys, because null lefthan

Re: [GENERAL] unnesesary sorting after Merge Full Join

2008-02-23 Thread Decibel!
On Feb 21, 2008, at 4:08 AM, Alexey Nalbat wrote: I'd like to use ORDER BY in any specified order and LIMIT, OFFSET for paging query results. The query is FULL OUTER JOIN of two tables by field id. I think the results of Merge Full Join to be ordered by some "combined id". And there is no need