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

Commit587b6aa

Browse files
committed
Correct relation size estimate with low fillfactor
Since commit29cf61a, table_block_relation_estimate_size() considersfillfactor when estimating number of rows in a relation before the firstANALYZE. The formula however did not consider tuples may be larger thanavailable space determined by fillfactor, ending with density 0. Thisultimately means the relation was estimated to contain a single row.The executor however places at least one tuple per page, even with verylow fillfactor values, so the density should be at least 1. Fixed byclamping the density estimate using clamp_row_est().Reported by Heikki Linnakangas. Fix by me, with regression test inspiredby example provided by Heikki.Backpatch to 17, where the issue was introduced.Reported-by: Heikki LinnakangasBackpatch-through: 17Discussion:https://postgr.es/m/2bf9d973-7789-4937-a7ca-0af9fb49c71e@iki.fi
1 parent788baa9 commit587b6aa

File tree

3 files changed

+36
-0
lines changed

3 files changed

+36
-0
lines changed

‎src/backend/access/table/tableam.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#include"access/syncscan.h"
2525
#include"access/tableam.h"
2626
#include"access/xact.h"
27+
#include"optimizer/optimizer.h"
2728
#include"optimizer/plancat.h"
2829
#include"port/pg_bitutils.h"
2930
#include"storage/bufmgr.h"
@@ -740,6 +741,8 @@ table_block_relation_estimate_size(Relation rel, int32 *attr_widths,
740741
tuple_width+=overhead_bytes_per_tuple;
741742
/* note: integer division is intentional here */
742743
density= (usable_bytes_per_page*fillfactor /100) /tuple_width;
744+
/* There's at least one row on the page, even with low fillfactor. */
745+
density=clamp_row_est(density);
743746
}
744747
*tuples=rint(density* (double)curpages);
745748

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1646,4 +1646,21 @@ SELECT COUNT(*) FROM brin_hot_3 WHERE a = 2;
16461646

16471647
DROP TABLE brin_hot_3;
16481648
SET enable_seqscan = on;
1649+
-- Test that estimation of relation size works with tuples wider than the
1650+
-- relation fillfactor. We create a table with wide inline attributes and
1651+
-- low fillfactor, insert rows and then see how many rows EXPLAIN shows
1652+
-- before running analyze. We disable autovacuum so that it does not
1653+
-- interfere with the test.
1654+
CREATE TABLE table_fillfactor (
1655+
n char(1000)
1656+
) with (fillfactor=10, autovacuum_enabled=off);
1657+
INSERT INTO table_fillfactor
1658+
SELECT 'x' FROM generate_series(1,1000);
1659+
SELECT * FROM check_estimated_rows('SELECT * FROM table_fillfactor');
1660+
estimated | actual
1661+
-----------+--------
1662+
1000 | 1000
1663+
(1 row)
1664+
1665+
DROP TABLE table_fillfactor;
16491666
-- End of Stats Test

‎src/test/regress/sql/stats.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -849,4 +849,20 @@ DROP TABLE brin_hot_3;
849849

850850
SET enable_seqscan=on;
851851

852+
-- Test that estimation of relation size works with tuples wider than the
853+
-- relation fillfactor. We create a table with wide inline attributes and
854+
-- low fillfactor, insert rows and then see how many rows EXPLAIN shows
855+
-- before running analyze. We disable autovacuum so that it does not
856+
-- interfere with the test.
857+
CREATETABLEtable_fillfactor (
858+
nchar(1000)
859+
) with (fillfactor=10, autovacuum_enabled=off);
860+
861+
INSERT INTO table_fillfactor
862+
SELECT'x'FROM generate_series(1,1000);
863+
864+
SELECT*FROM check_estimated_rows('SELECT * FROM table_fillfactor');
865+
866+
DROPTABLE table_fillfactor;
867+
852868
-- End of Stats Test

0 commit comments

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