PostgreSQL: How to Create Multiple Indexes for a Field?

In this article, we'll discuss how to create multiple indexes for the same column in a PostgreSQL database table to solve performance issues and overcome various constraints.

By digoal

Background

In PostgreSQL, you can create multiple indexes for a single column. This article explains various scenarios in which you can use this capability.

The create index syntax is as follows:

Command:     CREATE INDEX  
Description: define a new index  
Syntax:  
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]  
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )  
    [ INCLUDE ( column_name [, ...] ) ]  
    [ WITH ( storage_parameter = value [, ... ] ) ]  
    [ TABLESPACE tablespace_name ]  
    [ WHERE predicate ]

Reasons for Creating Multiple Indexes for the Same Column

1) Fix Performance Problems

1) Expansion: For example, updating indexes causes page expansion. You can perform reindexing to solve this performance problem.

Starting from PG12, reindex concurrently directly supports reindexing.

Command:     REINDEX  
Description: rebuild indexes  
Syntax:  
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name  
  
URL: https://www.postgresql.org/docs/12/sql-reindex.html

For versions earlier than PG12, create indexes with the same definition as create index concurrently and then delete the old indexes.

2) Reindexing is also required to fix the problem of invalid pages caused by indexes.

2) Different Scenarios Have Different Requirements for Queries or Constraints on the Same Field.

1) You can use the B-tree index for sorting, equal, greater than, and less than operations.

2) You need to create a GIN index if the column requires prefix and suffix fuzzy query and regex query.

GIN:

create extension pg_trgm;  
  
create index idx_t on t1 using gin (info gin_trgm_ops)  
  
select * from t1 where info like '%abcde%';  
  
select * from t1 where info ~ 'abcde';

3) Spatial distance sorting query and numerical distance sorting quer.

GiST:

postgres=# create table t12 (pos point, id int, info text);  
CREATE TABLE  
postgres=# create index idx_t12_1 on t12 using gist(pos);  
CREATE INDEX  
postgres=# explain select * from t12 order by pos <-> point(1,100) limit 1;  
                                   QUERY PLAN                                     
--------------------------------------------------------------------------------  
 Limit  (cost=0.14..0.18 rows=1 width=60)  
   ->  Index Scan using idx_t12_1 on t12  (cost=0.14..33.74 rows=1020 width=60)  
         Order By: (pos <-> '(1,100)'::point)  
(3 rows)

4) The like query with a prefix or suffix:

text_pattern_ops (collate <>'c' like query)

dtstest=> \l  
                                  List of databases  
   Name    |   Owner    | Encoding | Collate |   Ctype    |     Access privileges       
-----------+------------+----------+---------+------------+---------------------------  
 dtstest   | dtstest    | UTF8     | C       | en_US.utf8 |   
 postgres  | pg19792171 | UTF8     | C       | en_US.utf8 |   
 template0 | pg19792171 | UTF8     | C       | en_US.utf8 | =c/pg19792171            +  
           |            |          |         |            | pg19792171=CTc/pg19792171  
 template1 | pg19792171 | UTF8     | C       | en_US.utf8 | =c/pg19792171            +  
           |            |          |         |            | pg19792171=CTc/pg19792171  
(4 rows)  
  
dtstest=> create table test(id int, info text);  
CREATE TABLE  
dtstest=> create index idx_test_1 on test (info);  
CREATE INDEX  
dtstest=> explain select * from test where info like 'a%';  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Bitmap Heap Scan on test  (cost=1.31..6.54 rows=6 width=36)  
   Filter: (info ~~ 'a%'::text)  
   ->  Bitmap Index Scan on idx_test_1  (cost=0.00..1.31 rows=6 width=0)  
         Index Cond: ((info >= 'a'::text) AND (info < 'b'::text))  
(4 rows)  
  
dtstest=> explain select * from test where info ~ '^a';  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Bitmap Heap Scan on test  (cost=1.31..6.54 rows=6 width=36)  
   Filter: (info ~ '^a'::text)  
   ->  Bitmap Index Scan on idx_test_1  (cost=0.00..1.31 rows=6 width=0)  
         Index Cond: ((info >= 'a'::text) AND (info < 'b'::text))  
(4 rows)  
dtstest=> create database db1 with template template0 encoding 'utf8' lc_collate 'en_US.UTF8';  
CREATE DATABASE  
dtstest=> \c db1  
You are now connected to database "db1" as user "dtstest".  
db1=> create table test(id int, info text);  
CREATE TABLE  
db1=> create index idx_test_1 on test (info);  
CREATE INDEX  
db1=> explain select * from test where info like 'a%';  
                      QUERY PLAN                        
------------------------------------------------------  
 Seq Scan on test  (cost=0.00..25.88 rows=6 width=36)  
   Filter: (info ~~ 'a%'::text)  
(2 rows)  
  
db1=> explain select * from test where info ~ '^a';  
                      QUERY PLAN                        
------------------------------------------------------  
 Seq Scan on test  (cost=0.00..25.88 rows=6 width=36)  
   Filter: (info ~ '^a'::text)  
(2 rows)  
  
db1=> create index idx_test_2 on test (info text_pattern_ops);  
CREATE INDEX  
db1=> explain select * from test where info like 'a%';  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Bitmap Heap Scan on test  (cost=1.31..6.54 rows=6 width=36)  
   Filter: (info ~~ 'a%'::text)  
   ->  Bitmap Index Scan on idx_test_2  (cost=0.00..1.31 rows=6 width=0)  
         Index Cond: ((info ~>=~ 'a'::text) AND (info ~<~ 'b'::text))  
(4 rows)  
  
db1=> explain select * from test where info ~ '^a';  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Bitmap Heap Scan on test  (cost=1.31..6.54 rows=6 width=36)  
   Filter: (info ~ '^a'::text)  
   ->  Bitmap Index Scan on idx_test_2  (cost=0.00..1.31 rows=6 width=0)  
         Index Cond: ((info ~>=~ 'a'::text) AND (info ~<~ 'b'::text))  
(4 rows)

5) Null first and last, and the field in reverse order and positive order:

db1=> create index idx1 on test (info nulls first);  
CREATE INDEX  
  
db1=> create index idx2 on test (info desc);  
CREATE INDEX

6) Partial index:

db1=> create index idx3 on test (info) where abs(mod(id,4))=0;  
CREATE INDEX  
  
db1=> create index idx4 on test (info) where abs(mod(id,4))=1;  
CREATE INDEX  
  
db1=> create index idx5 on test (info) where abs(mod(id,4))=2;  
CREATE INDEX  
  
db1=> create index idx6 on test (info) where abs(mod(id,4))=3;  
CREATE INDEX

7) Global index (in the future):

create global index ... on partent…

8) Exclusive constraint index:

For example, space interleaving or range type interleaving is not allowed in GIS polygon records in the table.

db1=>  create table t2(id int, c1 box);  
CREATE TABLE  
  
db1=> alter table t2 add constraint uk exclude using gist (c1 with &&);  
ALTER TABLE  
  
db1=> insert into t2 values (1, box(point(0,0), point(1,1)));  
INSERT 0 1  
db1=> insert into t2 values (1, box(point(0,0), point(1,1)));  
ERROR:  conflicting key value violates exclusion constraint "uk"  
DETAIL:  Key (c1)=((1,1),(0,0)) conflicts with existing key (c1)=((1,1),(0,0)).  
db1=> insert into t2 values (1, box(point(0.5,0.5), point(0.6,0.6)));  
ERROR:  conflicting key value violates exclusion constraint "uk"  
DETAIL:  Key (c1)=((0.6,0.6),(0.5,0.5)) conflicts with existing key (c1)=((1,1),(0,0)).

9) Expression indexes:

create index idx on t (lower(id));

10) The distribution of data in some fields is characterized with a good linear correlation.

For range queries, you can use the BRIN index.

create index idx on t using brin(ts);

11) There are different include conditions for include index use.

create index idx1 on t (c1) include (c2,c3,c4);

create index idx2 on t (c1) include (c5,c6);

Community

PostgreSQL: How to Create Multiple Indexes for a Field?

Background

Reasons for Creating Multiple Indexes for the Same Column

1) Fix Performance Problems

2) Different Scenarios Have Different Requirements for Queries or Constraints on the Same Field.

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Overview

Database for FinTech Solution

Oracle Database Migration Solution