You can get the list of indexes, their table and column using this query:

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
   -- and t.relname like 'mytable'
order by
    t.relname,
    i.relname;

From there, you can check existence by index name or involved column(s) and decide to create/skip the index.

Answer from JGH on Stack Overflow
🌐
Neon
neon.com › postgresql › indexes › list-indexes
PostgreSQL List Indexes
However, it does provide you with access to the pg_indexes view so that you can query the index information. If you use the psql program to interact with the PostgreSQL database, you can use the \d command to view the index information for a table.
Discussions

Is there a cleaner way to write an existence check?
Well, if you can handle no rows returned as meaning false, then you can remove the outer query. You’ll either get a single row with one value of 1, or no rows. More on reddit.com
🌐 r/PostgreSQL
27
4
June 26, 2024
Check if a key/value or value exists in an arbitrary jsonb.
Why are you using json in the first place? And the requirement "anywhere in the blob of data" doesn't seem real. You can try to look into json path expressions, but I'd honestly just reconsider usage of jsonb, and usage of proper datatypes and tables. More on reddit.com
🌐 r/PostgreSQL
10
1
March 13, 2023
Top answer
1 of 2
29

Combining the comment from @Rory and info in @Zaytsev Dmitry's answer:

The CREATE INDEX CONCURRENTLY will not return until the index has finished building. So you know the index is done when your query returns.

However if you're building a large index that runs for hours you may wonder if it is 'really' still running.

You can use the query for 'invalid' indexes:

SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

...if your index appears in the results then it either failed or is still in progress.

Another way to confirm the latter is to check the locks table:

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE mode = 'ShareUpdateExclusiveLock'
ORDER BY a.query_start;

Although CONCURRENTLY does not take exclusive lock on the table it will hold a weaker ShareUpdateExclusiveLock which you should see in the results from this query.

If there is no lock it implies that either the index has finished building, or the create failed and left an invalid index.

Between these two queries you can thus determine which of the three possible states (completed/in progress/failed) your index is in.

2 of 2
16

You can get list of invalid indexes.

SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

If you see your index in this query it means the index won't work and you have to recreate index.

Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it with CONCURRENTLY flag

🌐
PostgreSQL
postgresql.org › docs › current › sql-createindex.html
PostgreSQL: Documentation: 18: CREATE INDEX
May 14, 2026 - When CREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index.
🌐
CYBERTEC PostgreSQL
cybertec-postgresql.com › home › find and fix a missing postgresql index
Find and fix a missing PostgreSQL Index | CYBERTEC PostgreSQL | Services & Support
May 7, 2026 - The takeaway here is that a SINGLE missing PostgreSQL index in a relevant place can ruin the entire database and keep the entire system busy without yielding useful performance. What is really important to remember is the way we have approached the problem. pg_stat_user_tables is an excellent indicator to help you figure out where to look for problems. Then you can inspect pg_stat_statements and look for the worst queries. Sorting by total_exec_time DESC is the key. If you want to learn more about PostgreSQL and database performance in general, I can highly recommend Laurenz Albe’s post about “Count(*) made fast” or his post entitled Query Parameter Data Types and Performance.
🌐
PostgreSQL
postgresql.org › message-id › 4252C891.3060601@ll.mit.edu
PostgreSQL: Re: Check for existence of index
April 5, 2005 - Is there a way I can get a > ... me if an index > exists on a given table in a given schema? > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your > message can get through to the mailing list cleanly > > Check for existence ...
🌐
Percona
percona.com › home › blog › useful postgresql index maintenance queries to improve performance
Useful PostgreSQL Index Maintenance Queries to Improve Performance - Percona
May 5, 2026 - Learn essential PostgreSQL index maintenance queries to spot unused or duplicate indexes, reduce bloat, and keep performance on track.
🌐
Medium
tech-talk.the-experts.nl › your-postgres-indexes-might-be-useless-heres-how-to-check-e1314fd1b6ae
Your Postgres Indexes Might Be Useless — Here’s How to Check
March 4, 2026 - We can do this using the pg_indexes table that exists in every Postgres database. SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'positions'; ... Another partial composite index but for last week (as of current writing).
Find elsewhere
🌐
pgMustard
pgmustard.com › blog › why-isnt-postgres-using-my-index
Why isn't Postgres using my index? - pgMustard
December 19, 2025 - If you think you’re in the boat of Postgres not being able to use the index, it is first worth checking that the index you’re expecting Postgres to use actually exists (in the environment you’re testing in). If it doesn’t, please believe me that you are not the first, and won’t be ...
🌐
GeeksforGeeks
geeksforgeeks.org › postgresql › postgresql-list-indexes
PostgreSQL - List Indexes - GeeksforGeeks
July 15, 2025 - By using the pg_indexes view or the psql command, PostgreSQL users can efficiently list and manage indexes within their databases. Both methods provide critical insights into schema organization and index definitions, which are essential for ...
🌐
Medium
mohyusufz.medium.com › how-to-check-for-indexes-on-foreign-key-columns-in-postgresql-450159772f8e
How to Check for Indexes on Foreign Key Columns in PostgreSQL | by mohyusufz | Medium
October 10, 2024 - Checking for Indexes: In the main SELECT statement, we use a subquery with pg_index to check if there’s an index that matches the columns in the foreign key constraint. If an index exists, the query returns the index name.
🌐
DataCamp
datacamp.com › doc › postgresql › exists
PostgreSQL EXISTS
The `EXISTS` clause in PostgreSQL is a conditional expression used to determine whether a subquery returns any rows. It is often used in `SELECT`, `UPDATE`, and `DELETE` statements to test the presence of records in a subquery. The `EXISTS` clause is used when you need to check if a subquery ...
🌐
Tembo
tembo.io › docs › getting-started › postgres_guides › listing-indexes-in-postgres
Listing Indexes in Postgres
There is also a Slack thread in #customer-feedback from Thursday where support proposed a role-mapping checklist.
🌐
CommandPrompt Inc.
commandprompt.com › education › how-to-drop-an-index-in-postgresql
How to Drop an INDEX in PostgreSQL — CommandPrompt Inc.
June 1, 2023 - - The use of the CONCURRENTLY option blocks access to the table while dropping the index. - After that, type the name of the index with the IF EXISTS clause which checks the existence of the index in the table.
Address   2950 Newmarket ST STE 101 - 231, 98226, Bellingham
🌐
Neon
neon.com › postgresql › postgresql-indexes › postgresql-create-index
PostgreSQL CREATE INDEX Statement
First, specify the index name after the CREATE INDEX clause. Second, use the IF NOT EXISTS option to prevent an error if the index already exists.
🌐
Medium
medium.com › towardsdev › why-postgresql-uses-sequential-scan-even-if-index-exists-19b439bb7c7f
Why PostgreSQL Uses Sequential Scan Even If Index Exists | by Nakul Mitra | Towards Dev
March 8, 2026 - No suitable index exists. PostgreSQL estimates that a full table scan is cheaper than using the index. PostgreSQL performs an Index Scan when the cost of using the index is lower than doing a full scan. This typically happens when: The query has a WHERE condition on a column that is indexed. The condition is highly selective (matches few rows). PostgreSQL estimates fewer disk reads using the index. ... If id is a primary key or has an index, PostgreSQL usually uses an Index Scan here.
🌐
DEV Community
dev.to › digitalpollution › overview-of-postgresql-indexing-lpi
Overview of PostgreSQL indexing - DEV Community
September 24, 2024 - Every time you insert, update, or delete data, PostgreSQL needs to maintain the indexes, which means they can slow down these write operations if there are too many indexes to update. To identify indexes that may be unused, look for those with a low idx_scan value in the pg_stat_user_indexes view. If an index has been scanned very few times (or not at all) and your queries don’t seem to benefit from it, you might consider removing it. Also, check for redundant indexes—indexes that cover the same columns in a similar way.
🌐
Kendralittle
kendralittle.com › 2016 › 01 › 28 › how-to-check-if-an-index-exists-on-a-table-in-sql-server
How to Check if an Index Exists on a Table in SQL Server
January 28, 2016 - At least index cleanup gets syntactically easier in SQL Server 2016: DROP INDEX gets a new conditional clause to check for existence. ... Whee! At least that’s nice and easy. ... Index bloat in Postgres can cause problems, but it’s easy to miss.
🌐
Reddit
reddit.com › r/postgresql › is there a cleaner way to write an existence check?
r/PostgreSQL on Reddit: Is there a cleaner way to write an existence check?
June 26, 2024 -

Currently, I have query like this:

SELECT (EXISTS(SELECT 1 FROM "user" WHERE password = <password> AND username = <username> LIMIT 1))::bool; 

It works, but it is two sub queries, and I was wondering if there is a better way to write a query to check the existence of a value?

Top answer
1 of 7
5
Well, if you can handle no rows returned as meaning false, then you can remove the outer query. You’ll either get a single row with one value of 1, or no rows.
2 of 7
5
First off, run this through EXPLAIN ANALYZE and understand what is actually happening before making judgements about subqueries. SQL is not like C or Python or Javascript. The compiler plans the query based on more than just your query -- it looks at indexes, statistics, etc. Second, if you're already checking the user table, don't return true or false! Instead, return NULL or the user record in its entirety because I'll bet a dollar you're gonna need that data. In other words, do this: ``` select user.* from user where ... ```` Otherwise, you are likely gonna immediately go back to the database and then look up the user that just gave you this (username, password) in a redundant query. Third, the part of your query that says ```where password = ``` means you are likely storing your password in plain text. That's not a good habit to get into. Instead, your query oughtta look more like: ``` where crypt() = user_table.encrypted_password ``` Or something along those lines. Read your database manual. It oughtta explain how to store passwords in an encrypted way. I don't care if this is just a learning project. It is good practice and not that hard to learn to do this right and the consequences of doing it wrong are disastrous. Fourth, good job experimenting and learning by doing, and please, keep at it!
🌐
PostgreSQL
postgresql.org › docs › current › sql-dropindex.html
PostgreSQL: Documentation: 18: DROP INDEX
May 14, 2026 - July 16, 2026: PostgreSQL 19 Beta 2 Released! ... Unsupported versions: 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1 ... DROP INDEX drops an existing index from the database system.