How to CREATE INDEX in ParadeDB

How do I use CREATE INDEX in ParadeDB to speed up queries?

CREATE INDEX adds a secondary data structure that speeds up ParadeDB query filtering and sorting without modifying table data.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does CREATE INDEX do in ParadeDB?

An index stores a sorted copy of selected columns, letting the planner avoid full-table scans. ParadeDB inherits PostgreSQL’s B-tree, Hash, GiST, GIN, BRIN, and Vector methods, so you can accelerate lookups, full-text search, and embeddings.

When should I create an index?

Create indexes for columns frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.Avoid indexing low-cardinality flags or columns updated every row, because maintenance cost can outweigh benefit.

How do I pick the right index method?

Use B-tree for equality and range filters on “Customers.id” or “Orders.order_date”. Choose GIN for full-text on “Products.name”, BRIN for huge append-only “OrderItems.id”, and Vector for ANN search on embeddings.

Why add INCLUDE columns?

INCLUDE stores extra columns in leaf pages so queries can be completely satisfied by the index, avoiding heap access.Example: include “total_amount” when filtering by “order_date” but also selecting the amount.

How to build indexes without locking writes?

Add the CONCURRENTLY keyword. ParadeDB builds the index in the background, letting inserts, updates, and deletes continue.It takes longer but avoids table-wide write locks.

Example: speeding up recent orders by customer

The query SELECT * FROM Orders WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 20; benefits from a composite B-tree index on (customer_id, order_date DESC).

Best practices for CREATE INDEX

1) Name indexes clearly: idx_orders_customer_id_date. 2) Match sort order in the index to ORDER BY direction. 3) Drop unused indexes with pg_stat_user_indexes to save space.4) Always test with EXPLAIN.

What are the performance caveats?

Each INSERT, UPDATE, or DELETE must also update every index on the table. Over-indexing slows writes and bloats storage. Monitor pg_indexes_size() regularly.

How do I verify an index is used?

Run EXPLAIN (ANALYZE, BUFFERS) before and after creating the index. Look for Index Scan or Index Only Scan nodes referencing your new index.

Why How to CREATE INDEX in ParadeDB is important

How to CREATE INDEX in ParadeDB Example Usage


-- Speed up vector similarity search on product embeddings
CREATE INDEX idx_products_embedding
    ON Products USING vector (embedding_vector)
    WITH (lists = 100, probes = 10);

-- Improve customer email lookup
CREATE UNIQUE INDEX idx_customers_email
    ON Customers (email);

-- Optimize latest orders by customer
CREATE INDEX CONCURRENTLY idx_orders_customer_date
    ON Orders (customer_id, order_date DESC);

How to CREATE INDEX in ParadeDB Syntax


CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ IF NOT EXISTS ] index_name
    ON table_name [ USING { btree | hash | gist | gin | brin | vector } ]
    ( column_expression [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ...] ) ]
    [ TABLESPACE tablespace_name ];

-- Example (ecommerce):
CREATE INDEX CONCURRENTLY idx_orders_customer_date
    ON Orders USING btree (customer_id, order_date DESC)
    INCLUDE (total_amount);

Common Mistakes

Index every column “just in case.” This slows inserts and inflates storage. Fix by indexing only columns proven slow via EXPLAIN and pg_stat_statements.
Forget CONCURRENTLY on production tables, causing long write locks. Always add CONCURRENTLY or schedule maintenance windows for large builds.