Does YugaByte SQL support partial indexes (indexing a subset of rows)?

General
1

Suppose one knows that a majority of the rows in a table have a certain c5 as NULL. Further, suppose one doesn’t expect to do a query like “SELECT * FROM T WHERE c5=NULL” (and OK with a full scan if such a query is accidentally issued).

Does YugaByte SQL support a partial index (like postgres) such that only the non-NULL c5 values are indexed?

2

Hi @Edward_Ribbery,

Yes, I believe YSQL does support partial indexes. Will let @mihnea or @neha confirm. cc @bryn

3

@Edward_Ribbery Indeed, partial indexes are supported.
Here is an example using a (unique) index on a jsonb attribute (which may be missing/null).
Note: for compatibility reasons our default database is also called postgres.

Start cluster and connect (if needed)

./bin/yb-ctl start
./bin/ysqlsh

Create table and index.

postgres=# CREATE TABLE test(i int PRIMARY KEY, j jsonb);
CREATE TABLE
postgres=# CREATE UNIQUE index json_attr_idx ON test((j->>'k1')) WHERE j->>'k1' IS NOT NULL;
CREATE INDEX

Now we insert some values.

postgres=# INSERT INTO test(i, j) VALUES (1, '{"k0": "a1", "k1": "b1"}');
INSERT 0 1
postgres=# INSERT INTO test(i, j) VALUES (2, '{"k0": "a2", "k1": "b2"}');
INSERT 0 1

Insert a duplicate value (b1 already exists so uniqueness check should fail).

postgres=# INSERT INTO test(i, j) VALUES (3, '{"k0": "a1", "k1": "b1"}');
ERROR:  duplicate key value violates unique constraint "json_attr_idx"

Insert a couple of NULLs in the index (i.e. no value for that attribute).

postgres=# INSERT INTO test(i, j) VALUES (4, '{"k0": "a4"}');
INSERT 0 1
postgres=# INSERT INTO test(i, j) VALUES (5, '{"k0": "a5"}');
INSERT 0 1

A select with a matching condition will use the index.

postgres=# EXPLAIN SELECT * FROM test WHERE j->>'k1' = 'b1';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using json_attr_idx on test  (cost=0.00..4.01 rows=1000 width=36)
   Index Cond: ((j ->> 'k1'::text) = 'b1'::text)
(2 rows)
postgres=# SELECT * FROM test WHERE j->>'k1' = 'b1';
 i |            j
---+--------------------------
 1 | {"k0": "a1", "k1": "b1"}
(1 row)

A select checking for null NULL will do a table scan.
Note: We call this a “Foreign Scan” for historical reasons – we have a TODO to change it.

postgres=# EXPLAIN SELECT * FROM test WHERE j->>'k1' IS NULL;
                          QUERY PLAN
--------------------------------------------------------------
 Foreign Scan on test  (cost=0.00..102.50 rows=1000 width=36)
   Filter: ((j ->> 'k1'::text) IS NULL)
(2 rows)

postgres=# SELECT * FROM test WHERE j->>'k1' IS NULL;
 i |      j
---+--------------
 5 | {"k0": "a5"}
 4 | {"k0": "a4"}
(2 rows)
1 Like