Re: BUG #5652: Optimizer does wrong thing with partitioned tables - Mailing list pgsql-bugs

From Chris Travers
Subject Re: BUG #5652: Optimizer does wrong thing with partitioned tables
Date
Msg-id AANLkTi=te=Sj_AGMw7kmr7UmbnWOANt6E5Zbk0XbgucS@mail.gmail.com
Whole thread Raw
In response to Re: BUG #5652: Optimizer does wrong thing with partitioned tables  (Mladen Gogala <mgogala@vmsinfo.com>)
List pgsql-bugs
On Fri, Sep 10, 2010 at 1:53 PM, Mladen Gogala <mgogala@vmsinfo.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

pgsql-bugs by date:

Previous
From: Jeff Davis
Date:
Subject: Re: BUG #5652: Optimizer does wrong thing with partitioned tables
Next
From: Mladen Gogala
Date:
Subject: Re: BUG #5652: Optimizer does wrong thing with partitioned tables