Having columns for month, quarter and year because you want to show statistics grouped by all in reports.Some examples we’ve seen with Citus open source and Citus customers in the cloud are: In actual production schemas, you invariably have certain columns which have dependencies or relationships with each other which the database doesn’t know about. Note that we’ve queried pg_stats (a view holding more readable version of the column statistics.)ĬREATE TABLE tbl ( col1 int, col2 int ) INSERT INTO tbl SELECT i / 10000, i / 100000 FROM generate_series ( 1, 10000000 ) s ( i ) ANALYZE tbl select * from pg_stats where tablename = 'tbl' and attname = 'col1' -+- schemaname | public tablename | tbl attname | col1 inherited | f null_frac | 0 avg_width | 4 n_distinct | 1000 most_common_vals | Real-world implications The query output below shows that the planner (correctly) estimates that there are 1000 distinct values for the column col1 in the table and also makes other estimates on most common values, frequencies etc. You can see an example below for the kind of statistics Postgres collected for col1 in our table below. Pg_statistic stores statistics about each column like what % of values are null for the column, what are the most common values, histogram bounds etc. Pg_class basically stores the total number of entries in each table and index, as well as the number of disk blocks occupied by them. These statistics are stored by the planner in pg_class and in pg_statistics. They are collected / updated mainly by running ANALYZE or VACUUM (and a few DDL commands such as CREATE INDEX). These statistics allow the planner to estimate how many rows will be returned after executing a certain part of the plan, which then influences the kind of scan or join algorithm which will be used. One very significant input to deciding which plan to use is the statistics the planner collects. But, how does Postgres come up with these plans? It shows how the table(s) referenced by the statement will be scanned (using a sequential scan, index scan etc), and what join algorithms will be used if multiple tables are used. EXPLAIN shows you the execution plan that the PostgreSQL planner generates for the supplied statement. If you’ve done some performance tuning with Postgres, you might have used EXPLAIN.
0 kommentar(er)
