PostgreSQL: Optimizations and indexes
3 min read

PostgreSQL: Optimizations and indexes

The sheep abo represents my naiveness towards performance in DBMS.
Photo by Flavio Gasperini / Unsplash

Today, I'm going to talk about one of the most interesting topics in software and computer engineering, performance. PostgreSQL, as one of the most advanced database management systems out there, lacks the ability to suggest indexes according to your query planner.

Indexes

There's currently 6 different index types supported in PostgreSQL 14. I'll briefly talk about these types and create a baseline for you to understand the advantages and disadvantages of using them.

CREATE INDEX name ON table USING HASH (column);
An example SQL statement to create a hash indexz

B-tree Index

  • B-trees focuses on equality and range queries on data that can be sorted in some ordering. If you're using the following equality operators, PostgreSQL query planner will consider using a B-tree index. (<, <=, =, >=, >)
  • BETWEEN, IN, IS NULL or IS NOT NULL sql queries can also be implemented with a B-tree index search.

If you're using SELECT * FROM repositories WHERE created_at BETWEEN $1 AND $2 kind of queries, B-tree indexes are the correct choice for you.

Hash Index

  • Hash indexes store a 32-bit hash code derived from the value of the column. Therefore, it's only used with = operator.

GiST Index

  • Lossy Generalized Search Tree index.
A GiST index is lossy, meaning that the index might produce false matches, and it is necessary to check the actual table row to eliminate such false matches. (PostgreSQL does this automatically when needed.) GiST indexes are lossy because each document is represented in the index by a fixed-length signature. The signature is generated by hashing each word into a single bit in an n-bit string, with all these bits OR-ed together to produce an n-bit document signature. When two words hash to the same bit position there will be a false match. If all words in the query have matches (real or false) then the table row must be retrieved to see if the match is correct.

CREATE INDEX name ON table USING GIST (column);

SP-GiST Index

  • SP-GST is an abvreviation for space-partitioned GiST. SP-GiST supports partitioned search trees such as quad-trees, k-d trees and suffix trees. The common feature of these structures is that they repeatedly divide the earch space into partitions that need not be of equal size.
  • More appropriate explanation of GiST and SP-GiST can be found on here.

GIN Index

  • Generalized inverted indexes prefered mostly for text search. GIN indexes stores only the words as tsvector values.
  • A table check is required when using a query with weights.

CREATE INDEX name ON table USING GIN (column);

BRIN Index

  • Introduced on PostgreSQL 9.5
  • BRIN index is the block-range index that allows serious performance boosts which involve BETWEEN queries.
  • 20 times better compared to B-Tree index. Source.

CREATE INDEX testtab_date_brin_idx  ON testtab USING BRIN (date);

Index Recommendations

While looking for a good index recommendation extension for PostgreSQL, I saw an extension developed by Powa team on Github.

Installation

  1. Clone the repository on the preferred location using the following command: git clone git@github.com:powa-team/pg_qualstats.git.
  2. Move to the correct folder using cd pg_qualstats and before running installation make sure that you have postgres in your environment.
  3. After that, you need to run make install on the root directory. This will install the extension using the PostgreSQL header files and link it to your build.
  4. Navigate to your postgresql.conf file and manipulate the line containing preload libraries to shared_preload_libraries = 'pg_qualstats'
  5. Restart the PostgreSQL process

Usage

On the database where you want to get recommendations create the extension using the following SQL statement: CREATE EXTENSION pg_qualstats;. Now you're good to go! Execute different queries, preferably from your application and later query the following SQL statement:

SELECT v
  FROM json_array_elements(
    pg_qualstats_index_advisor(min_filter => 50)->'indexes') v
  ORDER BY v::text COLLATE "C";
                               v
---------------------------------------------------------------
 "CREATE INDEX ON public.adv USING btree (id1)"
 "CREATE INDEX ON public.adv USING btree (val, id1, id2, id3)"
 "CREATE INDEX ON public.pgqs USING btree (id)"
(3 rows)
Example recommendation from pg_qualstats PostgreSQL extension

This query will give you possible indexes which can improve certain queries you've executed.

For more detailed explanation on this awesome extension, please visit the extensions official Github repository.