[BUGS] BUG #14642: Excessive sorting node appears in the plan

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