Re: BUG #5652: Optimizer does wrong thing with partitioned tables

On Fri, Sep 10, 2010 at 1:53 PM, Mladen Gogala <mgogala(at)vmsinfo(dot)com> wrote:

> Jeff, that's the problem. Functions like "MAX" are rather ordinary and
> frequently used. Using sequential scan to read all partitions is the wrong
> thing to do. I agree that AVG() cannot be computed using index but MAX() and
> MIN() can. I will send you personally 2 versions of a script that I am still
> writing, just to see to what extent do I go to get the necessary
> performance. Optimizer definitely needs fixes when it comes to partitions.
>

IIRC, the planner already has been tweaked to allow index scans on MAX
for single tables. This of course did not happen within a stable
branch.

The question over whether this is a "bug" or a "feature" depends to a
large extent on how one defines a bug. I would be inclined to call
this a "bug" for discussion purposes since it causes the planner to
make plan choices that are well known to be problematic in these
cases, but it I would not be in favor of correcting this in a stable
branch. My reading of the change log is that it is rare that changes
to long-standing behavior in general, and particularly for the
optimizer, occur within a stable branch. Given that this is
long-standing behavior, I think it is worth accepting that it is not a
"bug" we might want fixed within a stable release.

I agree with the suggestion that a discussion start on -hackers. I
still think it is a problem that should be fixed. Just not in a
stable branch, esp. because this has a reasonable workaround (changing
to an order by... limit 1).

I guess what I am trying to suggest here is that "bug" and "feature"
are not distinct categories which have no overlap. Where software,
like an RDBMS, is mission-critical, I think it is a good practice to
do what the Pg developers do and avoid making unnecessary changes
within a stable release. This means that some "bugs" should be
treated as "features" where the behavior is longstanding, a workaround
is possible, and the fix likely to involve changes to important
components.

Best Wishes,
Chris Travers