Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

sys.table_partition ​

This view describes how a partitioned table is distributed by the partitions defined in the CREATE TABLE statement. Each row in this view represents a unique permutation of the partition column values in the partitioned table. The count column of the view is a count of the rows that are stored in a single partition. The sum of the count column matches the current number of rows in the partitioned table.

You can query this view to check for potential skew in the partitioning scheme for the table. If the count column reflects an uneven distribution, you can look for the source of the skew and find ways to fix it. For example, you may consider scaling back the number of partitions or choosing hash partitioning instead of range partitioning.

Note: This view does not account for rows that have not yet been flushed to the column store.

Column NameData TypeDescription
table_idbigintUnique table ID. Only partitioned tables are captured in this view.
n1, n2, n3, n4integerValues in these columns are repeated, ordered sets of integers that map to actual values in the partition columns for the table. If fewer than 4 partition columns exist in a table, the unused n columns are filled with zeroes. If OUTSIDE RANGE partitions were created for a table, you will see two additional rows in the view that account for these partitions. If IS NULL partitions were created, you will see an additional row for that partition. See Partitioning Options.

countbigintRow count for a given combination of partition values for a table. The sum(count) result against this view matches the total number of rows in the partitioned table.

Examples ​

For example, table 16984 is partitioned on one column:

premdb=# select * from sys.table_partition where table_id=16984;
 table_id | n1 | n2 | n3 | n4 | count  
----------+----+----+----+----+--------
   16984 |  0 |  0 |  0 |  0 | 291060
   16984 |  1 |  0 |  0 |  0 | 291060
   16984 |  2 |  0 |  0 |  0 | 291060
   16984 |  3 |  0 |  0 |  0 | 239400
   16984 |  4 |  0 |  0 |  0 | 239400
   16984 |  5 |  0 |  0 |  0 | 239400
   16984 |  6 |  0 |  0 |  0 | 239400
...

The following query returns the sum of the count column, which is equivalent to the count(*) result from the partitioned table.

premdb=# select sum(count) from sys.table_partition where table_id=16984;
   sum   
---------
 5421780
(1 row)

The following example joins to the sys.table view to get the table name for table 16895. The query computes several aggregates over the count column to analyze skew in the partitioning.

premdb=# select name, stp.table_id, max(count), min(count), avg(count), stddev(count) 
from sys.table_partition stp, sys.table sct where stp.table_id=sct.table_id and count <>0 
group by name, stp.table_id;
          name            | table_id | max  | min  |          avg          |      stddev       
---------------------------+----------+------+------+-----------------------+-------------------
 partitioned_newmatchstats |    16895 | 9576 | 8664 | 8798.9596412556053812 | 324.1986257363057
(1 row)

Parent topic:System Views