\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, 10000001 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 | 1e+07 | 65 | reltuples_test 27422 | 1e+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 176410 live rows and 18590 dead rows; 3000 rows in sample, 10981367 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 163150 live rows and 31850 dead rows; 3000 rows in sample, 11948112 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 151060 live rows and 43940 dead rows; 3000 rows in sample, 12900718 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 135655 live rows and 59345 dead rows; 3000 rows in sample, 13835185 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 128245 live rows and 66755 dead rows; 3000 rows in sample, 14758916 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 123565 live rows and 71435 dead rows; 3000 rows in sample, 15674572 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 113685 live rows and 81315 dead rows; 3000 rows in sample, 16576847 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 108550 live rows and 86450 dead rows; 3000 rows in sample, 17470388 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 105040 live rows and 89960 dead rows; 3000 rows in sample, 18356707 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 93990 live rows and 101010 dead rows; 3000 rows in sample, 19228409 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.92284e+07 | 62 | reltuples_test 54839 | 1.92284e+07 | 351 | reltuples_test_pkey (2 rows)