Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9d1a535

Browse files
committed
Fix costing bug in MergeAppend
When building a MergeAppendPath which has child paths that are notsorted correctly for the MergeAppend's sort order, we apply the cost ofsorting those paths to the MergeAppendPath costs.Here we fix a bug where the number of tuples specified that needed to besorted was effectively pg_class.reltuples rather than the number ofexpected row in the subpath. This effectively penalizes MergeAppendplans any time any filter is present on the MergeAppend subpath as thesort cost added is to sort all tuples in the table rather than just theones expected the path to return.This did not affect UNION ALL type queries as the RelOptInfo tuples isset from the subquery's path rows. It does affect MergeAppends uses forinheritance and partitioned tables.This is a long-standing bug introduced when MergeAppend was first addedin11cad29. No backpatch as this could result in plan changes.Author: Alexander KuzmenkovReviewed-by: Ashutosh Bapat, Aleksander Alekseev, David RowleyDiscussion:https://postgr.es/m/CALzhyqyhoXQDR-Usd_0HeWk%3DuqNLzoVeT8KhRoo%3DpV_KzgO3QQ%40mail.gmail.com
1 parent9589b03 commit9d1a535

File tree

3 files changed

+37
-1
lines changed

3 files changed

+37
-1
lines changed

‎src/backend/optimizer/util/pathnode.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1470,7 +1470,7 @@ create_merge_append_path(PlannerInfo *root,
14701470
root,
14711471
pathkeys,
14721472
subpath->total_cost,
1473-
subpath->parent->tuples,
1473+
subpath->rows,
14741474
subpath->pathtarget->width,
14751475
0.0,
14761476
work_mem,

‎src/test/regress/expected/inherit.out

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1714,6 +1714,29 @@ order by t1.b limit 10;
17141714
(14 rows)
17151715

17161716
reset enable_nestloop;
1717+
drop table matest0 cascade;
1718+
NOTICE: drop cascades to table matest1
1719+
-- Test a MergeAppend plan where one child requires a sort
1720+
create table matest0(a int primary key);
1721+
create table matest1() inherits (matest0);
1722+
insert into matest0 select generate_series(1, 400);
1723+
insert into matest1 select generate_series(1, 200);
1724+
analyze matest0;
1725+
analyze matest1;
1726+
explain (costs off)
1727+
select * from matest0 where a < 100 order by a;
1728+
QUERY PLAN
1729+
---------------------------------------------------------------
1730+
Merge Append
1731+
Sort Key: matest0.a
1732+
-> Index Only Scan using matest0_pkey on matest0 matest0_1
1733+
Index Cond: (a < 100)
1734+
-> Sort
1735+
Sort Key: matest0_2.a
1736+
-> Seq Scan on matest1 matest0_2
1737+
Filter: (a < 100)
1738+
(8 rows)
1739+
17171740
drop table matest0 cascade;
17181741
NOTICE: drop cascades to table matest1
17191742
--

‎src/test/regress/sql/inherit.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -624,6 +624,19 @@ reset enable_nestloop;
624624

625625
droptable matest0 cascade;
626626

627+
-- Test a MergeAppend plan where one child requires a sort
628+
createtablematest0(aintprimary key);
629+
createtablematest1() inherits (matest0);
630+
insert into matest0select generate_series(1,400);
631+
insert into matest1select generate_series(1,200);
632+
analyze matest0;
633+
analyze matest1;
634+
635+
explain (costs off)
636+
select*from matest0where a<100order by a;
637+
638+
droptable matest0 cascade;
639+
627640
--
628641
-- Test merge-append for UNION ALL append relations
629642
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp