Jeff Davis wrote:
On Fri, 2010-09-10 at 08:10 -0700, Chris Travers wrote:
Just adding my voice to the "fix it" camp. Is there any reason the
table scans in this sort of thing cannot be independently planned?
I don't think it's about independent planning. For instance, AVG clearly
can't be planned this way, there are particular properties of MAX that
allow the optimization:
1. MAX(x) can be rewritten as: ORDER BY x DESC LIMIT 1
2. The MAX of set S is the MAX of the MAXes of each partition of S
The optimizer knows about the former, but not the latter.
Regards,
Jeff Davis
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.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
