Postgres CREATE INDEX Statement Generator

Choose a table, the columns or expressions to index, the index method (B-tree, hash, GIN, GiST, BRIN, SP-GiST) and options such as UNIQUE, CONCURRENTLY, IF NOT EXISTS, partial WHERE predicates, INCLUDE (covering) columns, operator classes like jsonb_path_ops or gin_trgm_ops, sort direction and NULLS ordering. The tool writes a correct CREATE INDEX statement plus a matching DROP INDEX CONCURRENTLY / REINDEX script and queries to verify validity, size and usage. Everything runs in your browser — nothing is uploaded.

Table (schema.table)Index name (optional)MethodOperator class

Columns / expressions (in order)

UNIQUECONCURRENTLYIF NOT EXISTSONLY (skip partitions)

Partial index WHEREINCLUDE columnsfillfactorTablespace

CREATE INDEX statement

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_created_at_idx ON public.orders (customer_id, created_at DESC);

-- CONCURRENTLY cannot run inside a transaction block (no BEGIN/COMMIT around it, and not in a migration tool's transaction).
-- If the build fails you get an INVALID index: check with the query below and DROP it before retrying.
-- Column order matters: put equality-filtered columns first, then range/ORDER BY columns.

Verify: validity, size, usage, plan

-- Definition, validity and size
SELECT i.indexrelid::regclass AS index_name,
       i.indisvalid            AS is_valid,
       i.indisunique           AS is_unique,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
       pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index i
WHERE i.indrelid = 'public.orders'::regclass
ORDER BY 1;

-- Is it actually used? (resets with pg_stat_reset)
SELECT idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'orders_customer_id_created_at_idx';

-- Confirm the planner picks it
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM public.orders
WHERE customer_id = /* value */ NULL;

DROP / REINDEX

-- Remove the index without blocking writes (also cannot run in a transaction block)
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_created_at_idx;

-- Rebuild it in place if it becomes bloated (PG12+)
REINDEX INDEX CONCURRENTLY orders_customer_id_created_at_idx;

Do more than postgres create index statement generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the table (schema.table) and add one or more columns; type an expression such as lower(email) or (data->>'sku') for an expression index.
Pick the index method and optional operator class, then toggle UNIQUE, CONCURRENTLY, IF NOT EXISTS, a partial WHERE clause, INCLUDE columns, fillfactor or tablespace.
Copy the CREATE INDEX statement and run it in psql or Chat2DB; use the verification queries to confirm the index is valid and used, and the DROP script to remove it safely.

Frequently asked questions

When should I use GIN, GiST or BRIN instead of a B-tree index in PostgreSQL?

B-tree (the default) covers equality and range comparisons, ORDER BY and uniqueness, and is right for most columns. Use GIN for multi-valued data: jsonb containment (@>, @?), arrays, tsvector full-text search and pg_trgm LIKE '%text%' searches. GiST suits geometric, range and nearest-neighbour queries and exclusion constraints. BRIN stores only min/max per block range, so it is tiny and ideal for very large append-only tables where the column correlates with physical order (timestamps, sequences). Hash indexes support only equality and are rarely better than B-tree.

Why does CREATE INDEX CONCURRENTLY fail inside a transaction or leave an INVALID index?

CREATE INDEX CONCURRENTLY cannot run inside a transaction block because it performs two table scans and waits for existing transactions between them; migration tools that wrap changes in BEGIN/COMMIT must disable the transaction for that step. If the build is interrupted or hits a uniqueness violation, PostgreSQL leaves the index marked INVALID (indisvalid = false in pg_index): it consumes space and slows writes but is never used. Drop it with DROP INDEX CONCURRENTLY and run the statement again.

How do I check whether a Postgres index is used and how big it is?

Query pg_stat_user_indexes (idx_scan counts index scans since the last stats reset), pg_relation_size(indexrelid) for size, and pg_get_indexdef for the definition; run EXPLAIN (ANALYZE, BUFFERS) on your query to see whether the planner chooses the index. This generator emits those queries for you. If you prefer a visual workflow, Chat2DB — a free AI-powered database client — shows indexes, sizes and explain plans in its table designer; download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.