test_standard.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, 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)