Friday, March 15, 2019

How to describe and analyze a partitioned table in postgreSQL


In this example, I am describing a table called "albums" in a database called "music". The table is partitioned:

psql -h myserver.mydomain.com -U music musicdb
Password for user music:
psql (11.2)
Type "help" for help.

Describe the table:
musicdb=> \d album
Table "music.album"
    Column    |         Type          | Collation | Nullable |           Default
--------------+-----------------------+-----------+----------+------------------------------
 album_id     | integer               |           | not null | generated always as identity
 album_name   | character varying(40) |           |          |
 genre        | character varying(20) |           |          |
 label        | character varying(40) |           |          |
 release_year | date                  |           |          |
Partition key: RANGE (date_part('year'::text, release_year))
Number of partitions: 3 (Use \d+ to list them.)

Describe the table's partitions:
musicdb=> \d+ album
Table "music.album"
    Column    |         Type          | Collation | Nullable |           Default            | Storage  | Stats target | Description
--------------+-----------------------+-----------+----------+------------------------------+----------+--------------+-------------
 album_id     | integer               |           | not null | generated always as identity | plain    |              |
 album_name   | character varying(40) |           |          |                              | extended |              |
 genre        | character varying(20) |           |          |                              | extended |              |
 label        | character varying(40) |           |          |                              | extended |              |
 release_year | date                  |           |          |                              | plain    |              |
Partition key: RANGE (date_part('year'::text, release_year))
Partitions: albums_1974 FOR VALUES FROM ('1974') TO ('1975'),
            albums_1979 FOR VALUES FROM ('1979') TO ('1980'),
            albums_default DEFAULT

Analyze the table:
musicdb=> analyze verbose album;
INFO:  analyzing "music.album" inheritance tree
INFO:  "albums_1974": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
INFO:  "albums_1979": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  analyzing "music.albums_1974"
INFO:  "albums_1974": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
INFO:  analyzing "music.albums_1979"
INFO:  "albums_1979": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  analyzing "music.albums_default"
INFO:  "albums_default": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

To check the statistics:

select attname,inherited,null_frac,n_distinct,most_common_vals,most_common_freqs,histogram_bounds 
from pg_stats 
where tablename='album';
attname inherited null_frac n_distinct most_common_vals most_common_freqs histogram_bounds
album_id true 0.0 -1.0     {1,2,3}
album_name true 0.0 -0.6666667 {KISS} {0.666666687}  
genre true 0.0 -0.33333334 {"Hard Rock"} {1}  
label true 0.6666667 -0.3333333      
release_year true 0.0 -0.6666667 {1974-01-18} {0.666666687}  

No comments:

Post a Comment