public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v44 3/3] Replace assuming a composite object on a scalar 3+ messages / 3 participants [nested] [flat]
* [PATCH v44 3/3] Replace assuming a composite object on a scalar @ 2021-01-07 08:04 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Dmitrii Dolgov @ 2021-01-07 08:04 UTC (permalink / raw) For jsonb subscripting assignment it could happen that the provided path assumes an object or an array at some level, but the source jsonb has a scalar value there. Originally the update operation will be skipped and no message will be shown that nothing happened. Return an error to indicate such situations. --- src/backend/utils/adt/jsonfuncs.c | 11 +++++++++++ src/test/regress/expected/jsonb.out | 12 ++++++++++++ src/test/regress/sql/jsonb.sql | 8 ++++++++ 3 files changed, 31 insertions(+) diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index f14f6c3191..ebc0b06f5b 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -4943,6 +4943,17 @@ setPath(JsonbIterator **it, Datum *path_elems, break; case WJB_ELEM: case WJB_VALUE: + /* + * If instructed complain about attempts to replace whithin a + * scalar value. + */ + if ((op_type & JB_PATH_FILL_GAPS) && (level < path_len - 1)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot replace existing key"), + errdetail("The path assumes key is a composite object, " + "but it is a scalar value."))); + res = pushJsonbValue(st, r, &v); break; default: diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index b7c268b53f..0df808c36d 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -5134,6 +5134,18 @@ select * from test_jsonb_subscript; 1 | {"a": [null, {"c": [null, null, 1]}]} (1 row) +-- trying replace assuming a composite object, but it's a scalar +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '{"a": 1}'); +update test_jsonb_subscript set test_json['a']['b']['c'] = '1'; +ERROR: cannot replace existing key +DETAIL: The path assumes key is a composite object, but it is a scalar value. +update test_jsonb_subscript set test_json['a'][0]['c'] = '1'; +ERROR: cannot replace existing key +DETAIL: The path assumes key is a composite object, but it is a scalar value. +update test_jsonb_subscript set test_json['a'][0][0] = '1'; +ERROR: cannot replace existing key +DETAIL: The path assumes key is a composite object, but it is a scalar value. -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); to_tsvector diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 0320db0ea4..c62a2f9aec 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1371,6 +1371,14 @@ insert into test_jsonb_subscript values (1, '{"a": []}'); update test_jsonb_subscript set test_json['a'][1]['c'][2] = '1'; select * from test_jsonb_subscript; +-- trying replace assuming a composite object, but it's a scalar + +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '{"a": 1}'); +update test_jsonb_subscript set test_json['a']['b']['c'] = '1'; +update test_jsonb_subscript set test_json['a'][0]['c'] = '1'; +update test_jsonb_subscript set test_json['a'][0][0] = '1'; + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); -- 2.21.0 --i5uwshohlpbxyckd-- ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Does anyone ever use OPTIMIZER_DEBUG? @ 2023-09-28 21:59 Tom Lane <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2023-09-28 21:59 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: PostgreSQL Developers <[email protected]> David Rowley <[email protected]> writes: > In c4a1933b4 I pushed a fix for a 4 year old bug in print_path() where > I'd forgotten to add handling for TidRangePaths while working on > bb437f995. > 4 years is quite a long time for such a bug. Maybe that's because > nobody uses OPTIMIZER_DEBUG. I certainly don't, and Tom mentions [1] > he doesn't either. > Is there anyone out there who uses it? > If not, it's about 320 lines of uselessness. We could also discuss keeping the "tracing" aspect of it, but replacing debug_print_rel with pprint(rel), which'd still allow removal of all the "DEBUG SUPPORT" stuff at the bottom of allpaths.c. That's pretty much all of the maintenance-requiring stuff in it. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Does anyone ever use OPTIMIZER_DEBUG? @ 2023-10-02 23:29 David Rowley <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: David Rowley @ 2023-10-02 23:29 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: PostgreSQL Developers <[email protected]> On Fri, 29 Sept 2023 at 10:59, Tom Lane <[email protected]> wrote: > We could also discuss keeping the "tracing" aspect of it, but > replacing debug_print_rel with pprint(rel), which'd still allow > removal of all the "DEBUG SUPPORT" stuff at the bottom of allpaths.c. > That's pretty much all of the maintenance-requiring stuff in it. To assist discussion, I've attached a patch for that. I likely can't contribute much to that discussion due to being more of an "attach a debugger" person rather than an "add printf statements" person. To eliminate a hurdle for anyone who wants to chip in, I've attached the old and new debug output from the following query: select * from pg_class where oid = 1234; One observation is that the output is quite a bit larger with the patched version and does not seem as useful if you wanted OPTIMIZER_DEBUG to help you figure out why a given Path was chosen. David After canonicalize_qual() {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } RELOPTINFO (pg_class): rows=1 width=273 baserestrictinfo: pg_class.oid = 1234 path list: IdxScan(pg_class) rows=1 cost=0.27..8.29 cheapest parameterized paths: IdxScan(pg_class) rows=1 cost=0.27..8.29 cheapest startup path: IdxScan(pg_class) rows=1 cost=0.27..8.29 cheapest total path: IdxScan(pg_class) rows=1 cost=0.27..8.29 After canonicalize_qual() {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } {RELOPTINFO :reloptkind 0 :relids (b 1) :rows 1 :consider_startup false :consider_param_startup false :consider_parallel true :reltarget {PATHTARGET :exprs ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 7 } {VAR :varno 1 :varattno 2 :vartype 19 :vartypmod -1 :varcollid 950 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 7 } {VAR :varno 1 :varattno 3 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location 7 } {VAR :varno 1 :varattno 4 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 4 :location 7 } {VAR :varno 1 :varattno 5 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 5 :location 7 } {VAR :varno 1 :varattno 6 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 6 :location 7 } {VAR :varno 1 :varattno 7 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 7 :location 7 } {VAR :varno 1 :varattno 8 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 8 :location 7 } {VAR :varno 1 :varattno 9 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 9 :location 7 } {VAR :varno 1 :varattno 10 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 10 :location 7 } {VAR :varno 1 :varattno 11 :vartype 700 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 11 :location 7 } {VAR :varno 1 :varattno 12 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 12 :location 7 } {VAR :varno 1 :varattno 13 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 13 :location 7 } {VAR :varno 1 :varattno 14 :vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 14 :location 7 } {VAR :varno 1 :varattno 15 :vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 15 :location 7 } {VAR :varno 1 :varattno 16 :vartype 18 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 16 :location 7 } {VAR :varno 1 :varattno 17 :vartype 18 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 17 :location 7 } {VAR :varno 1 :varattno 18 :vartype 21 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 18 :location 7 } {VAR :varno 1 :varattno 19 :vartype 21 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 19 :location 7 } {VAR :varno 1 :varattno 20 :vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 20 :location 7 } {VAR :varno 1 :varattno 21 :vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 21 :location 7 } {VAR :varno 1 :varattno 22 :vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 22 :location 7 } {VAR :varno 1 :varattno 23 :vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 23 :location 7 } {VAR :varno 1 :varattno 24 :vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 24 :location 7 } {VAR :varno 1 :varattno 25 :vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 25 :location 7 } {VAR :varno 1 :varattno 26 :vartype 18 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 26 :location 7 } {VAR :varno 1 :varattno 27 :vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 27 :location 7 } {VAR :varno 1 :varattno 28 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 28 :location 7 } {VAR :varno 1 :varattno 29 :vartype 28 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 29 :location 7 } {VAR :varno 1 :varattno 30 :vartype 28 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 30 :location 7 } {VAR :varno 1 :varattno 31 :vartype 1034 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 31 :location 7 } {VAR :varno 1 :varattno 32 :vartype 1009 :vartypmod -1 :varcollid 950 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 32 :location 7 } {VAR :varno 1 :varattno 33 :vartype 194 :vartypmod -1 :varcollid 950 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 33 :location 7 } ) :sortgrouprefs <> :cost.startup 0 :cost.per_tuple 0 :width 774 :has_volatile_expr 0 } :pathlist ( {INDEXPATH :path.pathtype 323 :parent_relids (b 1) :required_outer (b) :path.parallel_aware false :path.parallel_safe true :path.parallel_workers 0 :path.rows 1 :path.startup_cost 0.2725 :path.total_cost 8.290000000000001 :path.pathkeys <> :indexinfo {INDEXOPTINFO :indexoid 2662 :reltablespace 0 :pages 4 :tuples 432 :tree_height 1 :ncolumns 1 :nkeycolumns 1 :indexkeys ( 1) :indexcollations ( 0) :opfamily ( 1989) :opcintype ( 26) :sortopfamily ( 1989) :reverse_sort ( false) :nulls_first ( false) :canreturn ( true) :relam 403 :indpred <> :indextlist ( {TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location -1 } :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } ) :indrestrictinfo ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :predOK false :unique true :immediate true :hypothetical false :amcanorderbyop false :amoptionalkey true :amsearcharray true :amsearchnulls true :amhasgettuple true :amhasgetbitmap true :amcanparallel true :amcanmarkpos true } :indexclauses ( {INDEXCLAUSE :rinfo {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } :indexquals ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :lossy false :indexcol 0 :indexcols <> } ) :indexorderbys <> :indexorderbycols <> :indexscandir 1 :indextotalcost 4.28 :indexselectivity 0.0023148148148148147 } ) :ppilist <> :partial_pathlist <> :cheapest_startup_path {INDEXPATH :path.pathtype 323 :parent_relids (b 1) :required_outer (b) :path.parallel_aware false :path.parallel_safe true :path.parallel_workers 0 :path.rows 1 :path.startup_cost 0.2725 :path.total_cost 8.290000000000001 :path.pathkeys <> :indexinfo {INDEXOPTINFO :indexoid 2662 :reltablespace 0 :pages 4 :tuples 432 :tree_height 1 :ncolumns 1 :nkeycolumns 1 :indexkeys ( 1) :indexcollations ( 0) :opfamily ( 1989) :opcintype ( 26) :sortopfamily ( 1989) :reverse_sort ( false) :nulls_first ( false) :canreturn ( true) :relam 403 :indpred <> :indextlist ( {TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location -1 } :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } ) :indrestrictinfo ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :predOK false :unique true :immediate true :hypothetical false :amcanorderbyop false :amoptionalkey true :amsearcharray true :amsearchnulls true :amhasgettuple true :amhasgetbitmap true :amcanparallel true :amcanmarkpos true } :indexclauses ( {INDEXCLAUSE :rinfo {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } :indexquals ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :lossy false :indexcol 0 :indexcols <> } ) :indexorderbys <> :indexorderbycols <> :indexscandir 1 :indextotalcost 4.28 :indexselectivity 0.0023148148148148147 } :cheapest_total_path {INDEXPATH :path.pathtype 323 :parent_relids (b 1) :required_outer (b) :path.parallel_aware false :path.parallel_safe true :path.parallel_workers 0 :path.rows 1 :path.startup_cost 0.2725 :path.total_cost 8.290000000000001 :path.pathkeys <> :indexinfo {INDEXOPTINFO :indexoid 2662 :reltablespace 0 :pages 4 :tuples 432 :tree_height 1 :ncolumns 1 :nkeycolumns 1 :indexkeys ( 1) :indexcollations ( 0) :opfamily ( 1989) :opcintype ( 26) :sortopfamily ( 1989) :reverse_sort ( false) :nulls_first ( false) :canreturn ( true) :relam 403 :indpred <> :indextlist ( {TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location -1 } :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } ) :indrestrictinfo ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :predOK false :unique true :immediate true :hypothetical false :amcanorderbyop false :amoptionalkey true :amsearcharray true :amsearchnulls true :amhasgettuple true :amhasgetbitmap true :amcanparallel true :amcanmarkpos true } :indexclauses ( {INDEXCLAUSE :rinfo {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } :indexquals ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :lossy false :indexcol 0 :indexcols <> } ) :indexorderbys <> :indexorderbycols <> :indexscandir 1 :indextotalcost 4.28 :indexselectivity 0.0023148148148148147 } :cheapest_unique_path <> :cheapest_parameterized_paths ( {INDEXPATH :path.pathtype 323 :parent_relids (b 1) :required_outer (b) :path.parallel_aware false :path.parallel_safe true :path.parallel_workers 0 :path.rows 1 :path.startup_cost 0.2725 :path.total_cost 8.290000000000001 :path.pathkeys <> :indexinfo {INDEXOPTINFO :indexoid 2662 :reltablespace 0 :pages 4 :tuples 432 :tree_height 1 :ncolumns 1 :nkeycolumns 1 :indexkeys ( 1) :indexcollations ( 0) :opfamily ( 1989) :opcintype ( 26) :sortopfamily ( 1989) :reverse_sort ( false) :nulls_first ( false) :canreturn ( true) :relam 403 :indpred <> :indextlist ( {TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location -1 } :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } ) :indrestrictinfo ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :predOK false :unique true :immediate true :hypothetical false :amcanorderbyop false :amoptionalkey true :amsearcharray true :amsearchnulls true :amhasgettuple true :amhasgetbitmap true :amcanparallel true :amcanmarkpos true } :indexclauses ( {INDEXCLAUSE :rinfo {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } :indexquals ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :lossy false :indexcol 0 :indexcols <> } ) :indexorderbys <> :indexorderbycols <> :indexscandir 1 :indextotalcost 4.28 :indexselectivity 0.0023148148148148147 } ) :direct_lateral_relids (b) :lateral_relids (b) :relid 1 :reltablespace 0 :rtekind 0 :min_attr -6 :max_attr 33 :nulling_relids (b) :lateral_vars <> :lateral_referencers (b) :indexlist ( {INDEXOPTINFO :indexoid 3455 :reltablespace 0 :pages 2 :tuples 432 :tree_height 0 :ncolumns 2 :nkeycolumns 2 :indexkeys ( 9 8) :indexcollations ( 0 0) :opfamily ( 1989 1989) :opcintype ( 26 26) :sortopfamily ( 1989 1989) :reverse_sort ( false false) :nulls_first ( false false) :canreturn ( true true) :relam 403 :indpred <> :indextlist ( {TARGETENTRY :expr {VAR :varno 1 :varattno 9 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 9 :location -1 } :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } {TARGETENTRY :expr {VAR :varno 1 :varattno 8 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 8 :location -1 } :resno 2 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } ) :indrestrictinfo ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :predOK false :unique false :immediate true :hypothetical false :amcanorderbyop false :amoptionalkey true :amsearcharray true :amsearchnulls true :amhasgettuple true :amhasgetbitmap true :amcanparallel true :amcanmarkpos true } {INDEXOPTINFO :indexoid 2663 :reltablespace 0 :pages 5 :tuples 432 :tree_height 1 :ncolumns 2 :nkeycolumns 2 :indexkeys ( 2 3) :indexcollations ( 950 0) :opfamily ( 1994 1989) :opcintype ( 19 26) :sortopfamily ( 1994 1989) :reverse_sort ( false false) :nulls_first ( false false) :canreturn ( true true) :relam 403 :indpred <> :indextlist ( {TARGETENTRY :expr {VAR :varno 1 :varattno 2 :vartype 19 :vartypmod -1 :varcollid 950 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location -1 } :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } {TARGETENTRY :expr {VAR :varno 1 :varattno 3 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location -1 } :resno 2 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } ) :indrestrictinfo ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :predOK false :unique true :immediate true :hypothetical false :amcanorderbyop false :amoptionalkey true :amsearcharray true :amsearchnulls true :amhasgettuple true :amhasgetbitmap true :amcanparallel true :amcanmarkpos true } {INDEXOPTINFO :indexoid 2662 :reltablespace 0 :pages 4 :tuples 432 :tree_height 1 :ncolumns 1 :nkeycolumns 1 :indexkeys ( 1) :indexcollations ( 0) :opfamily ( 1989) :opcintype ( 26) :sortopfamily ( 1989) :reverse_sort ( false) :nulls_first ( false) :canreturn ( true) :relam 403 :indpred <> :indextlist ( {TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location -1 } :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false } ) :indrestrictinfo ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :predOK false :unique true :immediate true :hypothetical false :amcanorderbyop false :amoptionalkey true :amsearcharray true :amsearchnulls true :amhasgettuple true :amhasgetbitmap true :amcanparallel true :amcanmarkpos true } ) :statlist <> :pages 13 :tuples 432 :allvisfrac 0.8461538461538461 :eclass_indexes (b 0) :subroot <> :subplan_params <> :rel_parallel_workers -1 :amflags 1 :serverid 0 :userid 0 :useridiscurrent false :unique_for_rels <> :non_unique_for_rels <> :baserestrictinfo ( {RESTRICTINFO :clause {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } :is_pushed_down true :can_join false :pseudoconstant false :has_clone false :is_clone false :leakproof false :has_volatile 2 :security_level 0 :num_base_rels 1 :clause_relids (b 1) :required_relids (b 1) :incompatible_relids (b) :outer_relids (b) :left_relids (b 1) :right_relids (b) :orclause <> :rinfo_serial 1 :eval_cost.startup 0 :eval_cost.per_tuple 0.0025 :norm_selec 0.0023148148148148147 :outer_selec -1 :mergeopfamilies (o 1989) :left_em {EQUIVALENCEMEMBER :em_expr {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } :em_relids (b 1) :em_is_const false :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :right_em {EQUIVALENCEMEMBER :em_expr {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } :em_relids (b) :em_is_const true :em_is_child false :em_datatype 26 :em_jdomain {JOINDOMAIN :jd_relids (b 1) } } :outer_is_left false :hashjoinoperator 0 :left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1 :right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0 } ) :baserestrictcost.startup 0 :baserestrictcost.per_tuple 0.0025 :baserestrict_min_security 0 :joininfo <> :has_eclass_joins false :consider_partitionwise_join false :top_parent_relids (b) :nparts -1 :partbounds_merged false :partition_qual <> :live_parts (b) :all_partrels (b) } Attachments: [text/plain] old_debug_print_rel.txt (1003B, ../../CAApHDvoMLP3ZpAE4_zivkT1GJU293BT7Yy-Kmo-kxU+Nc_-EWg@mail.gmail.com/2-old_debug_print_rel.txt) download | inline: After canonicalize_qual() {OPEXPR :opno 607 :opfuncid 184 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ( {VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 29 } {CONST :consttype 26 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 33 :constvalue 4 [ -46 4 0 0 0 0 0 0 ] } ) :location 32 } RELOPTINFO (pg_class): rows=1 width=273 baserestrictinfo: pg_class.oid = 1234 path list: IdxScan(pg_class) rows=1 cost=0.27..8.29 cheapest parameterized paths: IdxScan(pg_class) rows=1 cost=0.27..8.29 cheapest startup path: IdxScan(pg_class) rows=1 cost=0.27..8.29 cheapest total path: IdxScan(pg_class) rows=1 cost=0.27..8.29 [text/plain] new_pprint.txt (73.5K, ../../CAApHDvoMLP3ZpAE4_zivkT1GJU293BT7Yy-Kmo-kxU+Nc_-EWg@mail.gmail.com/3-new_pprint.txt) download [application/octet-stream] replace_debug_print_rel_with_pprint.patch (8.5K, ../../CAApHDvoMLP3ZpAE4_zivkT1GJU293BT7Yy-Kmo-kxU+Nc_-EWg@mail.gmail.com/4-replace_debug_print_rel_with_pprint.patch) download | inline diff: diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index b11b939989..4108a617f2 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -563,7 +563,7 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, set_cheapest(rel); #ifdef OPTIMIZER_DEBUG - debug_print_rel(root, rel); + pprint(rel); #endif } @@ -3483,7 +3483,7 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) set_cheapest(rel); #ifdef OPTIMIZER_DEBUG - debug_print_rel(root, rel); + pprint(rel); #endif } } @@ -4351,7 +4351,7 @@ generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel) continue; #ifdef OPTIMIZER_DEBUG - debug_print_rel(root, child_rel); + pprint(child_rel); #endif live_children = lappend(live_children, child_rel); @@ -4368,325 +4368,3 @@ generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel) add_paths_to_append_rel(root, rel, live_children); list_free(live_children); } - - -/***************************************************************************** - * DEBUG SUPPORT - *****************************************************************************/ - -#ifdef OPTIMIZER_DEBUG - -static void -print_relids(PlannerInfo *root, Relids relids) -{ - int x; - bool first = true; - - x = -1; - while ((x = bms_next_member(relids, x)) >= 0) - { - if (!first) - printf(" "); - if (x < root->simple_rel_array_size && - root->simple_rte_array[x]) - printf("%s", root->simple_rte_array[x]->eref->aliasname); - else - printf("%d", x); - first = false; - } -} - -static void -print_restrictclauses(PlannerInfo *root, List *clauses) -{ - ListCell *l; - - foreach(l, clauses) - { - RestrictInfo *c = lfirst(l); - - print_expr((Node *) c->clause, root->parse->rtable); - if (lnext(clauses, l)) - printf(", "); - } -} - -static void -print_path(PlannerInfo *root, Path *path, int indent) -{ - const char *ptype; - bool join = false; - Path *subpath = NULL; - int i; - - switch (nodeTag(path)) - { - case T_Path: - switch (path->pathtype) - { - case T_SeqScan: - ptype = "SeqScan"; - break; - case T_SampleScan: - ptype = "SampleScan"; - break; - case T_FunctionScan: - ptype = "FunctionScan"; - break; - case T_TableFuncScan: - ptype = "TableFuncScan"; - break; - case T_ValuesScan: - ptype = "ValuesScan"; - break; - case T_CteScan: - ptype = "CteScan"; - break; - case T_NamedTuplestoreScan: - ptype = "NamedTuplestoreScan"; - break; - case T_Result: - ptype = "Result"; - break; - case T_WorkTableScan: - ptype = "WorkTableScan"; - break; - default: - ptype = "???Path"; - break; - } - break; - case T_IndexPath: - ptype = "IdxScan"; - break; - case T_BitmapHeapPath: - ptype = "BitmapHeapScan"; - break; - case T_BitmapAndPath: - ptype = "BitmapAndPath"; - break; - case T_BitmapOrPath: - ptype = "BitmapOrPath"; - break; - case T_TidPath: - ptype = "TidScan"; - break; - case T_TidRangePath: - ptype = "TidRangePath"; - break; - case T_SubqueryScanPath: - ptype = "SubqueryScan"; - break; - case T_ForeignPath: - ptype = "ForeignScan"; - break; - case T_CustomPath: - ptype = "CustomScan"; - break; - case T_NestPath: - ptype = "NestLoop"; - join = true; - break; - case T_MergePath: - ptype = "MergeJoin"; - join = true; - break; - case T_HashPath: - ptype = "HashJoin"; - join = true; - break; - case T_AppendPath: - ptype = "Append"; - break; - case T_MergeAppendPath: - ptype = "MergeAppend"; - break; - case T_GroupResultPath: - ptype = "GroupResult"; - break; - case T_MaterialPath: - ptype = "Material"; - subpath = ((MaterialPath *) path)->subpath; - break; - case T_MemoizePath: - ptype = "Memoize"; - subpath = ((MemoizePath *) path)->subpath; - break; - case T_UniquePath: - ptype = "Unique"; - subpath = ((UniquePath *) path)->subpath; - break; - case T_GatherPath: - ptype = "Gather"; - subpath = ((GatherPath *) path)->subpath; - break; - case T_GatherMergePath: - ptype = "GatherMerge"; - subpath = ((GatherMergePath *) path)->subpath; - break; - case T_ProjectionPath: - ptype = "Projection"; - subpath = ((ProjectionPath *) path)->subpath; - break; - case T_ProjectSetPath: - ptype = "ProjectSet"; - subpath = ((ProjectSetPath *) path)->subpath; - break; - case T_SortPath: - ptype = "Sort"; - subpath = ((SortPath *) path)->subpath; - break; - case T_IncrementalSortPath: - ptype = "IncrementalSort"; - subpath = ((SortPath *) path)->subpath; - break; - case T_GroupPath: - ptype = "Group"; - subpath = ((GroupPath *) path)->subpath; - break; - case T_UpperUniquePath: - ptype = "UpperUnique"; - subpath = ((UpperUniquePath *) path)->subpath; - break; - case T_AggPath: - ptype = "Agg"; - subpath = ((AggPath *) path)->subpath; - break; - case T_GroupingSetsPath: - ptype = "GroupingSets"; - subpath = ((GroupingSetsPath *) path)->subpath; - break; - case T_MinMaxAggPath: - ptype = "MinMaxAgg"; - break; - case T_WindowAggPath: - ptype = "WindowAgg"; - subpath = ((WindowAggPath *) path)->subpath; - break; - case T_SetOpPath: - ptype = "SetOp"; - subpath = ((SetOpPath *) path)->subpath; - break; - case T_RecursiveUnionPath: - ptype = "RecursiveUnion"; - break; - case T_LockRowsPath: - ptype = "LockRows"; - subpath = ((LockRowsPath *) path)->subpath; - break; - case T_ModifyTablePath: - ptype = "ModifyTable"; - break; - case T_LimitPath: - ptype = "Limit"; - subpath = ((LimitPath *) path)->subpath; - break; - default: - ptype = "???Path"; - break; - } - - for (i = 0; i < indent; i++) - printf("\t"); - printf("%s", ptype); - - if (path->parent) - { - printf("("); - print_relids(root, path->parent->relids); - printf(")"); - } - if (path->param_info) - { - printf(" required_outer ("); - print_relids(root, path->param_info->ppi_req_outer); - printf(")"); - } - printf(" rows=%.0f cost=%.2f..%.2f\n", - path->rows, path->startup_cost, path->total_cost); - - if (path->pathkeys) - { - for (i = 0; i < indent; i++) - printf("\t"); - printf(" pathkeys: "); - print_pathkeys(path->pathkeys, root->parse->rtable); - } - - if (join) - { - JoinPath *jp = (JoinPath *) path; - - for (i = 0; i < indent; i++) - printf("\t"); - printf(" clauses: "); - print_restrictclauses(root, jp->joinrestrictinfo); - printf("\n"); - - if (IsA(path, MergePath)) - { - MergePath *mp = (MergePath *) path; - - for (i = 0; i < indent; i++) - printf("\t"); - printf(" sortouter=%d sortinner=%d materializeinner=%d\n", - ((mp->outersortkeys) ? 1 : 0), - ((mp->innersortkeys) ? 1 : 0), - ((mp->materialize_inner) ? 1 : 0)); - } - - print_path(root, jp->outerjoinpath, indent + 1); - print_path(root, jp->innerjoinpath, indent + 1); - } - - if (subpath) - print_path(root, subpath, indent + 1); -} - -void -debug_print_rel(PlannerInfo *root, RelOptInfo *rel) -{ - ListCell *l; - - printf("RELOPTINFO ("); - print_relids(root, rel->relids); - printf("): rows=%.0f width=%d\n", rel->rows, rel->reltarget->width); - - if (rel->baserestrictinfo) - { - printf("\tbaserestrictinfo: "); - print_restrictclauses(root, rel->baserestrictinfo); - printf("\n"); - } - - if (rel->joininfo) - { - printf("\tjoininfo: "); - print_restrictclauses(root, rel->joininfo); - printf("\n"); - } - - printf("\tpath list:\n"); - foreach(l, rel->pathlist) - print_path(root, lfirst(l), 1); - if (rel->cheapest_parameterized_paths) - { - printf("\n\tcheapest parameterized paths:\n"); - foreach(l, rel->cheapest_parameterized_paths) - print_path(root, lfirst(l), 1); - } - if (rel->cheapest_startup_path) - { - printf("\n\tcheapest startup path:\n"); - print_path(root, rel->cheapest_startup_path, 1); - } - if (rel->cheapest_total_path) - { - printf("\n\tcheapest total path:\n"); - print_path(root, rel->cheapest_total_path, 1); - } - printf("\n"); - fflush(stdout); -} - -#endif /* OPTIMIZER_DEBUG */ diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 50bc3b503a..7b896d821e 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -63,10 +63,6 @@ extern void create_partial_bitmap_paths(PlannerInfo *root, RelOptInfo *rel, extern void generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel); -#ifdef OPTIMIZER_DEBUG -extern void debug_print_rel(PlannerInfo *root, RelOptInfo *rel); -#endif - /* * indxpath.c * routines to generate index paths ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-02 23:29 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-01-07 08:04 [PATCH v44 3/3] Replace assuming a composite object on a scalar Dmitrii Dolgov <[email protected]> 2023-09-28 21:59 Re: Does anyone ever use OPTIMIZER_DEBUG? Tom Lane <[email protected]> 2023-10-02 23:29 ` Re: Does anyone ever use OPTIMIZER_DEBUG? David Rowley <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox