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

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: v12.0: ERROR: could not find pathkey item to sort
Date: 2019-10-11 14:37:03
Message-ID: 20191011143703.GN10470@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)entry=0) at elog.c:411
#1 0x000000000087a959 in elog_finish (elevel=elevel(at)entry=20, fmt=fmt(at)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(at)entry=0x0, adjust_tlist_in_place=<optimized out>,
adjust_tlist_in_place(at)entry=false, p_numsortkeys=p_numsortkeys(at)entry=0x7ffc4b2e10c4, p_sortColIdx=p_sortColIdx(at)entry=0x7ffc4b2e10c8, p_sortOperators=p_sortOperators(at)entry=0x7ffc4b2e10d0,
p_collations=p_collations(at)entry=0x7ffc4b2e10d8, p_nullsFirst=p_nullsFirst(at)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(at)entry=0x7f7fdc3ac6b0, best_path=0x7f7cb8410cc8, flags=flags(at)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(at)entry=0x7f7fdc3ac6b0, best_path=best_path(at)entry=0x7f7cb8410d58, flags=flags(at)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(at)entry=0x7f7fdc3ac6b0, best_path=0x7f7cb8446cd8, flags=flags(at)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(at)entry=0x7f7fdc3ac6b0, best_path=0x7f7cb84486c8, flags=flags(at)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(at)entry=0x7f7fdc34b948, best_path=best_path(at)entry=0x7f7cb8448db8, flags=<optimized out>, flags(at)entry=0) at createplan.c:670
#15 0x00000000006a6d31 in create_plan_recurse (root=root(at)entry=0x7f7fdc34b948, best_path=0x7f7cb8448db8, flags=flags(at)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(at)entry=0x7f7fdc34b948, best_path=best_path(at)entry=0x7f7cb844fb80) at createplan.c:1020
#18 0x00000000006a6d75 in create_plan_recurse (root=root(at)entry=0x7f7fdc34b948, best_path=0x7f7cb844fb80, flags=flags(at)entry=1) at createplan.c:393
#19 0x00000000006a9739 in create_plan (root=root(at)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(at)entry=0x1bd2308, cursorOptions=cursorOptions(at)entry=256, boundParams=boundParams(at)entry=0x0) at postgres.c:878
#22 0x000000000075fbee in pg_plan_queries (querytrees=<optimized out>, cursorOptions=cursorOptions(at)entry=256, boundParams=boundParams(at)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(at)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(at)entry=3, argv=argv(at)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(at)entry=0x0, adjust_tlist_in_place=<optimized out>,
adjust_tlist_in_place(at)entry=false, p_numsortkeys=p_numsortkeys(at)entry=0x7ffc4b2e10c4, p_sortColIdx=p_sortColIdx(at)entry=0x7ffc4b2e10c8, p_sortOperators=p_sortOperators(at)entry=0x7ffc4b2e10d0,
p_collations=p_collations(at)entry=0x7ffc4b2e10d8, p_nullsFirst=p_nullsFirst(at)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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2019-10-11 14:48:37 Re: v12.0: ERROR: could not find pathkey item to sort
Previous Message Jeremy Finzel 2019-10-11 14:31:08 Re: BRIN index which is much faster never chosen by planner