The following bug has been logged on the website:
Bug reference: 14642
Logged by: Alexey Bashtanov
Email address: bashtanov@imap.cc
PostgreSQL version: 9.6.2
Operating system: Ubuntu Linux 14.04
Description:
Hello,
Under certain circumstances, an excessive sorting node appears in the
plan.
It happens when at the same time:
1) There is a merge join node with a join by two equality clauses (say,
`innr.p=outr.p AND innr.q=outr.q`);
2) One of the children, say the inner one, comes as a `SELECT p, q FROM ..
WHERE p = 0 GROUP BY p, q` or `GROUP BY q`, doesn't matter.
3) It uses sort+groupAgg, not hashAgg.
Then the result of the grouping, being already sorted by p (which is always
1), and q, gets sorted again for the merge join.
It looks like the grouping subplan is aware of p=1 and simplifies its
pathkeys from [(innr.p, 1), (innr.q)] to [(innr.q)]. However, looks like it
does not share this kind of knowledge with the code that plans the merge
join, so the merge join keeps requiring the data coming in [innr.p, innr.q]
order.
Do blackbox-style subplans, such as subqueries with grouping, share only the
resulting stream order info with the enclosing query, not the equivalence
classes?
This is the example:
---
set enable_hashjoin to off;
set enable_hashagg to off;
set enable_nestloop to off;
explain
select *
from (select outr%2 o2, outr%5 o5from generate_series(1, 10) outrorder by o2, o5
) o
join (select innr%2 i2, innr%5 i5from generate_series(1, 10) innrwhere innr%2 = 0 group by innr%2, innr%5
) i on o.o2 = i.i2 and o.o5 = i.i5;
---
I tried 9.4.11, 9.6.2, and master versions.
Best regards, Alexey Bashtanov
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
