Hello.
Couple of questions:
- Q1: Today I decided to do a vacuum full verbose analyze on a large table that has been giving me slow performance. And then I did it again. I noticed that after each run the values in my indexes and estimate row version changed. What really got me wondering is the fact my indexes report more rows than are in the table and then the estimated rows is less than the actual amount.
The table is a read-only table that is updated 1/wk. After updating it is vacuumed full. I've also tried reindexing but the numbers still change.
Is this normal? Below is a partial output for 4 consecutive vacuum full analyzes. No data was added nor was there anyone in the table.
- Q2: I have about a dozen 5M plus row tables. I currently have my max_fsm_pages set to 300,000. As you can see in vacuum full output I supplied, one table is already over this amount. Is there a limit on the size of max_fsm_pages?
CONF settings:
# - Memory -
shared_buffers = 2000 # min 16, at least max_connections*2, 8KB each
sort_mem = 12288 # min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB
# - Free Space Map -
max_fsm_pages = 300000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 500 # min 100, ~50 bytes each
Vacuum full information
#after second vacuum full
INFO: index "emaildat_fkey" now contains 8053743 row versions in 25764 pages
DETAIL: 1895 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.38s/0.42u sec elapsed 11.11 sec.
INFO: analyzing "cdm.cdm_email_data"
INFO: "cdm_email_data": 65882 pages, 3000 rows sampled, 392410 estimated total rows
#after third vacuum full
INFO: index "emaildat_fkey" now contains 8052738 row versions in 25769 pages
DETAIL: 890 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.08s/0.32u sec elapsed 4.36 sec.
INFO: analyzing "cdm.cdm_email_data"
INFO: "cdm_email_data": 65874 pages, 3000 rows sampled, 392363 estimated total rows
#after REINDEX and vacuum full
INFO: index "emaildat_fkey" now contains 8052369 row versions in 25771 pages
DETAIL: 521 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.37s/0.35u sec elapsed 4.79 sec.
INFO: analyzing "cdm.cdm_email_data"
INFO: "cdm_email_data": 65869 pages, 3000 rows sampled, 392333 estimated total rows
#After vacuum full(s)
mdc_oz=# select count(*) from cdm.cdm_email_data;
count
---------
5433358
(1 row)
TIA
Patrick