Thread: Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1

Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1

From
"Luke Lonergan"
Date:

Works great - plans no longer sort, but rather use indices as expected.  It's in use in Greenplum now.

It's a simple approach, should easily extend from gpdb to postgres. The patch is against gpdb so someone needs to 'port' it.

- Luke

Msg is shrt cuz m on ma treo

 -----Original Message-----
From:   Simon Riggs [mailto:simon@2ndquadrant.com]
Sent:   Saturday, October 27, 2007 05:34 PM Eastern Standard Time
To:     Luke Lonergan
Cc:     Heikki Linnakangas; Anton; pgsql-performance@postgresql.org
Subject:        Re: [PERFORM] partitioned table and ORDER BY indexed_field DESCLIMIT 1

On Sat, 2007-10-27 at 15:12 -0400, Luke Lonergan wrote:
> And I repeat - 'we fixed that and submitted a patch' - you can find it
> in the unapplied patches queue.

I got the impression it was a suggestion rather than a tested patch,
forgive me if that was wrong.

Did the patch work? Do you have timings/different plan?

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1

From
Simon Riggs
Date:
On Sat, 2007-10-27 at 17:48 -0400, Luke Lonergan wrote:
> Works great - plans no longer sort, but rather use indices as
> expected.  It's in use in Greenplum now.
>
> It's a simple approach, should easily extend from gpdb to postgres.
> The patch is against gpdb so someone needs to 'port' it.

The part of the patch that didn't work for me was the nrels==1 bit. The
way it currently works there is only ever 0 or 2+ rels. The normal
Postgres code has to cater for the possibility of a non-empty parent
table, which seems to destroy the possibility of using this technique.

I agree its annoying and I have a way of doing this, but that's an 8.4
thing now.

Anybody think different?

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> I agree its annoying and I have a way of doing this, but that's an 8.4
> thing now.

It was an 8.4 thing quite some time ago, since no working patch was ever
submitted.

            regards, tom lane

Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1

From
Simon Riggs
Date:
On Sun, 2007-10-28 at 12:53 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > I agree its annoying and I have a way of doing this, but that's an 8.4
> > thing now.
>
> It was an 8.4 thing quite some time ago, since no working patch was ever
> submitted.

Sorry, I meant that the problem itself is annoying, not anything to do
with patches. I'm very happy with all that we've done.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com