The complete PostgreSQL Index Advisor guide [Online]

What is an Index?

A database index is a data structure that speeds up data retrieval operations from a table while requiring additional writes and storage space to maintain. Using an index, you can quickly find data in a database table without having to search every row every time. Indexes can be built from one or more columns of a database table, enabling orderly access to random records as well as rapid random lookups.

Important: Don't just index everything! It's important to use indexes wisely, since they add overhead to the system as a whole.
When adding an index the SELECT query might become faster, but INSERT/UPDATE/DELETE operations may take more time, as more internal structures (the indexes) will be updated as a side effect.

How to create a PostgreSQL Index

Here is the CREATE INDEX syntax (applicable for PostgreSQL ver. 10 to 15)

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

PostgreSQL supports B-tree, hash, GiST, SP-GiST, GIN, and BRIN indexes. It is also possible to create your own indexes, but this is much more complex.

The command has different switches and parameters. Here are some of the important ones:

UNIQUE

This parameter makes sure that there are no duplicated values, during the creation of the index and each time data is added. If a duplicated value is found, an error will occur.

CONCURRENTLY

[Not recommended] For better performance while creating indexes. Using Concurrently will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done.

INCLUDE

[Optional] A list of non-key columns. Adding non-key columns to an index should be done cautiously, especially if they are wide columns. Data insertion will fail if an index tuple exceeds the maximum size for the index type.

name

name of the index, we highly recommend that you will use a meaningful full name, for example:

posts_idx_answercoun_creationda_id

which is in the format of

<tablename>_idx_<first_column>_<second_column>

If you don't provide a name, PostgreSQL will choose a generic name, based on a similar format.

table_name

table name to be indexed

method

PostgreSQL supports B-tree, hash, GiST, SP-GiST, GIN, and BRIN indexes.  Default is btree

column_name

The most significant parameter, where you specify the columns that should be indexed.

ASC / DESC

The sort order. Default is ASC, if you know that most of the time you plan to select data in a certain order, this is important to mention during creation.

There are many more advanced parameters that you can read about in the PostgreSQL documentation.

How does the Online PostgreSQL Index Advisor work?

It takes 60 seconds to get indexing recommendations using the Index Advisor. You can start here, upload your schema, submit your query, and you'll get the optimal indexing recommendations. You can also submit your slow query log files, or install the performance sensor, to get indexing recommendations for your entire database workload, and not just for a single query.

This is how the optimal indexing recommendations will look like in the Online PostgreSQL Index Advisor:

FAQ

Do I need to provide my database credentials to work with the Index Advisor?

No, the solution is 100% non-intrusive and doesn't require any access to your database or data. We'll never ask for your database's credentials.

Does the Index Advisor provide the commands to create the missing indexes?

Yes. You don't need to be a database expert to fix your indexes, EverSQL will guide you through it.

How to maintain indexes?

In order for the query planner to update the decisions planner, it may be necessary for you to run the ANALYZE command regularly.
Maintaining the index properly prevents it from becoming fragmented. The index can be re-created simultaneously or you can use the REINDEX command.

How to list all PostgreSQL Indexes?

SELECT tablename, indexname, indexdef 
 FROM pg_indexes 
 ORDER BY tablename, indexname;

How to list all on Indexes on a specific PostgreSQL table?

SELECT indexname, indexdef
 FROM pg_indexes 
 WHERE tablename = 'abcd';

How can I find if I have duplicate indexes?

The PostgreSQL Index Advisor will scan all your existing indexes and identify redundant indexes, that you can delete, reduce storage and improve PostgreSQL performance.