Partial indexes

Explore partial indexes in YugabyteDB using YSQL

Partial indexes allow you to improve query performance by reducing the index size. A smaller index is faster to scan, easier to maintain, and requires less storage.

Partial indexing works by specifying the rows defined by a conditional expression (called the predicate of the partial index), typically in the WHERE clause of the table.

Syntax

CREATE INDEX index_name ON table_name(column_list) WHERE condition;

The WHERE clause specifies which rows need to be added to the index.

Example

Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

This example uses the customers table from the Northwind sample database.

View the contents of the customers table:

SELECT * FROM customers LIMIT 3;

 customer_id |       company_name        |  contact_name  |    contact_title    |           address           |   city    | region | postal_code | country |     phone      |      fax
-------------+---------------------------+----------------+---------------------+-----------------------------+-----------+--------+-------------+---------+----------------+----------------
 FAMIA       | Familia Arquibaldo        | Aria Cruz      | Marketing Assistant | Rua Orós, 92                | Sao Paulo | SP     | 05442-030   | Brazil  | (11) 555-9857  |
 VINET       | Vins et alcools Chevalier | Paul Henriot   | Accounting Manager  | 59 rue de l'Abbaye          | Reims     |        | 51100       | France  | 26.47.15.10    | 26.47.15.11
 GOURL       | Gourmet Lanchonetes       | André Fonseca  | Sales Associate     | Av. Brasil, 442             | Campinas  | SP     | 04876-786   | Brazil  | (11) 555-9482  |
(3 rows)

Suppose you want to query the subset of customers who are Sales Managers in the USA. The query plan using the EXPLAIN statement would look like the following:

northwind=# EXPLAIN SELECT * FROM customers where (country = 'USA' and contact_title = 'Sales Manager');

                                    QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on customers  (cost=0.00..105.00 rows=1000 width=738)
  Filter: (((country)::text = 'USA'::text) AND ((contact_title)::text = 'Sales Manager'::text))
(2 rows)

Without creating a partial index, querying the customers table with the WHERE clause scans all the rows sequentially. Creating a partial index limits the number of rows to be scanned for the same query.

Create a partial index on the columns country and city from the customers table as follows:

northwind=# CREATE INDEX index_country ON customers(country) WHERE(contact_title = 'Sales Manager');

Verify with the EXPLAIN statement that the number of rows is significantly less compared to the original query plan.

northwind=# EXPLAIN SELECT * FROM customers where (country = 'USA' and contact_title = 'Sales Manager');

                                  QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using index_country on customers  (cost=0.00..5.00 rows=10 width=738)
  Index Cond: ((country)::text = 'USA'::text)
(2 rows)

Learn more