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.
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);
- 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. (
IS NOT NULLsql 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 indexes store a 32-bit hash code derived from the value of the column. Therefore, it's only used with
- 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-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.
- Generalized inverted indexes prefered mostly for text search. GIN indexes stores only the words as
- A table check is required when using a query with weights.
CREATE INDEX __`name`_ ON __`table`_ USING GIN (__`column`_);
- 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);
While looking for a good index recommendation extension for PostgreSQL, I saw an extension developed by Powa team on Github.
- Clone the repository on the preferred location using the following command:
git clone firstname.lastname@example.org:powa-team/pg_qualstats.git.
- Move to the correct folder using
cd pg_qualstatsand before running installation make sure that you have
postgresin your environment.
- After that, you need to run
make installon the root directory. This will install the extension using the PostgreSQL header files and link it to your build.
- Navigate to your
postgresql.conffile and manipulate the line containing preload libraries to
shared_preload_libraries = 'pg_qualstats'
- Restart the PostgreSQL process
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)
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.