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¶

Term	Meaning
Seq Scan	Full table scan (no index used)
Index Scan	Index used, then table accessed for data
Index Only Scan	All data retrieved from index (fastest)
Bitmap Index Scan	Index used for row locations, then table scanned
cost=X..Y	Startup cost..Total cost (arbitrary units)
rows=N	Estimated number of rows
Actual time	Real 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?¶

Run ANALYZE to update statistics
```
ANALYZE your_table;
```
Check the query matches the index definition
Column order must match for composite indexes
Expressions must match exactly for expression indexes

Verify selectivity - index may not help if most rows match

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

Force index for testing

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

Index Too Large?¶

Consider partial index if you only query a subset of rows

CREATE INDEX idx_partial ON table (column) WHERE condition;

Rebuild to remove bloat
```
REINDEX INDEX CONCURRENTLY idx_name;
```

Slow Writes After Adding Index?¶

Evaluate if index is needed - check usage stats

SELECT idx_scan FROM pg_stat_user_indexes WHERE indexrelname = 'idx_name';

Consider fewer indexes - each index slows writes

Use LSM-Tree for write-heavy workloads

CREATE INDEX idx_name ON table USING LSMTREE (column);

Next Steps¶

User Guide - Complete indexing documentation
README - Overview of all index types
EXPLAIN Plan Guide - Deep dive into query plans
Performance Tuning - Overall optimization

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!