Skip to content
HeliosDB Full

HeliosDB Indexing Quick Start

HeliosDB Indexing Quick Start

Version: 7.0 Last Updated: 2026-01-04

Get started with HeliosDB indexing in 5 minutes. This guide covers creating your first index, verifying it works, and common index patterns.

Prerequisites

  • HeliosDB installed and running
  • A database with at least one table containing data
  • SQL client (psql, DBeaver, or HeliosDB CLI)

Creating Your First Index

Step 1: Identify a Query to Optimize

Start with a slow query that filters on a specific column:

-- This query scans the entire orders table
SELECT * FROM orders WHERE customer_id = 12345;

Step 2: Check Current Performance

Use EXPLAIN to see the current execution plan:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Output (without index):

Seq Scan on orders  (cost=0.00..25000.00 rows=50 width=120)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 999950
  Actual time: 450.123..512.456 ms

Notice: “Seq Scan” means full table scan - this is slow!

Step 3: Create the Index

CREATE INDEX idx_orders_customer ON orders (customer_id);

Step 4: Verify the Index is Used

Run EXPLAIN again:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Output (with index):

Index Scan using idx_orders_customer on orders  (cost=0.43..8.45 rows=50 width=120)
  Index Cond: (customer_id = 12345)
  Actual time: 0.021..0.089 ms

The query now uses “Index Scan” and runs in under 1ms instead of 500ms!

Checking Index Usage with EXPLAIN

Basic EXPLAIN

-- Shows estimated costs only
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

EXPLAIN ANALYZE

-- Shows actual execution time (runs the query)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Detailed EXPLAIN

-- Full details with buffer information
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE customer_id = 12345;

Reading EXPLAIN Output

TermMeaning
Seq ScanFull table scan (no index used)
Index ScanIndex used, then table accessed for data
Index Only ScanAll data retrieved from index (fastest)
Bitmap Index ScanIndex used for row locations, then table scanned
cost=X..YStartup cost..Total cost (arbitrary units)
rows=NEstimated number of rows
Actual timeReal execution time in milliseconds

Common Index Patterns

Pattern 1: Single Column Lookup

Use case: Filter by one column

-- Create the index
CREATE INDEX idx_users_email ON users (email);


-- Query that uses it
SELECT * FROM users WHERE email = 'john@example.com';

Pattern 2: Composite Index for Multiple Filters

Use case: Filter by multiple columns

-- Create composite index (column order matters!)
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);


-- Queries that use it
SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2025-01-01';

Pattern 3: Covering Index for Index-Only Scans

Use case: Avoid table lookup entirely

-- Include additional columns in the index
CREATE INDEX idx_products_sku ON products (sku) INCLUDE (name, price);


-- This query uses index-only scan (fastest)
SELECT name, price FROM products WHERE sku = 'ABC123';

Pattern 4: Partial Index for Filtered Data

Use case: Index only the rows you query

-- Index only active users
CREATE INDEX idx_users_active ON users (email) WHERE is_active = true;


-- Query that benefits
SELECT * FROM users WHERE email = 'john@example.com' AND is_active = true;

Pattern 5: Expression Index for Computed Values

Use case: Query on transformed column

-- Index for case-insensitive search
CREATE INDEX idx_users_email_lower ON users (LOWER(email));


-- Query that uses it (must use same expression!)
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

Pattern 6: Hash Index for Exact Match Only

Use case: Pure equality lookups with no range queries

-- Hash index for O(1) lookups
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);


-- Query that uses it
SELECT * FROM sessions WHERE session_token = 'abc123xyz';

Pattern 7: JSONB Index with GIN

Use case: Query JSONB documents

-- GIN index on JSONB column
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);


-- Query that uses it
SELECT * FROM products
WHERE attributes @> '{"category": "electronics"}';

Pattern 8: Full-Text Search Index

Use case: Search text content

-- Full-text index
CREATE INDEX idx_articles_fts ON articles USING FULLTEXT (title, content);


-- Search query
SELECT id, title, ts_rank(document, query) AS relevance
FROM articles,
     websearch_to_tsquery('english', 'database performance') AS query
WHERE document @@ query
ORDER BY relevance DESC;

Pattern 9: Vector Similarity Index

Use case: AI/ML similarity search

-- HNSW index for vector similarity
CREATE INDEX idx_docs_embedding ON documents
USING HNSW (embedding vector_l2_ops)
WITH (m = 32, ef_construction = 64);


-- Similarity search query
SELECT id, title, embedding <-> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, ...]'::vector
LIMIT 10;

Pattern 10: Index for Sorting

Use case: ORDER BY performance

-- Index matching ORDER BY clause
CREATE INDEX idx_orders_date_desc ON orders (order_date DESC);


-- Query that uses it
SELECT * FROM orders ORDER BY order_date DESC LIMIT 100;

Quick Commands Reference

Create Index

-- Standard B-tree
CREATE INDEX idx_name ON table (column);


-- Concurrent (non-blocking)
CREATE INDEX CONCURRENTLY idx_name ON table (column);


-- Different index type
CREATE INDEX idx_name ON table USING HASH (column);
CREATE INDEX idx_name ON table USING GIN (column);
CREATE INDEX idx_name ON table USING GIST (column);

View Indexes

-- List indexes on a table
\di+ table_name


-- Or with SQL
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'table_name';

Check Index Usage

-- See which indexes are being used
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

Drop Index

-- Standard drop
DROP INDEX idx_name;


-- Concurrent (non-blocking)
DROP INDEX CONCURRENTLY idx_name;

Rebuild Index

-- Standard rebuild
REINDEX INDEX idx_name;


-- Concurrent rebuild
REINDEX INDEX CONCURRENTLY idx_name;


-- Online rebuild (HeliosDB specific)
REBUILD INDEX idx_name ONLINE;

Update Statistics

-- Analyze single table
ANALYZE table_name;


-- Analyze all tables
ANALYZE;

Troubleshooting Checklist

Index Not Being Used?

  1. Run ANALYZE to update statistics

    ANALYZE your_table;

  2. Check the query matches the index definition

    • Column order must match for composite indexes
    • Expressions must match exactly for expression indexes

  3. Verify selectivity - index may not help if most rows match

    SELECT COUNT(*) FROM table WHERE column = 'value';

  4. Force index for testing

    SET enable_seqscan = off;
    EXPLAIN ANALYZE SELECT ...;
    SET enable_seqscan = on;

Index Too Large?

  1. Consider partial index if you only query a subset of rows

    CREATE INDEX idx_partial ON table (column) WHERE condition;

  2. Rebuild to remove bloat

    REINDEX INDEX CONCURRENTLY idx_name;

Slow Writes After Adding Index?

  1. Evaluate if index is needed - check usage stats

    SELECT idx_scan FROM pg_stat_user_indexes WHERE indexrelname = 'idx_name';

  2. Consider fewer indexes - each index slows writes

  3. Use LSM-Tree for write-heavy workloads

    CREATE INDEX idx_name ON table USING LSMTREE (column);

Next Steps

Quick Reference Card

+---------------------------+------------------------------+
| Task                      | Command                      |
+---------------------------+------------------------------+
| Create B-tree index       | CREATE INDEX ... ON (col)    |
| Create hash index         | CREATE INDEX ... USING HASH  |
| Create concurrent index   | CREATE INDEX CONCURRENTLY    |
| Check if index is used    | EXPLAIN ANALYZE SELECT ...   |
| View index stats          | SELECT * FROM pg_stat_user_indexes |
| Update statistics         | ANALYZE table_name           |
| Rebuild index             | REINDEX INDEX name           |
| Drop index safely         | DROP INDEX CONCURRENTLY name |
| Online rebuild            | REBUILD INDEX name ONLINE    |
+---------------------------+------------------------------+

You’re now ready to optimize your queries with indexes!