column order in GROUP BY
От | Neil Conway |
---|---|
Тема | column order in GROUP BY |
Дата | |
Msg-id | 1141351003.24513.35.camel@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: column order in GROUP BY
|
Список | pgsql-hackers |
The query optimizer currently does not consider reordering a query's grouping columns. While the order in which ORDER BY columns are specified affects the semantics of the query, AFAICS GROUP BY's column order does not. Reordering a query's grouping columns would allow the optimizer to avoid some unnecessary sorts; for example, given an index on (a, b), we should be able to avoid a sort in this query: SELECT a, b, max(c) FROM t1 GROUP BY b, a; which the optimizer is currently incapable of doing. I think fixing this properly would require teaching the planner that certain PathKeys are unordered, so the planner can pick whichever order is best. That looks like a fairly invasive change: the assumption that PathKeyItems are ordered looks pretty widespread. A simple hack might help with a subset of this problem, though. For queries with both ORDER BY and GROUP BY clauses, we can sort the grouping columns according to their position in the ORDER BY list. So, given a query like: SELECT a, b, max(c) FROM t1 GROUP BY a, b ORDER BY b; We can avoid the redundant sort for the ORDER BY by grouping by (b, a) instead. Attached is a proof-of-concept patch that implements this, although it's an enormous kludge. Thoughts? -Neil
Вложения
В списке pgsql-hackers по дате отправления: