In the world of Postgres, indexes are crucial for efficient navigation within the database storage (referred to as a 'heap'). Postgres does not support clustering for it, and the MVCC architecture leads to the accumulation of many versions of the same tuple. Therefore, it is essential to be able to create and maintain efficient indexes to support applications.
Here are some tips for optimizing and improving index usage.
Note: the queries shown below work on an unmodified .
Using Covering Indexes
Let’s consider a query to retrieve email addresses for inactive users. In the table customer, there is a column active, and the query is quite simple:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows) The query invokes a full sequential scan of the table. customer,Let’s create an index for the column active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows) It worked; the subsequent scan turned into an 'index scan'. This means that Postgres will scan the index 'idx_cust1', and then continue searching through the heap of the table to read the values of other columns (in this case, the column email), which the query requires.
In PostgreSQL 11, covering indexes were introduced. They allow including one or more additional columns in the index itself — their values are stored in the index data structure.
If we were to take advantage of this feature and add the email value inside the index, then Postgres would not need to search in the heap of the table for the value email. Let’s see if this works:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows) «Index Only Scan’ tells us that the query now only requires the index, which helps avoid all disk I/O operations for reading the table heap.
Currently, covering indexes are only available for B-trees. However, in this case, the maintenance effort will be higher.
Using partial indexes
Partial indexes only index a subset of the table rows. This allows for smaller index sizes and faster scanning.
Suppose we need to get a list of email addresses of our customers from California. The query would be:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)What will regular indexes give us:
pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows) Scanning address was replaced by index scanning idx_address1, and then the heap was scanned address.
Since this is a frequent query and needs to be optimized, we can use a partial index that indexes only those rows with addresses where the district is 'California':
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=12.38..28.96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Index Only Scan using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows) Now the query reads only idx_address2 and does not touch the table address.
Using multi-value indexes (Multi-Value Indexes)
Some columns that need indexing may not contain a scalar data type. Column types such as jsonb, arrays and tsvector can contain composite or multiple values. If you need to index such columns, you usually have to search through all individual values in these columns.
Let's try to find the titles of all movies that contain cuts from failed dubbing. In the table film there is a text column called special_features. If a movie has this "special feature", then the column contains an element in the form of a text array Behind The Scenes. To find all such movies, we need to select all rows with "Behind The Scenes" for any values in the array. special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; The containment operator (containment operator) @> checks if the right side is a subset of the left side.
Query plan:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Which requests a full scan of the heap with a cost of 67.
Let's see if a regular B-tree index helps us:
pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)The index was not even considered. The B-tree index does not realize the existence of individual elements within indexed values.
We need a GIN index.
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)A GIN index supports matching individual values with indexed composite values, resulting in a query plan cost reduction of more than half.
Eliminating duplicate indexes.
Indexes accumulate over time, and sometimes a new index may contain the same definition as one of the previous ones. To obtain human-readable SQL definitions of indexes, you can use the catalog view. pg_indexes. You will also be able to easily find duplicate definitions:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
Superset Indexes
It may happen that you accumulate a lot of indexes, one of which indexes a superset of columns that are indexed by other indexes. This can be both desirable and undesirable — the superset can lead to scanning only by indexes, which is good, but it may take up too much space, or the query for which this superset was intended for optimization may no longer be used.
If you need to automate the identification of such indexes, you can start with from the table pg_catalog.
Unused Indexes
As applications that use databases evolve, so do the queries they use. Previously added indexes may no longer be used by any queries. Each time the index is scanned, it is marked by the statistics collector, and you can view the value in the system catalog view pg_stat_user_indexes . idx_scan, which serves as a cumulative counter. Monitoring this value over a period (say, a month) will provide a good insight into which indexes are unused and may be removed.
Here is a query to retrieve the current scan counters for all indexes in the schema 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 rows)Recreating indexes with fewer locks
Indexes often need to be recreated, for instance, when they bloat in size, and recreation can speed up scanning. Indexes can also become corrupted. Changing index parameters may also require its recreation.
Enabling parallel index creation
In PostgreSQL 11, B-Tree index creation is concurrent. Multiple parallel workers can be used to speed up the creation process. However, ensure that these configuration parameters are set correctly:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Default values are too low. Ideally, these numbers should be increased along with the number of CPU cores. Read more in .
Background index creation
You can create an index in the background using the parameter CONCURRENTLY commands CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXThis index creation procedure differs from the regular one in that it does not require table locking, meaning it does not block write operations. On the other hand, it takes more time and consumes more resources.
Postgres offers a wealth of flexible options for index creation and solutions for any particular cases, as well as ways to manage your database in the event of explosive growth of your application. We hope these tips help you make queries fast and your database ready to scale.
Source: habr.com
