CODEWITHSUNDEEP
postgresql
Jazi
Jazi

Reputation: 6732

PostgreSQL + ANALYZE + pg_statistic table

I have question about pg_statistic table which stores data from ANALYZE. Could someone tell me what does columns of this table contains? I have the list here, but i don't know (don't understand) what is in this columns: stanullfrac, stadistinct, stakindN, staopN, stanumbersN, stavaluesN.

Upvotes: 6

Views: 12120

Answers (1)

leonbloy
leonbloy

Reputation: 75906

Seems pretty straightforward to me. Specially if you look inside the definition the more user-friendly pg_stat view ( \d+ pg_stats )

For example, say :

select * from pg_statistic where starelid = 23825 and staattnum=2;

starelid    | 23825      => table (oid)
staattnum   | 2          => column 2
stanullfrac | 0          =>  0% null valuess
stadistinct | -0.484307  => about 48% are distict, i.e., the average repetition is about 2
stakind1    | 1                   => code '1' : most common vals 
stavalues1  | {"John","Mary" ...  => values of most common valss
stanumbers1 | {0.0086,0.0064 ...  => percentage occurrence of common vals
stakind2    | 2                   => code '2' : histogram bounds 
stavalues2  | {-,"AAA", ...       => values (bounds) for histogram intervals
stanumbers2 | 
stakind3    | 3                   => code '3' => correlation
stavalues3  | 
stanumbers3 | {0.00826469}        => correlation coefficient

Same thing using the human_readable pg_stats view:

select * from pg_stats where tablename = 'persons' and attname = 'first_name';
schemaname        | public
tablename         | persons
attname           | first_name
inherited         | f
null_frac         | 0
avg_width         | 11
n_distinct        | -0.484307
most_common_vals  | {"John","Mary"...
most_common_freqs | {0.00867898,0.00640832...
histogram_bounds  | {-,"AAA"...
correlation       | 0.00826469

Upvotes: 14

Related Questions