We learned in a previous tip that the YB_TABLE_PROPERTIES function can be used to display the number of tablets for a YSQL table.
Example:
yugabyte=# CREATE TABLE public.emp (id INT, email VARCHAR) SPLIT INTO 8 TABLETS;
CREATE TABLE
yugabyte=# SELECT num_tablets FROM yb_table_properties('public.emp'::regclass);
num_tablets
-------------
8
(1 row)
The function can also be used for secondary indexes, even those that are created to enforce a constraint.
yugabyte=# ALTER TABLE emp ADD CONSTRAINT emp_email_uk UNIQUE (email);
ALTER TABLE
yugabyte=# SELECT * FROM pg_indexes WHERE indexname = 'emp_email_uk';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+--------------+------------+-----------------------------------------------------------------------
public | emp | emp_email_uk | | CREATE UNIQUE INDEX emp_email_uk ON public.emp USING lsm (email HASH)
(1 row)
yugabyte=# SELECT num_tablets FROM yb_table_properties('public.emp_email_uk'::regclass);
num_tablets
-------------
1
(1 row)
But indexes that enforce a primary key constraint won’t work with the function.
yugabyte=# ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (id);
ALTER TABLE
yugabyte=# SELECT * FROM pg_indexes WHERE tablename = 'emp';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+--------------+------------+-----------------------------------------------------------------------
public | emp | emp_email_uk | | CREATE UNIQUE INDEX emp_email_uk ON public.emp USING lsm (email HASH)
public | emp | emp_pk | | CREATE UNIQUE INDEX emp_pk ON public.emp USING lsm (id HASH)
(2 rows)
yugabyte=# SELECT num_tablets FROM yb_table_properties('public.emp_pk'::regclass);
num_tablets
-------------
(1 row)
This makes sense as a primary key is just a logical representation of how the table itself is organized on disk (i.e. the hash/range partition columns and ordering columns). That is, there is not a separate physical storage table for a primary key index, whereas, secondary indexes like those that enforce a unique key constraint have a separate physical storage table…
If you’d prefer to display the number of tablets for a table when you pass the primary key’s OID to the YB_TABLE_PROERTIES function, one option is to create your own version.
CREATE OR REPLACE FUNCTION my_yb_table_properties(table_oid OID)
RETURNS TABLE (num_tablets BIGINT, num_hash_key_columns BIGINT, is_colocated BOOLEAN, tablegroup_oid OID, colocation_id OID)
AS $$
SELECT CASE WHEN c2.contype = 'p' THEN yb_table_properties(conrelid) ELSE yb_table_properties(c1.oid) END
FROM pg_catalog.pg_class AS c1
LEFT JOIN pg_constraint c2
ON c2.conname = c1.relname
AND c2.connamespace = c1.relnamespace
WHERE c1.oid = table_oid;
$$
LANGUAGE sql;
Example:
yugabyte=# SELECT relname, a.num_tablets, b.num_tablets my_num_tablets
yugabyte-# FROM pg_class, yb_table_properties(relfilenode) a, my_yb_table_properties(relfilenode) b
yugabyte-# WHERE relname ILIKE 'emp%';
relname | num_tablets | my_num_tablets
--------------+-------------+----------------
emp | 8 | 8
emp_email_uk | 1 | 1
emp_pk | | 8
(3 rows)
Have Fun!
