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} | |