# Does YugaByte SQL support partial indexes (indexing a subset of rows)? **URL:** \ **Category:** General\ **Created:** [June 12, 2019, 5:33am UTC](https://forum.yugabyte.com/t/does-yugabyte-sql-support-partial-indexes-indexing-a-subset-of-rows/391 "2019-06-12T05:33:27Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Edward\_Ribbery](https://avatars.discourse-cdn.com/v4/letter/e/a88e57/32.png) [@Edward\_Ribbery](https://forum.yugabyte.com/u/Edward_Ribbery)\ **Post date:** [June 12, 2019, 5:33am UTC](https://forum.yugabyte.com/t/does-yugabyte-sql-support-partial-indexes-indexing-a-subset-of-rows/391/1 "2019-06-12T05:33:27Z")
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? ---
**Author:** ![karthik](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/karthik/32/13_2.png) [@karthik](https://forum.yugabyte.com/u/karthik)\ **Post date:** [June 12, 2019, 5:35am UTC](https://forum.yugabyte.com/t/does-yugabyte-sql-support-partial-indexes-indexing-a-subset-of-rows/391/2 "2019-06-12T05:35:10Z")
Hi @Edward_Ribbery, Yes, I believe YSQL does support partial indexes. Will let @mihnea or @neha confirm. cc @bryn ---
**Author:** ![mihnea](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/mihnea/32/81_2.png) [@mihnea](https://forum.yugabyte.com/u/mihnea)\ **Post date:** [June 12, 2019, 7:18am UTC](https://forum.yugabyte.com/t/does-yugabyte-sql-support-partial-indexes-indexing-a-subset-of-rows/391/3 "2019-06-12T07:18:23Z")
@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) ```auto ./bin/yb-ctl start ./bin/ysqlsh ``` Create table and index. ```sql 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. ```sql 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). ```sql 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). ```sql 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. ```sql 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._ ```sql 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) ```