Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the relevant data dictionary tables in PostgreSQL statistics". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Pg_class

In the pg_class data dictionary table, two statistics of Relation (including Index) are stored: the total page occupancy relpages and the total tuple total reltuples, which are used to estimate the start-up cost and total cost of the access path when the optimizer performs physical optimization.

The test script is as follows, insert 10w data into the t _ grxx table and create 2 indexes:

Drop table if exists tactile grxxbot create table t_grxx (dwbh varchar (10), grbh varchar (10), xm varchar (20), xb varchar (10), nl int); insert into t_grxx (dwbh,grbh,xm,xb,nl) select generate_series (1XM' 100000) / 10 | |', generate_series (1jue 100000), 'XM' | generate_series (1Mague 100000), (case when (floor (random () * 2) = 0) then' male 'else' female 'end), floor (random () * 100 + 1): int Create index idx_t_grxx_grbh on t_grxx (grbh); create index idx_t_grxx_dwbh on t_grxx (dwbh)

Statistics in basic Relational t_grxx

Testdb=# select relpages,reltuples from pg_class where relname = 'tasking grxxxwords; relpages | reltuples-+-726 | 100000-> pages, tuples 10000 (1 row)

Statistics for index idx_t_grxx_grbh

Testdb=# select relpages,reltuples from pg_class where relname = 'idx_t_grxx_grbh'; relpages | reltuples-+-276 | 100000-> number of pages 276, tuples 10000 (1 row) 2, pg_statistic

Pg_statistic is the main data dictionary table for storing statistical information in PG. Generate statistics by commanding ANALYZE, which are provided to the optimizer during the planning phase and are the basis of cost estimation.

The table structure of pg_statistic is as follows:

Testdb=#\ d pg_statistic Table "pg_catalog.pg_statistic" Column | Type | Collation | Nullable | Default-+-starelid | oid | | not null | staattnum | smallint | | | not null | stainherit | boolean | | not null | stanullfrac | real | | not null | stawidth | integer | | not null | stadistinct | real | | not null | stakind1 | smallint | | not null | stakind2 | smallint | | not null | stakind3 | smallint | not null | stakind4 | smallint | not null | stakind5 | | Smallint | | not null | staop1 | oid | | not null | staop2 | oid | | not null | staop3 | oid | | not null | staop4 | oid | not null | staop5 | oid | | not null | stanumbers1 | real [] | stanumbers2 | real [] | | stanumbers3 | real [] | stanumbers4 | real [] | stanumbers5 | real [] | stavalues1 | anyarray | stavalues2 | anyarray | stavalues3 | anyarray | stavalues4 | anyarray | | | stavalues5 | anyarray | Indexes: "pg_statistic_relid_att_inh_index" UNIQUE | Btree (starelid, staattnum, stainherit)

Where:

Starelid: the relationship or index Oid to which the data column belongs

Staattnum: data column number

Stainherit: whether to inherit the table (partition table)

Percentage of stanullfrac:NULL value

Stawidth: average row size in bytes

Stadistinct: unique value information for non-null values. > 0 indicates the number of unique values; = 0 indicates unknown; dwbhstakind1 | 1-> STATISTIC_KIND_MCV, high frequency statistics staop1 | 98-> "=", query pg_operator,select * from pg_operator where oid=98 Stanumbers1 | {0.0003}-> ratio of high frequency values, that is, 3/10000stavalues1 | {24}-> indicates the value of '24' stakind2 | 2-> STATISTIC_KIND_HISTOGRAM, histogram staop2 | 664-> "2": 0.040900, "2 = > 1": 1.000000}-> STATS_EXT_DEPENDENCIES statistics "what are the relevant data dictionary tables in PostgreSQL statistics"? Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.