Thread: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

I just read the lead ups to this post - didn't see Tom and Greg's comments.

The approach we took was to recognize the ordering of child nodes and propagate that to the append in the special case of only one child (after CE).  This is the most common use-case in 'partitioning', and so is an easy, high payoff low amount of code fix.

I'd suggest we take this approach while also considering a more powerful set of append merge capabilities.

- Luke

Msg is shrt cuz m on ma treo

 -----Original Message-----
From:   Luke Lonergan [mailto:LLonergan@greenplum.com]
Sent:   Saturday, October 27, 2007 03:14 PM Eastern Standard Time
To:     Heikki Linnakangas; Anton
Cc:     pgsql-performance@postgresql.org
Subject:        Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1

And I repeat - 'we fixed that and submitted a patch' - you can find it in the unapplied patches queue.

The patch isn't ready for application, but someone can quickly implement it I'd expect.

- Luke

Msg is shrt cuz m on ma treo

 -----Original Message-----
From:   Heikki Linnakangas [mailto:heikki@enterprisedb.com]
Sent:   Saturday, October 27, 2007 05:20 AM Eastern Standard Time
To:     Anton
Cc:     pgsql-performance@postgresql.org
Subject:        Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1

Anton wrote:
> I repost here my original question "Why it no uses indexes?" (on
> partitioned table and ORDER BY indexed_field DESC LIMIT 1), if you
> mean that you miss this discussion.

As I said back then:

The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
below the append node.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Luke Lonergan" <LLonergan@greenplum.com> writes:

> The approach we took was to recognize the ordering of child nodes and
> propagate that to the append in the special case of only one child (after
> CE). This is the most common use-case in 'partitioning', and so is an easy,
> high payoff low amount of code fix.

Ah yes, we should definitely try to prune singleton append nodes. On a lark I
had tried to do precisely that to see what would happen but ran into precisely
the problem you had to solve here with your pullup_vars function. That's one
of the functions which wasn't included in the original patch so I'll look at
the patch from the queue to see what's involved.

Actually currently it's not a common case because we can't eliminate the
parent partition. I have some ideas for how to deal with that but haven't
written them up yet.

In theory if we can preserve ordering across append nodes there's no good
reason to prune them. But generally I think simplifying the plan is good if
only to present simpler plans to the user.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com