=== Applying patches on top of PostgreSQL commit ID e8bfad4ca842733b957c01e732ec009778f952cd === /etc/rc.d/jail: WARNING: Per-jail configuration via jail_* variables is obsolete. Please consider migrating to /etc/jail.conf. Sat Nov 8 04:56:20 UTC 2025 On branch cf/5093 nothing to commit, working tree clean === using 'git am' to apply patch ./v0-0001-Employ-EquivalenceClass-to-adjust-ndistinct-estim.patch === Applying: Employ EquivalenceClass to adjust ndistinct estimation. Using index info to reconstruct a base tree... M contrib/postgres_fdw/postgres_fdw.c M src/backend/optimizer/path/costsize.c M src/backend/optimizer/path/indxpath.c M src/backend/optimizer/plan/createplan.c M src/backend/optimizer/plan/planner.c M src/backend/optimizer/prep/prepunion.c M src/backend/optimizer/util/pathnode.c M src/backend/utils/adt/selfuncs.c M src/include/nodes/pathnodes.h M src/include/optimizer/cost.h M src/include/utils/selfuncs.h M src/test/regress/expected/incremental_sort.out M src/test/regress/expected/stats.out M src/test/regress/sql/incremental_sort.sql M src/test/regress/sql/stats.sql Falling back to patching base and 3-way merge... Auto-merging src/test/regress/sql/stats.sql Auto-merging src/test/regress/sql/incremental_sort.sql CONFLICT (content): Merge conflict in src/test/regress/sql/incremental_sort.sql Auto-merging src/test/regress/expected/stats.out Auto-merging src/test/regress/expected/incremental_sort.out CONFLICT (content): Merge conflict in src/test/regress/expected/incremental_sort.out Auto-merging src/include/utils/selfuncs.h Auto-merging src/include/optimizer/cost.h Auto-merging src/include/nodes/pathnodes.h Auto-merging src/backend/utils/adt/selfuncs.c Auto-merging src/backend/optimizer/util/pathnode.c CONFLICT (content): Merge conflict in src/backend/optimizer/util/pathnode.c Auto-merging src/backend/optimizer/prep/prepunion.c Auto-merging src/backend/optimizer/plan/planner.c CONFLICT (content): Merge conflict in src/backend/optimizer/plan/planner.c Auto-merging src/backend/optimizer/plan/createplan.c Auto-merging src/backend/optimizer/path/indxpath.c Auto-merging src/backend/optimizer/path/costsize.c CONFLICT (content): Merge conflict in src/backend/optimizer/path/costsize.c Auto-merging contrib/postgres_fdw/postgres_fdw.c error: Failed to merge in the changes. hint: Use 'git am --show-current-patch=diff' to see the failed patch Patch failed at 0001 Employ EquivalenceClass to adjust ndistinct estimation. When you have resolved this problem, run "git am --continue". If you prefer to skip this patch, run "git am --skip" instead. To restore the original branch and stop patching, run "git am --abort". Unstaged changes after reset: M contrib/postgres_fdw/postgres_fdw.c M src/backend/optimizer/path/costsize.c M src/backend/optimizer/path/equivclass.c M src/backend/optimizer/path/indxpath.c M src/backend/optimizer/plan/createplan.c M src/backend/optimizer/plan/planner.c M src/backend/optimizer/prep/prepunion.c M src/backend/optimizer/util/pathnode.c M src/backend/utils/adt/selfuncs.c M src/include/nodes/pathnodes.h M src/include/optimizer/cost.h M src/include/utils/selfuncs.h M src/test/regress/expected/incremental_sort.out M src/test/regress/expected/stats.out M src/test/regress/sql/incremental_sort.sql M src/test/regress/sql/stats.sql === using patch(1) to apply patch ./v0-0001-Employ-EquivalenceClass-to-adjust-ndistinct-estim.patch === patching file contrib/postgres_fdw/postgres_fdw.c Hunk #1 succeeded at 3366 (offset -4 lines). patching file src/backend/optimizer/path/costsize.c Hunk #1 succeeded at 1973 (offset -26 lines). Hunk #2 succeeded at 1986 (offset -26 lines). Hunk #3 succeeded at 1996 (offset -26 lines). Hunk #4 FAILED at 2528. Hunk #5 succeeded at 2855 (offset 6 lines). Hunk #6 succeeded at 2878 (offset 6 lines). Hunk #7 succeeded at 3658 (offset 6 lines). Hunk #8 succeeded at 6587 (offset 23 lines). 1 out of 8 hunks FAILED -- saving rejects to file src/backend/optimizer/path/costsize.c.rej patching file src/backend/optimizer/path/equivclass.c patching file src/backend/optimizer/path/indxpath.c Hunk #1 succeeded at 2401 (offset 1 line). patching file src/backend/optimizer/plan/createplan.c Hunk #1 succeeded at 5427 (offset -96 lines). patching file src/backend/optimizer/plan/planner.c Hunk #1 succeeded at 151 (offset 7 lines). Hunk #2 succeeded at 3684 (offset 84 lines). Hunk #3 succeeded at 3724 (offset 84 lines). Hunk #4 succeeded at 3751 (offset 84 lines). Hunk #5 succeeded at 3763 (offset 84 lines). Hunk #6 succeeded at 7119 with fuzz 2 (offset 3045 lines). Hunk #7 FAILED at 4846. Hunk #8 FAILED at 5017. Hunk #9 succeeded at 7469 (offset 111 lines). 2 out of 9 hunks FAILED -- saving rejects to file src/backend/optimizer/plan/planner.c.rej patching file src/backend/optimizer/prep/prepunion.c Hunk #1 succeeded at 679 (offset 14 lines). patching file src/backend/optimizer/util/pathnode.c Hunk #1 FAILED at 1858. Hunk #2 succeeded at 2815 (offset -244 lines). 1 out of 2 hunks FAILED -- saving rejects to file src/backend/optimizer/util/pathnode.c.rej patching file src/backend/utils/adt/selfuncs.c Hunk #1 succeeded at 3454 (offset 10 lines). Hunk #2 succeeded at 3491 (offset 10 lines). Hunk #3 succeeded at 3525 (offset 10 lines). patching file src/include/nodes/pathnodes.h Hunk #1 succeeded at 1611 (offset 110 lines). Hunk #2 succeeded at 1628 (offset 110 lines). patching file src/include/optimizer/cost.h patching file src/include/utils/selfuncs.h Hunk #1 succeeded at 218 (offset 2 lines). patching file src/test/regress/expected/incremental_sort.out Hunk #1 FAILED at 1722. 1 out of 1 hunk FAILED -- saving rejects to file src/test/regress/expected/incremental_sort.out.rej patching file src/test/regress/expected/stats.out Hunk #1 succeeded at 1910 (offset 42 lines). patching file src/test/regress/sql/incremental_sort.sql Hunk #1 FAILED at 298. 1 out of 1 hunk FAILED -- saving rejects to file src/test/regress/sql/incremental_sort.sql.rej patching file src/test/regress/sql/stats.sql Hunk #1 succeeded at 944 (offset 19 lines). Unstaged changes after reset: M contrib/postgres_fdw/postgres_fdw.c M src/backend/optimizer/path/costsize.c M src/backend/optimizer/path/equivclass.c M src/backend/optimizer/path/indxpath.c M src/backend/optimizer/plan/createplan.c M src/backend/optimizer/plan/planner.c M src/backend/optimizer/prep/prepunion.c M src/backend/optimizer/util/pathnode.c M src/backend/utils/adt/selfuncs.c M src/include/nodes/pathnodes.h M src/include/optimizer/cost.h M src/include/utils/selfuncs.h M src/test/regress/expected/stats.out M src/test/regress/sql/stats.sql Removing src/backend/optimizer/path/costsize.c.rej Removing src/backend/optimizer/plan/planner.c.rej Removing src/backend/optimizer/util/pathnode.c.rej Removing src/test/regress/expected/incremental_sort.out.rej Removing src/test/regress/sql/incremental_sort.sql.rej === using 'git apply' to apply patch ./v0-0001-Employ-EquivalenceClass-to-adjust-ndistinct-estim.patch === Applied patch to 'contrib/postgres_fdw/postgres_fdw.c' cleanly. Applied patch to 'src/backend/optimizer/path/costsize.c' with conflicts. Applied patch to 'src/backend/optimizer/path/equivclass.c' cleanly. Applied patch to 'src/backend/optimizer/path/indxpath.c' cleanly. Applied patch to 'src/backend/optimizer/plan/createplan.c' cleanly. Applied patch to 'src/backend/optimizer/plan/planner.c' with conflicts. Applied patch to 'src/backend/optimizer/prep/prepunion.c' cleanly. Applied patch to 'src/backend/optimizer/util/pathnode.c' with conflicts. Applied patch to 'src/backend/utils/adt/selfuncs.c' cleanly. Applied patch to 'src/include/nodes/pathnodes.h' cleanly. Applied patch to 'src/include/optimizer/cost.h' cleanly. Applied patch to 'src/include/utils/selfuncs.h' cleanly. Applied patch to 'src/test/regress/expected/incremental_sort.out' with conflicts. Applied patch to 'src/test/regress/expected/stats.out' cleanly. Applied patch to 'src/test/regress/sql/incremental_sort.sql' with conflicts. Applied patch to 'src/test/regress/sql/stats.sql' cleanly. U src/backend/optimizer/path/costsize.c U src/backend/optimizer/plan/planner.c U src/backend/optimizer/util/pathnode.c U src/test/regress/expected/incremental_sort.out U src/test/regress/sql/incremental_sort.sql diff --cc src/backend/optimizer/path/costsize.c index 8335cf5b5c5,5b51d020b7f..00000000000 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@@ -2578,8 -2527,8 +2527,13 @@@ cost_memoize_rescan(PlannerInfo *root, est_cache_entries = floor(hash_mem_bytes / est_entry_bytes); /* estimate on the distinct number of parameter values */ ++<<<<<<< ours + ndistinct = estimate_num_groups(root, mpath->param_exprs, est_calls, NULL, + &estinfo); ++======= + ndistinct = estimate_num_groups(root, mpath->param_exprs, calls, NULL, + &estinfo, NULL); ++>>>>>>> theirs /* * When the estimation fell back on using a default value, it's a bit too diff --cc src/backend/optimizer/plan/planner.c index c4fd646b999,c6881362637..00000000000 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@@ -4145,11 -4066,18 +4148,21 @@@ create_ordinary_grouping_paths(PlannerI /* Gather any partially grouped partial paths. */ if (partially_grouped_rel && partially_grouped_rel->partial_pathlist) - { gather_grouping_paths(root, partially_grouped_rel); - set_cheapest(partially_grouped_rel); - } ++<<<<<<< ours + /* Now choose the best path(s) for partially_grouped_rel. */ + if (partially_grouped_rel && partially_grouped_rel->pathlist) + set_cheapest(partially_grouped_rel); ++======= + /* + * Estimate number of groups. + */ + dNumGroups = get_number_of_groups(root, cheapest_path->parent->relids, + cheapest_path->rows, + gd, + extra->targetList); ++>>>>>>> theirs /* Build final grouping paths */ add_paths_to_grouping_rel(root, input_rel, grouped_rel, diff --cc src/backend/optimizer/util/pathnode.c index e4fd6950fad,9652094b47b..00000000000 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@@ -1741,6 -1713,247 +1741,250 @@@ create_memoize_path(PlannerInfo *root, } /* ++<<<<<<< ours ++======= + * create_unique_path + * Creates a path representing elimination of distinct rows from the + * input data. Distinct-ness is defined according to the needs of the + * semijoin represented by sjinfo. If it is not possible to identify + * how to make the data unique, NULL is returned. + * + * If used at all, this is likely to be called repeatedly on the same rel; + * and the input subpath should always be the same (the cheapest_total path + * for the rel). So we cache the result. + */ + UniquePath * + create_unique_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, + SpecialJoinInfo *sjinfo) + { + UniquePath *pathnode; + Path sort_path; /* dummy for result of cost_sort */ + Path agg_path; /* dummy for result of cost_agg */ + MemoryContext oldcontext; + int numCols; + + /* Caller made a mistake if subpath isn't cheapest_total ... */ + Assert(subpath == rel->cheapest_total_path); + Assert(subpath->parent == rel); + /* ... or if SpecialJoinInfo is the wrong one */ + Assert(sjinfo->jointype == JOIN_SEMI); + Assert(bms_equal(rel->relids, sjinfo->syn_righthand)); + + /* If result already cached, return it */ + if (rel->cheapest_unique_path) + return (UniquePath *) rel->cheapest_unique_path; + + /* If it's not possible to unique-ify, return NULL */ + if (!(sjinfo->semi_can_btree || sjinfo->semi_can_hash)) + return NULL; + + /* + * When called during GEQO join planning, we are in a short-lived memory + * context. We must make sure that the path and any subsidiary data + * structures created for a baserel survive the GEQO cycle, else the + * baserel is trashed for future GEQO cycles. On the other hand, when we + * are creating those for a joinrel during GEQO, we don't want them to + * clutter the main planning context. Upshot is that the best solution is + * to explicitly allocate memory in the same context the given RelOptInfo + * is in. + */ + oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(rel)); + + pathnode = makeNode(UniquePath); + + pathnode->path.pathtype = T_Unique; + pathnode->path.parent = rel; + pathnode->path.pathtarget = rel->reltarget; + pathnode->path.param_info = subpath->param_info; + pathnode->path.parallel_aware = false; + pathnode->path.parallel_safe = rel->consider_parallel && + subpath->parallel_safe; + pathnode->path.parallel_workers = subpath->parallel_workers; + + /* + * Assume the output is unsorted, since we don't necessarily have pathkeys + * to represent it. (This might get overridden below.) + */ + pathnode->path.pathkeys = NIL; + + pathnode->subpath = subpath; + + /* + * Under GEQO and when planning child joins, the sjinfo might be + * short-lived, so we'd better make copies of data structures we extract + * from it. + */ + pathnode->in_operators = copyObject(sjinfo->semi_operators); + pathnode->uniq_exprs = copyObject(sjinfo->semi_rhs_exprs); + + /* + * If the input is a relation and it has a unique index that proves the + * semi_rhs_exprs are unique, then we don't need to do anything. Note + * that relation_has_unique_index_for automatically considers restriction + * clauses for the rel, as well. + */ + if (rel->rtekind == RTE_RELATION && sjinfo->semi_can_btree && + relation_has_unique_index_for(root, rel, NIL, + sjinfo->semi_rhs_exprs, + sjinfo->semi_operators)) + { + pathnode->umethod = UNIQUE_PATH_NOOP; + pathnode->path.rows = rel->rows; + pathnode->path.disabled_nodes = subpath->disabled_nodes; + pathnode->path.startup_cost = subpath->startup_cost; + pathnode->path.total_cost = subpath->total_cost; + pathnode->path.pathkeys = subpath->pathkeys; + + rel->cheapest_unique_path = (Path *) pathnode; + + MemoryContextSwitchTo(oldcontext); + + return pathnode; + } + + /* + * If the input is a subquery whose output must be unique already, then we + * don't need to do anything. The test for uniqueness has to consider + * exactly which columns we are extracting; for example "SELECT DISTINCT + * x,y" doesn't guarantee that x alone is distinct. So we cannot check for + * this optimization unless semi_rhs_exprs consists only of simple Vars + * referencing subquery outputs. (Possibly we could do something with + * expressions in the subquery outputs, too, but for now keep it simple.) + */ + if (rel->rtekind == RTE_SUBQUERY) + { + RangeTblEntry *rte = planner_rt_fetch(rel->relid, root); + + if (query_supports_distinctness(rte->subquery)) + { + List *sub_tlist_colnos; + + sub_tlist_colnos = translate_sub_tlist(sjinfo->semi_rhs_exprs, + rel->relid); + + if (sub_tlist_colnos && + query_is_distinct_for(rte->subquery, + sub_tlist_colnos, + sjinfo->semi_operators)) + { + pathnode->umethod = UNIQUE_PATH_NOOP; + pathnode->path.rows = rel->rows; + pathnode->path.disabled_nodes = subpath->disabled_nodes; + pathnode->path.startup_cost = subpath->startup_cost; + pathnode->path.total_cost = subpath->total_cost; + pathnode->path.pathkeys = subpath->pathkeys; + + rel->cheapest_unique_path = (Path *) pathnode; + + MemoryContextSwitchTo(oldcontext); + + return pathnode; + } + } + } + + /* Estimate number of output rows */ + pathnode->path.rows = estimate_num_groups(root, + sjinfo->semi_rhs_exprs, + rel->rows, + NULL, + NULL, + NULL); + numCols = list_length(sjinfo->semi_rhs_exprs); + + if (sjinfo->semi_can_btree) + { + /* + * Estimate cost for sort+unique implementation + */ + cost_sort(&sort_path, root, NIL, + subpath->disabled_nodes, + subpath->total_cost, + rel->rows, + subpath->pathtarget->width, + 0.0, + work_mem, + -1.0); + + /* + * Charge one cpu_operator_cost per comparison per input tuple. We + * assume all columns get compared at most of the tuples. (XXX + * probably this is an overestimate.) This should agree with + * create_upper_unique_path. + */ + sort_path.total_cost += cpu_operator_cost * rel->rows * numCols; + } + + if (sjinfo->semi_can_hash) + { + /* + * Estimate the overhead per hashtable entry at 64 bytes (same as in + * planner.c). + */ + int hashentrysize = subpath->pathtarget->width + 64; + + if (hashentrysize * pathnode->path.rows > get_hash_memory_limit()) + { + /* + * We should not try to hash. Hack the SpecialJoinInfo to + * remember this, in case we come through here again. + */ + sjinfo->semi_can_hash = false; + } + else + cost_agg(&agg_path, root, + AGG_HASHED, NULL, + numCols, pathnode->path.rows, + NIL, + subpath->disabled_nodes, + subpath->startup_cost, + subpath->total_cost, + rel->rows, + subpath->pathtarget->width); + } + + if (sjinfo->semi_can_btree && sjinfo->semi_can_hash) + { + if (agg_path.disabled_nodes < sort_path.disabled_nodes || + (agg_path.disabled_nodes == sort_path.disabled_nodes && + agg_path.total_cost < sort_path.total_cost)) + pathnode->umethod = UNIQUE_PATH_HASH; + else + pathnode->umethod = UNIQUE_PATH_SORT; + } + else if (sjinfo->semi_can_btree) + pathnode->umethod = UNIQUE_PATH_SORT; + else if (sjinfo->semi_can_hash) + pathnode->umethod = UNIQUE_PATH_HASH; + else + { + /* we can get here only if we abandoned hashing above */ + MemoryContextSwitchTo(oldcontext); + return NULL; + } + + if (pathnode->umethod == UNIQUE_PATH_HASH) + { + pathnode->path.disabled_nodes = agg_path.disabled_nodes; + pathnode->path.startup_cost = agg_path.startup_cost; + pathnode->path.total_cost = agg_path.total_cost; + } + else + { + pathnode->path.disabled_nodes = sort_path.disabled_nodes; + pathnode->path.startup_cost = sort_path.startup_cost; + pathnode->path.total_cost = sort_path.total_cost; + } + + rel->cheapest_unique_path = (Path *) pathnode; + + MemoryContextSwitchTo(oldcontext); + + return pathnode; + } + + /* ++>>>>>>> theirs * create_gather_merge_path * * Creates a path corresponding to a gather merge scan, returning diff --cc src/test/regress/expected/incremental_sort.out index fdec5b9ba52,b23dc13ca6f..00000000000 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@@ -1723,42 -1723,52 +1723,94 @@@ order by t1.four, t1.two limit 1 (12 rows) -- ++<<<<<<< ours +-- Test incremental sort for Append/MergeAppend +-- +create table prt_tbl (a int, b int) partition by range (a); +create table prt_tbl_1 partition of prt_tbl for values from (0) to (100); +create table prt_tbl_2 partition of prt_tbl for values from (100) to (200); +insert into prt_tbl select i%200, i from generate_series(1,1000)i; +create index on prt_tbl_1(a); +create index on prt_tbl_2(a, b); +analyze prt_tbl; +set enable_seqscan to off; +set enable_bitmapscan to off; +-- Ensure we get an incremental sort for the subpath of Append +explain (costs off) select * from prt_tbl order by a, b; + QUERY PLAN +------------------------------------------------------------ + Append + -> Incremental Sort + Sort Key: prt_tbl_1.a, prt_tbl_1.b + Presorted Key: prt_tbl_1.a + -> Index Scan using prt_tbl_1_a_idx on prt_tbl_1 + -> Index Only Scan using prt_tbl_2_a_b_idx on prt_tbl_2 +(6 rows) + +-- Ensure we get an incremental sort for the subpath of MergeAppend +explain (costs off) select * from prt_tbl_1 union all select * from prt_tbl_2 order by a, b; + QUERY PLAN +------------------------------------------------------------ + Merge Append + Sort Key: prt_tbl_1.a, prt_tbl_1.b + -> Incremental Sort + Sort Key: prt_tbl_1.a, prt_tbl_1.b + Presorted Key: prt_tbl_1.a + -> Index Scan using prt_tbl_1_a_idx on prt_tbl_1 + -> Index Only Scan using prt_tbl_2_a_b_idx on prt_tbl_2 +(7 rows) + +reset enable_bitmapscan; +reset enable_seqscan; +drop table prt_tbl; ++======= + -- Commuting of sides in an expression doesn't influence cost estimation + -- + CREATE TABLE sort_ndist_t1 (x numeric, y numeric); + CREATE TABLE sort_ndist_t2 (x numeric, y numeric); + INSERT INTO sort_ndist_t1 (x,y) + SELECT gs%10, gs%1000 FROM generate_series(1,1E4) AS gs; + INSERT INTO sort_ndist_t2 (x,y) + SELECT gs, gs FROM generate_series(1,1E4) AS gs; + CREATE INDEX t1_idx ON sort_ndist_t1 (x); + CREATE INDEX t2_idx ON sort_ndist_t2 (x); + VACUUM ANALYZE sort_ndist_t1, sort_ndist_t2; + SET enable_hashjoin = 'off'; + -- Having lots of duplicates after the join it is more effective to use plain + -- Sort instead of incremental sort. + EXPLAIN (COSTS OFF) + SELECT t1.x, t1.y FROM sort_ndist_t1 t1, sort_ndist_t2 t2 + WHERE t1.x=t2.x + ORDER BY t1.x,t1.y; + QUERY PLAN + -------------------------------------------------------------------- + Sort + Sort Key: t1.x, t1.y + -> Nested Loop + -> Seq Scan on sort_ndist_t1 t1 + -> Memoize + Cache Key: t1.x + Cache Mode: logical + -> Index Only Scan using t2_idx on sort_ndist_t2 t2 + Index Cond: (x = t1.x) + (9 rows) + + EXPLAIN (COSTS OFF) -- the plan must be the same as above + SELECT t1.x, t1.y FROM sort_ndist_t1 t1, sort_ndist_t2 t2 + WHERE t2.x=t1.x + ORDER BY t1.x,t1.y; + QUERY PLAN + -------------------------------------------------------------------- + Sort + Sort Key: t1.x, t1.y + -> Nested Loop + -> Seq Scan on sort_ndist_t1 t1 + -> Memoize + Cache Key: t1.x + Cache Mode: logical + -> Index Only Scan using t2_idx on sort_ndist_t2 t2 + Index Cond: (x = t1.x) + (9 rows) + + RESET enable_hashjoin; ++>>>>>>> theirs diff --cc src/test/regress/sql/incremental_sort.sql index bbe658a7588,298a2782b95..00000000000 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@@ -300,25 -300,30 +300,55 @@@ select * fro order by t1.four, t1.two limit 1; -- ++<<<<<<< ours +-- Test incremental sort for Append/MergeAppend +-- +create table prt_tbl (a int, b int) partition by range (a); +create table prt_tbl_1 partition of prt_tbl for values from (0) to (100); +create table prt_tbl_2 partition of prt_tbl for values from (100) to (200); +insert into prt_tbl select i%200, i from generate_series(1,1000)i; +create index on prt_tbl_1(a); +create index on prt_tbl_2(a, b); +analyze prt_tbl; + +set enable_seqscan to off; +set enable_bitmapscan to off; + +-- Ensure we get an incremental sort for the subpath of Append +explain (costs off) select * from prt_tbl order by a, b; + +-- Ensure we get an incremental sort for the subpath of MergeAppend +explain (costs off) select * from prt_tbl_1 union all select * from prt_tbl_2 order by a, b; + +reset enable_bitmapscan; +reset enable_seqscan; +drop table prt_tbl; ++======= + -- Commuting of sides in an expression doesn't influence cost estimation + -- + CREATE TABLE sort_ndist_t1 (x numeric, y numeric); + CREATE TABLE sort_ndist_t2 (x numeric, y numeric); + + INSERT INTO sort_ndist_t1 (x,y) + SELECT gs%10, gs%1000 FROM generate_series(1,1E4) AS gs; + INSERT INTO sort_ndist_t2 (x,y) + SELECT gs, gs FROM generate_series(1,1E4) AS gs; + CREATE INDEX t1_idx ON sort_ndist_t1 (x); + CREATE INDEX t2_idx ON sort_ndist_t2 (x); + VACUUM ANALYZE sort_ndist_t1, sort_ndist_t2; + + SET enable_hashjoin = 'off'; + + -- Having lots of duplicates after the join it is more effective to use plain + -- Sort instead of incremental sort. + EXPLAIN (COSTS OFF) + SELECT t1.x, t1.y FROM sort_ndist_t1 t1, sort_ndist_t2 t2 + WHERE t1.x=t2.x + ORDER BY t1.x,t1.y; + EXPLAIN (COSTS OFF) -- the plan must be the same as above + SELECT t1.x, t1.y FROM sort_ndist_t1 t1, sort_ndist_t2 t2 + WHERE t2.x=t1.x + ORDER BY t1.x,t1.y; + + RESET enable_hashjoin; ++>>>>>>> theirs