Re: v12.0: ERROR: could not find pathkey item to sort - Mailing list pgsql-hackers

On Fri, Oct 11, 2019 at 10:48:37AM -0400, Tom Lane wrote:
> Justin Pryzby <pryzby@telsasoft.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-0100: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@sss.pgh.pa.us>
Date:   Mon Mar 25 15:42:35 2019 -0400

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