\set QUIET on set default_statistics_target = :scale ; set work_mem = '512MB'; set maintenance_work_mem = '1GB'; begin; -- table is padded to get reasonable number of rows per page drop table if exists reltuples_test; create table reltuples_test as select id, id % 10 as ten, id % 100 as hun, id % 10000 as thou, id % 10000 as tenk, id %100000 as lahk, id % 1000000 as meg, 'some extra padding to adjust rowsize' as padding from generate_series(1::bigint, (:scale * 1000000)::bigint) g(id); -- avoid extranous vacuums and analyzes and add primary key alter table reltuples_test set (autovacuum_enabled = false), add constraint reltuples_test_pkey primary key (id); commit; -- vacuum verbose reltuples_test; analyze verbose reltuples_test; psql:reltuples_analyze_bug.sql:35: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:35: INFO: "reltuples_test": scanned 3000 of 153847 pages, containing 195000 live rows and 0 dead rows; 3000 rows in sample, 10000055 estimated total rows \echo "-- Starting Status --" "-- Starting Status --" select relpages, reltuples, (reltuples/relpages)::int as rows_per_page, relname from pg_class where relname ~ '^reltuples_test'; relpages | reltuples | rows_per_page | relname ----------+-------------+---------------+--------------------- 153847 | 1.00001e+07 | 65 | reltuples_test 27422 | 1.00001e+07 | 365 | reltuples_test_pkey (2 rows) \echo -- Update 1% of table at a time for :iterations. -- Note: bug is that reltuples increases with each iteration. select format('update reltuples_test set id = id + %s where id < %s', :scale * 1000000, n * :scale * 1000000 * :update_frac) as update_query, 'analyze verbose reltuples_test' as estimate from generate_series(1, :iterations) g(n) \gexec update reltuples_test set id = id + 10000000 where id < 1000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 169231 pages, containing 176475 live rows and 18525 dead rows; 3000 rows in sample, 9955014 estimated total rows update reltuples_test set id = id + 10000000 where id < 2000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 184615 pages, containing 162175 live rows and 32825 dead rows; 3000 rows in sample, 9979979 estimated total rows update reltuples_test set id = id + 10000000 where id < 3000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 200000 pages, containing 149565 live rows and 45435 dead rows; 3000 rows in sample, 9971000 estimated total rows update reltuples_test set id = id + 10000000 where id < 4000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 215384 pages, containing 138645 live rows and 56355 dead rows; 3000 rows in sample, 9953972 estimated total rows update reltuples_test set id = id + 10000000 where id < 5000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 230768 pages, containing 128375 live rows and 66625 dead rows; 3000 rows in sample, 9874947 estimated total rows update reltuples_test set id = id + 10000000 where id < 6000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 246153 pages, containing 121643 live rows and 73385 dead rows; 3000 rows in sample, 9980930 estimated total rows update reltuples_test set id = id + 10000000 where id < 7000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 261537 pages, containing 113945 live rows and 81055 dead rows; 3000 rows in sample, 9933611 estimated total rows update reltuples_test set id = id + 10000000 where id < 8000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 276922 pages, containing 110110 live rows and 84890 dead rows; 3000 rows in sample, 10163960 estimated total rows update reltuples_test set id = id + 10000000 where id < 9000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 292306 pages, containing 104910 live rows and 90090 dead rows; 3000 rows in sample, 10221941 estimated total rows update reltuples_test set id = id + 10000000 where id < 10000000.00 analyze verbose reltuples_test psql:reltuples_analyze_bug.sql:50: INFO: analyzing "public.reltuples_test" psql:reltuples_analyze_bug.sql:50: INFO: "reltuples_test": scanned 3000 of 307690 pages, containing 97890 live rows and 97110 dead rows; 3000 rows in sample, 10039925 estimated total rows -- final reltuples and rows per page \echo "-- Post Test Status --" "-- Post Test Status --" select relpages, reltuples, (reltuples/relpages)::int as rows_per_page, relname from pg_class where relname ~ '^reltuples_test'; relpages | reltuples | rows_per_page | relname ----------+-------------+---------------+--------------------- 307690 | 1.00399e+07 | 33 | reltuples_test 54839 | 1.00399e+07 | 183 | reltuples_test_pkey (2 rows)