Hello! Hello in this article i will show you how to check indexes on a table in Postgres. In previous articles we discussed how to check column type in PostgreSQL.
Indexes? What are they?
A table is basically an ordered version of yellow pages. Think about example yellow pages which is not ordered alphabetically, but born date.
For next generation yellow pages is a city wide fihrist.
Dream yourself searching a number in a born date ordered book, that would be a nightmare, right? That is same for a computer too, that open a file and search for the condition you give.
Indexes are memorizing records location by the column you want. Memorizing location of records by a selected column (preferably most searched columns) will help your CPU to search and find the records you need.
Indexes in Postgres
Different jobs need different tools. Postgres is well suited in this way, for each different search you can do or data types stored in the tables, PostgreSQL provides a different indexing algorithm. For mor information you can check PostgreSQL Documentation about indexes.
When you execute a query, Postgresql query optimizer first check indexes which can be used to execute query, satisfies the search condition. If engine finds an index for the search condition, engine run query using the index or indexes which speeds up the execution much.
Before starting i would like to give you an example. Let’s create a very simple table.
You can create a simple table as following. But to demonstrate difference of an index you need to have data in the table. Test data can be inserted as:
INSERT INTO index_test (var) VALUES(CAST(generate_series(1, 10000000) AS VARCHAR ));
Let’s create an index on this table the column we search and retry the same.
CREATE INDEX ON index_test USING btree(var);
As you can see both planning time and execution time is improved.
Alright, let’s get back to main topic of this article how to check indexes on a table in postgres.
How to Check Indexes on a Table in Postgres?
PostgreSQL has a system view that stores information about indexes, you can query this view from pg_indexes.
SELECT * FROM pg_catalog.pg_indexes ;
That returns you a list of all indexes.
It returns create statement as well as table, tablespace, index and schema name. You can also filter this result by table name column to check indexes on a table:
To Sum Up
In this article we talked about listing indexes on a table, in addition, covered index types and what is an index topic briefly.
Please leave comment below to meet! Drop me a question if you have : )
Hi! I’m an IT Specialist
I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.
Get in touch with me.
Leave a Reply