How to Check If a Table Has an Index in PostgreSQL

A query that took 5 milliseconds last month now takes 5 seconds. Nine times out of ten, the first question is the same: does this table have the right index?

This guide shows you how to find out. You’ll start with a quick look at one table and work up to checking whether a specific column is indexed, and whether PostgreSQL actually uses that index.

What Is an Index, in Plain Words?

An index is like the index at the back of a book. Without it, you’d flip through every page to find “replication.” With it, you jump straight to page 214.

In PostgreSQL, a table without a useful index forces a sequential scan: the database reads every row. On 100 rows nobody notices. On 100 million rows, everyone does.

Set Up Some Example Tables

We’ll use three small tables throughout this guide:

-- Has a primary key, plus two extra indexes
CREATE TABLE orders (
    id           bigint PRIMARY KEY,
    customer_id  bigint,
    status       text,
    created_at   timestamptz
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_status_created_idx ON orders (status, created_at);

-- Has a UNIQUE constraint, which creates an index automatically
CREATE TABLE customers (
    id     bigint PRIMARY KEY,
    email  text UNIQUE
);

-- Has no index at all
CREATE TABLE audit_events (
    happened_at  timestamptz,
    detail       text
);

Notice that we never ran CREATE INDEX on customers. It still gets two indexes, as you’ll see below.

Method 1: Use \d in psql (Fastest)

In psql, describe the table:

\d orders

                         Table "public.orders"
   Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 id          | bigint                   |           | not null |
 customer_id | bigint                   |           |          |
 status      | text                     |           |          |
 created_at  | timestamp with time zone |           |          |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_customer_id_idx" btree (customer_id)
    "orders_status_created_idx" btree (status, created_at)

Everything under Indexes is an index on this table. Each line shows the index name, its type (btree is the default), and the columns in parentheses.

Now check customers:

\d customers

Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)

Both the primary key and the UNIQUE constraint created an index behind the scenes. You never have to add an index on a primary key yourself.

See also: Mastering the Linux Command Line — Your Complete Linux Training Course

And the table with no index:

\d audit_events

There’s no Indexes section at all. That means no indexes.

Bonus: List All Indexes with Their Sizes

\di+

This lists every index in the current schema, along with its table and size on disk. It’s handy for spotting a 20 GB index you didn’t know about.

Method 2: Query pg_indexes (Easiest SQL)

If you prefer SQL, or you’re using a GUI instead of psql, the pg_indexes view is the easiest place to start:

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename  = 'orders';

         indexname         |                                     indexdef
---------------------------+-----------------------------------------------------------------------------------
 orders_pkey               | CREATE UNIQUE INDEX orders_pkey ON public.orders USING btree (id)
 orders_customer_id_idx    | CREATE INDEX orders_customer_id_idx ON public.orders USING btree (customer_id)
 orders_status_created_idx | CREATE INDEX orders_status_created_idx ON public.orders USING btree (status, created_at)

The indexdef column shows the full CREATE INDEX statement. You can copy it to rebuild the index on another server.

For audit_events, the same query returns zero rows.

A Simple Yes or No

For a script that only needs true or false:

SELECT EXISTS (
    SELECT 1
    FROM pg_index
    WHERE indrelid = 'public.audit_events'::regclass
) AS has_index;

 has_index
-----------
 f

From the shell:

psql -d shop -tAc "SELECT EXISTS (SELECT 1 FROM pg_index WHERE indrelid = 'public.audit_events'::regclass);"

f

Method 3: Check If a Specific Column Is Indexed

Usually the real question isn’t “does the table have any index?” It’s “is customer_id indexed?“

This query lists every index on a table, with each column and its position inside the index:

SELECT i.relname AS index_name,
       a.attname AS column_name,
       k.pos     AS position_in_index
FROM pg_index x
JOIN pg_class i ON i.oid = x.indexrelid
JOIN LATERAL unnest(x.indkey::int2[]) WITH ORDINALITY AS k(attnum, pos) ON true
JOIN pg_attribute a ON a.attrelid = x.indrelid AND a.attnum = k.attnum
WHERE x.indrelid = 'public.orders'::regclass
ORDER BY i.relname, k.pos;

        index_name         | column_name | position_in_index
---------------------------+-------------+-------------------
 orders_customer_id_idx    | customer_id |                 1
 orders_pkey               | id          |                 1
 orders_status_created_idx | status      |                 1
 orders_status_created_idx | created_at  |                 2

Pay attention to position_in_index. This is the part most people miss.

created_at is in an index, but in position 2. An index on (status, created_at) works like a phone book sorted by last name, then first name. It’s great for finding “Smith, John,” but it won’t help you find everyone named “John.”

So:

• WHERE status = 'paid' → the index helps.
• WHERE status = 'paid' AND created_at > now() - interval '1 day' → the index helps.
• WHERE created_at > now() - interval '1 day' on its own → this index usually won’t help.

If you filter by created_at alone a lot, it probably needs its own index.

Indexes on expressions, like lower(email), don’t show up in this query because they aren’t tied to a plain column. Check pg_indexes.indexdef for those.

Method 4: Find Tables With No Indexes at All

This query lists every user table that has no index of any kind:

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       pg_size_pretty(pg_relation_size(c.oid)) AS table_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
  AND NOT EXISTS (SELECT 1 FROM pg_index x WHERE x.indrelid = c.oid)
ORDER BY pg_relation_size(c.oid) DESC;

 schema_name |  table_name  | table_size
-------------+--------------+------------
 public      | audit_events | 8192 bytes

The results are sorted by size, so the biggest tables come first. A large table with no index at the top of this list is worth a closer look.

Method 5: Check If PostgreSQL Actually Uses the Index

An index that exists isn’t always an index that gets used. Two ways to check:

Ask EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

 Index Scan using orders_customer_id_idx on orders  (cost=0.29..8.31 rows=1 width=48)
   Index Cond: (customer_id = 42)

Index Scan using orders_customer_id_idx means the index is being used.

If you see Seq Scan on orders instead, PostgreSQL decided to read the whole table. That isn’t always bad. On a tiny table, or when the query returns most of the rows, reading everything really is faster.

Check the Usage Counters

PostgreSQL counts how many times each index has been scanned:

SELECT indexrelname AS index_name,
       idx_scan     AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;

        index_name         | times_used | index_size
---------------------------+------------+------------
 orders_status_created_idx |          0 | 16 kB
 orders_customer_id_idx    |       1520 | 16 kB
 orders_pkey               |      48210 | 16 kB

An index stuck at 0 for weeks on a production database is probably unused. It still takes up disk space and slows down every INSERT and UPDATE.

Before you drop it, check two things:

• Replicas: these counters only cover the server you’re connected to. If read replicas send queries to that index, they keep their own counts.
• Reset time: the counters start from zero after a stats reset. Check stats_reset in pg_stat_database to see when that last happened.

A Common Surprise: Foreign Keys Aren’t Indexed Automatically

Primary keys and UNIQUE constraints create indexes for you. Foreign keys don’t.

CREATE TABLE payments (
    id        bigint PRIMARY KEY,
    order_id  bigint REFERENCES orders (id)
);

\d payments shows only payments_pkey. Nothing exists on order_id.

That causes two problems. Joins from orders to payments get slow. And every DELETE on orders has to scan all of payments to check for rows that still reference it. On big tables, that can make deletes take minutes. Add the index yourself:

CREATE INDEX CONCURRENTLY payments_order_id_idx ON payments (order_id);

CONCURRENTLY builds the index without blocking writes. That’s the safe choice on a live table.

Summary

• Quick look: \d tablename and read the Indexes section.
• SQL: pg_indexes gives you names and full definitions.
• Is a specific column indexed? Check its position. Only the first column of a multi-column index is useful when you filter by that column alone.
• Is the index used? Run EXPLAIN on your query, or look at idx_scan in pg_stat_user_indexes.
• Remember: primary keys and UNIQUE constraints get indexes automatically. Foreign keys don’t.