Re: v12.0: ERROR: could not find pathkey item to sort

On Fri, Oct 11, 2019 at 10:48:37AM -0400, Tom Lane wrote:
> Justin Pryzby <pryzby(at)telsasoft(dot)com> writes:
> > The view is actually a join of two relkind=p partitioned tables (which I
> > will acknowledge probably performs poorly).
>
> Could you provide a self-contained test case please?

Working on it. FWIW explain for a v11 customer looks like this:

Nested Loop (cost=10000011818.10..10000076508.23 rows=734500 width=159)
Join Filter: ((s.site_location = ''::text) OR (s.site_office = (COALESCE(huawei_ggsn_201610.ne_name, huawei_ggsn_gw_201610.ne_name))))
-> Group (cost=11818.10..11946.31 rows=2937 width=40)
Group Key: (COALESCE(huawei_ggsn_201610.start_time, huawei_ggsn_gw_201610.start_time)), (COALESCE(huawei_ggsn_201610.ne_name, huawei_ggsn_gw_201610.ne_name))
-> Merge Append (cost=11818.10..11931.59 rows=2944 width=40)
Sort Key: (COALESCE(huawei_ggsn_201610.start_time, huawei_ggsn_gw_201610.start_time)), (COALESCE(huawei_ggsn_201610.ne_name, huawei_ggsn_gw_201610.ne_name))
-> Group (cost=332.48..333.10 rows=83 width=40)
Group Key: (COALESCE(huawei_ggsn_201610.start_time, huawei_ggsn_gw_201610.start_time)), (COALESCE(huawei_ggsn_201610.ne_name, huawei_ggsn_gw_201610.ne_name))
-> Sort (cost=332.48..332.69 rows=83 width=40)
Sort Key: (COALESCE(huawei_ggsn_201610.start_time, huawei_ggsn_gw_201610.start_time)), (COALESCE(huawei_ggsn_201610.ne_name, huawei_ggsn_gw_201610.ne_name))
-> Hash Full Join (cost=46.48..329.84 rows=83 width=40)
Hash Cond: ((huawei_ggsn_201610.ne_name = huawei_ggsn_gw_201610.ne_name) AND (huawei_ggsn_201610.ggsn_function = huawei_ggsn_gw_201610.ggsn_function) AND (huawei_ggsn_201610.start_time = huawei_
ggsn_gw_201610.start_time) AND (huawei_ggsn_201610.interval_seconds = huawei_ggsn_gw_201610.interval_seconds) AND (huawei_ggsn_201610.device_id = huawei_ggsn_gw_201610.device_id) AND (huawei_ggsn_201610.c_134710251 = huawei_ggs
n_gw_201610.c_134710251) AND (huawei_ggsn_201610.c_134710252 = huawei_ggsn_gw_201610.c_134710252) AND (huawei_ggsn_201610.c_134710253 = huawei_ggsn_gw_201610.c_134710253) AND (huawei_ggsn_201610.ne_id = huawei_ggsn_gw_201610.ne
_id) AND (huawei_ggsn_201610.ugw_function = huawei_ggsn_gw_201610.ugw_function))
Filter: ((COALESCE(huawei_ggsn_201610.start_time, huawei_ggsn_gw_201610.start_time) >= '2019-10-01 00:00:00-11'::timestamp with time zone) AND (COALESCE(huawei_ggsn_201610.start_time, huawei_ggs
n_gw_201610.start_time) < '2019-10-02 00:00:00-11'::timestamp with time zone))
-> Seq Scan on huawei_ggsn_201610 (cost=0.00..255.44 rows=744 width=94)
-> Hash (cost=20.44..20.44 rows=744 width=94)
-> Seq Scan on huawei_ggsn_gw_201610 (cost=0.00..20.44 rows=744 width=94)
[...]

I'm suspecting this; is it useful to test with this commit reverted ?

commit 8edd0e79460b414b1d971895312e549e95e12e4f
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Mon Mar 25 15:42:35 2019 -0400

Suppress Append and MergeAppend plan nodes that have a single child.