Date: Fri, 11 Oct 2019 09:37:03 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@lists.postgresql.org
Subject: v12.0: ERROR:  could not find pathkey item to sort
Message-ID: <20191011143703.GN10470@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

I've reduced the failing query as much as possible to this:

-- This is necessary to fail:
SET enable_nestloop=off;

SELECT * FROM
        (SELECT start_time, t1.site_id
        FROM pgw_kpi_view t1
        -- Apparently the where clause is necessary to fail...
        WHERE (start_time>='2019-10-10' AND start_time<'2019-10-11')
        -- The group by MAY be necessary to fail...
        GROUP BY 1,2
        ) AS data
JOIN sites ON ( sites.site_location='' OR sites.site_office=data.site_id)

The view is actually a join of two relkind=p partitioned tables (which I
will acknowledge probably performs poorly).

(gdb) bt
#0  errfinish (dummy=dummy@entry=0) at elog.c:411
#1  0x000000000087a959 in elog_finish (elevel=elevel@entry=20, fmt=fmt@entry=0x9d93d8 "could not find pathkey item to sort") at elog.c:1365
#2  0x00000000006a587f in prepare_sort_from_pathkeys (lefttree=0x7f7cb84492e8, pathkeys=<optimized out>, relids=0x7f7cb8410700, reqColIdx=reqColIdx@entry=0x0, adjust_tlist_in_place=<optimized out>, 
    adjust_tlist_in_place@entry=false, p_numsortkeys=p_numsortkeys@entry=0x7ffc4b2e10c4, p_sortColIdx=p_sortColIdx@entry=0x7ffc4b2e10c8, p_sortOperators=p_sortOperators@entry=0x7ffc4b2e10d0, 
    p_collations=p_collations@entry=0x7ffc4b2e10d8, p_nullsFirst=p_nullsFirst@entry=0x7ffc4b2e10e0) at createplan.c:5893
#3  0x00000000006a5a6a in make_sort_from_pathkeys (lefttree=<optimized out>, pathkeys=<optimized out>, relids=<optimized out>) at createplan.c:6020
#4  0x00000000006a6e30 in create_sort_plan (flags=4, best_path=0x7f7cb8410cc8, root=0x7f7fdc3ac6b0) at createplan.c:1985
#5  create_plan_recurse (root=root@entry=0x7f7fdc3ac6b0, best_path=0x7f7cb8410cc8, flags=flags@entry=4) at createplan.c:459
#6  0x00000000006a6e4e in create_group_plan (best_path=0x7f7cb8410d58, root=0x7f7fdc3ac6b0) at createplan.c:2012
#7  create_plan_recurse (root=root@entry=0x7f7fdc3ac6b0, best_path=best_path@entry=0x7f7cb8410d58, flags=flags@entry=1) at createplan.c:464
#8  0x00000000006a8278 in create_merge_append_plan (flags=4, best_path=0x7f7cb8446cd8, root=0x7f7fdc3ac6b0) at createplan.c:1333
#9  create_plan_recurse (root=root@entry=0x7f7fdc3ac6b0, best_path=0x7f7cb8446cd8, flags=flags@entry=4) at createplan.c:402
#10 0x00000000006a6e4e in create_group_plan (best_path=0x7f7cb84486c8, root=0x7f7fdc3ac6b0) at createplan.c:2012
#11 create_plan_recurse (root=root@entry=0x7f7fdc3ac6b0, best_path=0x7f7cb84486c8, flags=flags@entry=1) at createplan.c:464
#12 0x00000000006a9739 in create_plan (root=0x7f7fdc3ac6b0, best_path=<optimized out>) at createplan.c:325
#13 0x00000000006aa988 in create_subqueryscan_plan (scan_clauses=0x0, tlist=0x7f7cb8450820, best_path=0x7f7cb8448db8, root=0x7f7fdc34b948) at createplan.c:3385
#14 create_scan_plan (root=root@entry=0x7f7fdc34b948, best_path=best_path@entry=0x7f7cb8448db8, flags=<optimized out>, flags@entry=0) at createplan.c:670
#15 0x00000000006a6d31 in create_plan_recurse (root=root@entry=0x7f7fdc34b948, best_path=0x7f7cb8448db8, flags=flags@entry=0) at createplan.c:427
#16 0x00000000006a983a in create_nestloop_plan (best_path=0x7f7cb844fb80, root=0x7f7fdc34b948) at createplan.c:4008
#17 create_join_plan (root=root@entry=0x7f7fdc34b948, best_path=best_path@entry=0x7f7cb844fb80) at createplan.c:1020
#18 0x00000000006a6d75 in create_plan_recurse (root=root@entry=0x7f7fdc34b948, best_path=0x7f7cb844fb80, flags=flags@entry=1) at createplan.c:393
#19 0x00000000006a9739 in create_plan (root=root@entry=0x7f7fdc34b948, best_path=<optimized out>) at createplan.c:325
#20 0x00000000006b5a04 in standard_planner (parse=0x1bd2308, cursorOptions=256, boundParams=0x0) at planner.c:413
#21 0x000000000075fb2e in pg_plan_query (querytree=querytree@entry=0x1bd2308, cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0) at postgres.c:878
#22 0x000000000075fbee in pg_plan_queries (querytrees=<optimized out>, cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0) at postgres.c:968
#23 0x000000000076007a in exec_simple_query (
    query_string=0x1ba36e0 "SELECT * FROM\n\t(SELECT start_time, t1.site_id\n\tFROM pgw_kpi_view t1\n\t\n\tWHERE (start_time>='2019-10-10' AND start_time<'2019-10-11')\n\t\n\tGROUP BY 1,2\n\t) AS data\nJOIN sites ON ( sites.site_location='' OR"...) at postgres.c:1143
#24 0x0000000000761212 in PostgresMain (argc=<optimized out>, argv=argv@entry=0x1bd8e70, dbname=0x1bd8d10 "ts", username=<optimized out>) at postgres.c:4236
#25 0x0000000000483d02 in BackendRun (port=<optimized out>, port=<optimized out>) at postmaster.c:4431
#26 BackendStartup (port=0x1bd5190) at postmaster.c:4122
#27 ServerLoop () at postmaster.c:1704
#28 0x00000000006f0b1f in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x1b9e280) at postmaster.c:1377
#29 0x0000000000484c93 in main (argc=3, argv=0x1b9e280) at main.c:228


bt f:

#2  0x00000000006a587f in prepare_sort_from_pathkeys (lefttree=0x7f7cb84492e8, pathkeys=<optimized out>, relids=0x7f7cb8410700, reqColIdx=reqColIdx@entry=0x0, adjust_tlist_in_place=<optimized out>, 
    adjust_tlist_in_place@entry=false, p_numsortkeys=p_numsortkeys@entry=0x7ffc4b2e10c4, p_sortColIdx=p_sortColIdx@entry=0x7ffc4b2e10c8, p_sortOperators=p_sortOperators@entry=0x7ffc4b2e10d0, 
    p_collations=p_collations@entry=0x7ffc4b2e10d8, p_nullsFirst=p_nullsFirst@entry=0x7ffc4b2e10e0) at createplan.c:5893
        sortexpr = <optimized out>
        ec = 0x7f7cb8edbe28
        em = <optimized out>
        tle = <optimized out>
        pathkey = <optimized out>
        pk_datatype = <optimized out>
        sortop = <optimized out>
        j = <optimized out>
        tlist = 0x7f7cb8451bb8
        i = 0x7f7cb8edc2d8
        numsortkeys = 0
        sortColIdx = 0x7f7cb8451c58
        sortOperators = 0x7f7cb8451c70
        collations = 0x7f7cb8451c88
        nullsFirst = 0x7f7cb8451ca0
        __func__ = "prepare_sort_from_pathkeys"
#3  0x00000000006a5a6a in make_sort_from_pathkeys (lefttree=<optimized out>, pathkeys=<optimized out>, relids=<optimized out>) at createplan.c:6020
        numsortkeys = 32636
        sortColIdx = 0x7f7cb8447468
        sortOperators = 0x7f7cb83fa278
        collations = 0x0
        nullsFirst = 0x7f7cb8edc2f8