Hi Jeff, Corey, After fixing the statistics difference in dumps of tables with indexes, I now see difference in statistics of materialized view dump in the test I am developing at [1] (see the latest patches there).
I see following difference in the dump from the original regression
database and the dump taken from the database where the dump is
restored
@@ -441198,8 +441198,8 @@
SELECT * FROM pg_catalog.pg_restore_relation_stats(
'version', '180000'::integer,
'relation', 'public.mvtest_bb'::regclass,
- 'relpages', '1'::integer,
- 'reltuples', '1'::real,
+ 'relpages', '0'::integer,
+ 'reltuples', '-1'::real,
'relallvisible', '0'::integer
);
--
@@ -441218,8 +441218,8 @@
SELECT * FROM pg_catalog.pg_restore_relation_stats(
'version', '180000'::integer,
'relation', 'public.mvtest_tm'::regclass,
- 'relpages', '1'::integer,
- 'reltuples', '3'::real,
+ 'relpages', '0'::integer,
+ 'reltuples', '-1'::real,
'relallvisible', '0'::integer
);
--
@@ -441238,8 +441238,8 @@
SELECT * FROM pg_catalog.pg_restore_relation_stats(
'version', '180000'::integer,
'relation', 'public.mvtest_tvmm'::regclass,
- 'relpages', '1'::integer,
- 'reltuples', '1'::real,
+ 'relpages', '0'::integer,
+ 'reltuples', '-1'::real,
'relallvisible', '0'::integer
);
--
@@ -448468,9 +448468,9 @@
SELECT * FROM pg_catalog.pg_restore_relation_stats(
'version', '180000'::integer,
'relation', 'public.tableam_tblmv_heap2'::regclass,
- 'relpages', '1'::integer,
- 'reltuples', '1'::real,
- 'relallvisible', '1'::integer
+ 'relpages', '0'::integer,
+ 'reltuples', '-1'::real,
+ 'relallvisible', '0'::integer
);
These are materialised views created in the test matview.sql and create_am.sql.
When I tried to reproduce the issue outside the test using the
attached scripts. The SQL is just copied from matview.sql. But both
the dumps (from original and restored databases) do not show any
difference. But if I run "make installcheck", take dump of regression
database, restore it, take dump of restored database, I am able to see
the following difference
*** 458089,458097 ****
SELECT * FROM pg_catalog.pg_restore_relation_stats(
'version', '180000'::integer,
'relation', 'public.tableam_tblmv_heap2'::regclass,
! 'relpages', '1'::integer,
! 'reltuples', '1'::real,
! 'relallvisible', '1'::integer
);
--- 458089,458097 ----
SELECT * FROM pg_catalog.pg_restore_relation_stats(
'version', '180000'::integer,
'relation', 'public.tableam_tblmv_heap2'::regclass,
! 'relpages', '0'::integer,
! 'reltuples', '-1'::real,
! 'relallvisible', '0'::integer
);
This seems to be a real problem since the statistics is going back
i.e. useful statistics is being reset.
[1]
https://www.postgresql.org/message-id/CAExHW5sBbMki6Xs4XxFQQF3C4Wx3wxkLAcySrtuW3vrnOxXDNQ%40mail.gmail.com
--
Best Wishes,
Ashutosh Bapat
mtv_test.sql
Description: application/sql
mtv_test.sh
Description: application/shellscript
