An Overview of the Index Changes in PostgreSQL 11

The right application of indexes can make queries blazing fast.

Indexes use pointers to access data pages in a speedy fashion.

Major changes happened on Indexes in PostgreSQL 11, lots of much awaited patches have been released.

Let’s have a look at some of the great features of this release.

Parallel B-TREE Index Builds

PostgreSQL 11 introduced an infrastructure patch to enable parallel index creation.

It can be only used with B-Tree index as for now.

Building a parallel B-Tree index is two to three times faster than doing the same thing without parallel working (or serial build).

In PostgreSQL 11 parallel index creation is on by default.

There are two important parameters:

• max_parallel_workers – Sets the maximum number of workers that the system can support for parallel queries.
• max_parallel_maintenance_workers – Controls the maximum number of worker processes which can be used to CREATE INDEX.

Let’s check it with an example:

severalnines=# CREATE TABLE test_btree AS SELECT generate_series(1,100000000) AS id;
SELECT 100000000
severalnines=#  SET maintenance_work_mem = '1GB';
severalnines=# timing
severalnines=#  CREATE INDEX q ON test_btree (id);
TIME: 25294.185 ms (00:25.294)

Let’s try it with 8-way parallel work:

severalnines=# SET maintenance_work_mem = '2GB';
severalnines=# SET max_parallel_workers = 16;
severalnines=# SET max_parallel_maintenance_workers = 8;
severalnines=# timing
severalnines=# CREATE INDEX q1 ON test_btree (id);
TIME: 11001.240 ms (00:11.001)

We can see the performance difference with the parallel worker, more than 60% performant with just a small change. The maintenance_work_mem can also be increased to get more performance.

The ALTER table also helps to increase parallel workers. Below syntax can be used to increase parallel workers along with max_parallel_maintenance_workers. This bypasses the cost model completely.

ALTER TABLE test_btree SET (parallel_workers = 24);

Tip: RESET to default once the index build is completed to prevent adverse query plan.

CREATE INDEX with the CONCURRENTLY option supports parallel builds without special restrictions, only the first table scan is actually performed in parallel.

Deeper performance tests can be found here.

Add Predicate Locking for Hash, Gist, and Gin Indexes

PostgreSQL 11 shipped with predicate lock support for hash indexes, gin indexes, and gist indexes. These will make SERIALIZABLE transaction isolation much more efficient with those indexes.

Benefit: predicate locking can provide better performance at serializable isolation level by reducing the number of false positives which leads to unnecessary serialization failure.

In PostgreSQL 10, the lock range is the relation, but in PostgreSQL 11 lock is found to be page only.

Let’s test it out.

severalnines=# CREATE TABLE sv_predicate_lock1(c1 INT, c2 VARCHAR(10)) ;
CREATE TABLE
severalnines=# CREATE INDEX idx1_sv_predicate_lock1 ON sv_predicate_lock1 USING 'hash(c1) ;
CREATE INDEX
severalnines=# INSERT INTO sv_predicate_lock1 VALUES (generate_series(1, 100000),  'puja') ;
INSERT 0 100000
severalnines=#  BEGIN ISOLATION LEVEL SERIALIZABLE ;
BEGIN
severalnines=# SELECT * FROM sv_predicate_lock1 WHERE c1=10000 FOR UPDATE ;
  c1   |  c2
-------+-------
 10000 | puja
(1 row)

As we can see below, the lock is on page level instead of relation. In PostgreSQL 10 it was on relation level, so it’s a BIG WIN for concurrent transactions in PostgreSQL 11.

severalnines=# SELECT locktype, relation::regclass, mode FROM pg_locks ;
   locktype    |        relation         |      mode
---------------+-------------------------+-----------------
 relation      | pg_locks                | AccessShareLock
 relation      | idx1_sv_predicate_lock1 | AccessShareLock
 relation      | sv_predicate_lock1      | RowShareLock
 virtualxid    |                         | ExclusiveLock
 transactionid |                         | ExclusiveLock
 page          | idx1_sv_predicate_lock1 | SIReadLock
 tuple         | sv_predicate_lock1      | SIReadLock
(7 rows)

Tip: A sequential scan will always need a relation-level predicate lock. This can result in an increased rate of serialization failures. It may be helpful to encourage the use of index scans by reducing random_page_cost and/or increasing cpu_tuple_cost.

Allow HOT Updates for Some Expression Indexes

The Heap Only Tuple (HOT) feature, eliminates redundant index entries and allows the re-use of space taken by DELETEd or obsoleted UPDATEd tuples without performing a table-wide vacuum. It reduces index size by avoiding the creation of identically-keyed index entries.

If the value of an index expression is unchanged after UPDATE, allow HOT updates where previously PostgreSQL disallowed them, giving a significant performance boost in those cases.

This is especially useful for indexes such as JSON->>field where the JSON value changes but the indexed value does not.

This feature was rolled back in 11.1 due to performance degradation (AT Free BSD only as per Simon), more details / benchmark can be found here. This should be fixed in future release.

Allow Entire Hash Index Pages to be Scanned

Hash index: The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the = operator.  It was also not crash safe (not logged in WAL) so it needs to rebuild after DB crashes, and changes to hash were not written via streaming replication.

In PostgreSQL 10, the hash index was WAL logged, that means, it’s CRASH safe and can be replicated. Hash indexes use much less space compare to B-Tree so they can fit better in memory.

In PostgreSQL 11, Btree indexes have an optimization called “single page vacuum”, which opportunistically removes dead index pointers from index pages, preventing a huge amount of index bloat, which would otherwise occur. The same logic has been ported to Hash indexes. It accelerates space recycling, reducing bloat.

STATISTICS of Function Index

It is now possible to specify a STATISTICS value for a function index column. It’s highly valuable to the efficiency of a specialized application. We can now collect stats on expression columns, that will help the planner to take a more accurate decision.

severalnines=# CREATE INDEX idx1_stats ON stat ((s1 + s2)) ;
CREATE INDEX
severalnines=# ALTER INDEX idx1_stats ALTER COLUMN 1 SET STATISTICS 1000 ;
ALTER INDEX
severalnines=# d+ idx1_stats
 Index "public.idx1_stats"
Column | Type | Definition | Storage | Stats target
--------+---------+------------+---------+--------------
expr | numeric | (c1 + c2) | main | 1000
btree, for table "public.stat1"

amcheck

A new Contrib module amcheck has been added. Only B-Tree indexes can be checked.

Let’s test it out !

severalnines=# CREATE EXTENSION amcheck ;
CREATE EXTENSION
severalnines=# SELECT bt_index_check('idx1_stats') ;
ERROR: invalid page in block 0 of relation base/16385/16580
severalnines=#CREATE INDEX idx1_hash_data1 ON data1 USING hash (c1) ;
CREATE INDEX
severalnines=# SELECT bt_index_check('idx1_hash_data1') ;
ERROR: only B-Tree indexes are supported as targets for verification
DETAIL: Relation "idx1_hash_data1" is not a B-Tree index.

Local Partitioned Index Possible

Prior to PostgreSQL11, It was not possible to create an index on a child table or a partitioned table.

In PostgreSQL 11, when CREATE INDEX is run on a partitioned table / parent table, it creates catalog entries for an index on the partitioned table and cascades to create actual indexes on the existing partitions. It will create them in future partitions also.

Let’s try to create a parent table and a partition it:

severalnines=# create table test_part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
severalnines=# create table part_1 partition of test_part for values in ('India');
CREATE TABLE
severalnines=# create table part_2 partition of test_part for values in ('USA');
CREATE TABLE
severalnines=#
severalnines=# d+ test_part
                                        Table "public.test_part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              |
 list   | character varying(5) |           |          |         | extended |              |
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('India'),
            part_2 FOR VALUES IN ('USA')

Let’s try to create an index on the parent table:

severalnines=# create index i_test on test_part (a);
CREATE INDEX
severalnines=# d part_2
                     Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default
--------+----------------------+-----------+----------+---------
 a      | integer              |           |          |
 list   | character varying(5) |           |          |
Partition of: test_part FOR VALUES IN ('USA')
Indexes:
    "part_2_a_idx" btree (a)

severalnines=# d part_1
                     Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default
--------+----------------------+-----------+----------+---------
 a      | integer              |           |          |
 list   | character varying(5) |           |          |
Partition of: test_part FOR VALUES IN ('India')
Indexes:
    "part_1_a_idx" btree (a)

The index is cascaded down to all the partitions in PostgreSQL 11, which is a really cool feature.

Covering Index (include CLAUSE for indexes)

An INCLUDE clause to add columns to the index can be specified. This is effective when adding columns which are unrelated to a unique constraint of a unique index. The INCLUDE columns exists solely to allow more queries to benefit from index-only scans. Only B-tree indexes support INCLUDE clause as for now.

Let’s check the behavior without INCLUDE. It won’t use index only scan if additional columns appear in the SELECT. This can be achieved by using INCLUDE clause.

severalnines=# CREATE TABLE no_include (a int, b int, c int);
CREATE TABLE
severalnines=# INSERT INTO no_include SELECT 3 * val, 3 * val + 1, 3 * val + 2 FROM generate_series(0, 1000000) as val;
INSERT 0 1000001
severalnines=# CREATE UNIQUE INDEX old_unique_idx ON no_include(a, b);
CREATE INDEX
severalnines=# VACUUM ANALYZE;
VACUUM
EXPLAIN ANALYZE SELECT a, b FROM no_include WHERE a < 1000;  - It will do index only scan 
EXPLAIN ANALYZE SELECT a, b, c FROM no_include WHERE a < 1000; - It will not do index only scan as we have extra column in select. 
severalnines=# CREATE INDEX old_idx ON no_include (a, b, c);
CREATE INDEX
severalnines=# VACUUM ANALYZE;
VACUUM
severalnines=# EXPLAIN ANALYZE SELECT a, b, c FROM no_include WHERE a < 1000;   - It did index only scan as index on all three columns. 
                     QUERY PLAN
-------------------------------------------------
 Index Only Scan using old_idx on no_include
     (cost=0.42..14.92 rows=371 width=12)
     (actual time=0.086..0.291 rows=334 loops=1)
   Index Cond: (a < 1000)
   Heap Fetches: 0
 Planning Time: 2.108 ms
 Execution Time: 0.396 ms
(5 rows)

Let's try with include clause. In the example below the UNIQUE CONSTRAINT is created in columns a and b, but the index includes a c column.

severalnines=# CREATE TABLE with_include (a int, b int, c int);
CREATE TABLE
severalnines=# INSERT INTO with_include SELECT 3 * val, 3 * val + 1, 3 * val + 2 FROM generate_series(0, 1000000) as val;
INSERT 0 1000001
severalnines=# CREATE UNIQUE INDEX new_unique_idx ON with_include(a, b) INCLUDE (c);
CREATE INDEX
severalnines=# VACUUM ANALYZE;
VACUUM
severalnines=#  EXPLAIN ANALYZE SELECT a, b, c FROM with_include WHERE a < 10000;
                       QUERY PLAN
-----------------------------------------------------
 Index Only Scan using new_unique_idx on with_include
     (cost=0.42..116.06 rows=3408 width=12)
     (actual time=0.085..2.348 rows=3334 loops=1)
   Index Cond: (a < 10000)
   Heap Fetches: 0
 Planning Time: 1.851 ms
 Execution Time: 2.840 ms
(5 rows)

There cannot be any overlap between columns in the main column list and those from the include list

severalnines=# CREATE UNIQUE INDEX new_unique_idx ON with_include(a, b) INCLUDE (a);
ERROR:  42P17: included columns must not intersect with key columns
LOCATION:  DefineIndex, indexcmds.c:373

A column used with an expression in the main list works:

severalnines=# CREATE UNIQUE INDEX new_unique_idx_2 ON with_include(round(a), b) INCLUDE (a);
CREATE INDEX

Expressions cannot be used in an include list because they cannot be used in an index-only scan:

severalnines=# CREATE UNIQUE INDEX new_unique_idx_2 ON with_include(a, b) INCLUDE (round(c));
ERROR:  0A000: expressions are not supported in included columns
LOCATION:  ComputeIndexAttrs, indexcmds.c:1446

Conclusion

The new features of PostgreSQL will surely improve the lives of DBAs so it's heading to become a strong alternative choice in open source DB. I understand that a few features of indexes are currently limited to B-Tree, it is still a great start of Parallel execution era for PostgreSQL and heading to a nice tool to look closely. Thanks!