By default, the first column of a PRIMARY KEY or INDEX is taken to be the HASH column unless it is denoted as ASC or DESC which would indicate a RANGE column. The default RANGE column order is ASC if unspecified after the first column.
CREATE TABLE IF NOT EXISTS public.table1
customer_id uuid NOT NULL,
item_id uuid NOT NULL,
kind character varying(100) NOT NULL,
details character varying(100) NOT NULL,
CONSTRAINT table1_pkey PRIMARY KEY (customer_id, kind, item_id)
then table is created with
first column(customer_id) of PRIMARY KEY indicate a HASH column
second & third column(i.e., kind, item_id) of PRIMARY KEY indicate a RANGE column
Is my understanding(above) correct?
Which sharding is applied on below two indexes? hash or range
CREATE UNIQUE INDEX IF NOT EXISTS unique_customer_id ON table1(customer_id);
CREATE UNIQUE INDEX IF NOT EXISTS unique_item ON table1(customer_id, kind) WHERE kind='BAD' OR kind='SLOW';
Yes, in the example above, the PRIMARY KEY will be sharded on customer_id, and it will be sorted in kind, item_id order ascending.
In the first index (unique_customer_id), that index will be sharded on the customer_id field. In the second index (unique_item), it will be sharded on customer_id, with the kind field being sorted in ascending order within each customer_id. Your sample query will be slow/bad because you have to scan all customer_id shards for the specified kind field.