A quick look at postgresql analyze..
Analyze statement in postgresql helps compute statistics for a table/database.
The statistics thus collected is used by query planner to come up with a proper plan for the query.
The analyze command on a very large table always samples the record count and computes stats, hence the stats is always approximate than accurate.
The analyze command can be run without any attributes, in this case all the tables the user has access to will be stats updated.
analyze <table_name>;
usage of verbose in the command above, will print the progress of the analyze & samples it picked.
pgtst_db=> analyze verbose pgtst_schema.pgtst_tbl1;
INFO: analyzing "pgtst_schema.pgtst_tbl1"
INFO: "pgtst_tbl1": scanned 30000 of 105556 pages, containing 2841902 live rows and 0 dead rows; 30000 rows in sample, 9999327 estimated total rows
ANALYZE
pgtst_db=>
INFO: analyzing "pgtst_schema.pgtst_tbl1"
INFO: "pgtst_tbl1": scanned 30000 of 105556 pages, containing 2841902 live rows and 0 dead rows; 30000 rows in sample, 9999327 estimated total rows
ANALYZE
pgtst_db=>
We can "alter table ... alter column... set statistics..." to disable stats update on a column and also to enhance the stats update by setting a larger sample value.
See in the above example only 30K records were scanned of the 10M records.
Analyze will update stats on inheritance and partitions as well.
If analyze is running in background, to know its progress use the below view.
pg_stat_progress_analyze
Thanks
No comments:
Post a Comment