Partial indexes
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.
Setup
The examples run on any YugabyteDB universe.
Local
YugabyteDB Aeon
YugabyteDB Anywhere
Set up a local cluster
If a local universe is currently running, first destroy it.
Start a local one-node universe with an RF of 1 by first creating a single node, as follows:
./bin/yugabyted start \
--advertise_address=127.0.0.1 \
--base_dir=${HOME}/var/node1 \
--cloud_location=aws.us-east-2.us-east-2a
After starting the yugabyted processes on all the nodes, configure the data placement constraint of the universe, as follows:
./bin/yugabyted configure data_placement --base_dir=${HOME}/var/node1 --fault_tolerance=zone
This command can be executed on any node where you already started YugabyteDB.
To check the status of a running multi-node universe, run the following command:
./bin/yugabyted status --base_dir=${HOME}/var/node1
Setup
To set up a universe, refer to Set up a YugabyteDB Anywhere universe.Setup
To set up a cluster, refer to Set up a YugabyteDB Aeon cluster.The 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 using 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');
Using the EXPLAIN statement, verify 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)