test_patched.txt

\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)