Partial Indexes
Partial Indexes allow you to improve the query performance by reducing the index size. The smaller index size will be faster to scan and easier to maintain, thereby requiring lesser storage.
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 in the syntax specifies which rows need to be added to the index.
Example
This example uses the customers table from the Northwind sample database. Follow the steps to create a local cluster or in Yugabyte Cloud, and install the sample Northwind database.
- View the contents of the
customerstable.
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)
- Let's say you want to query the subset of customers who are
Sales Managersin theUSA. The query plan using theEXPLAINstatement 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 the partial index, querying the customers table with the WHERE clause will sequentially scan all the rows. Creating a partial index will limit the number of rows to be scanned for the same query.
- Create a partial index on the columns
countryandcityfrom thecustomerstable as follows:
northwind=# CREATE INDEX index_country ON customers(country) WHERE(contact_title = 'Sales Manager');
- Verify with the
EXPLAINstatment that the number of rows will be 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)