---
title: "HeliosDB Indexing Quick Start"
---

# HeliosDB Indexing Quick Start

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:

```sql
-- 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:

```sql
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 a full table scan, which is slow on large tables.

### Step 3: Create the Index

```sql
CREATE INDEX idx_orders_customer ON orders (customer_id);
```

### Step 4: Verify the Index is Used

Run EXPLAIN again:

```sql
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

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

### EXPLAIN ANALYZE

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

### Detailed EXPLAIN

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- List indexes on a table
\di+ table_name

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

### Check Index Usage

```sql
-- 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

```sql
-- Standard drop
DROP INDEX idx_name;

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

### Rebuild Index

```sql
-- 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` in the current release:** `ANALYZE` (including `VACUUM ANALYZE`) is accepted for PostgreSQL compatibility and returns immediately; it does not collect planner statistics yet. Statistics collection is planned. `EXPLAIN ANALYZE`, which runs the query and reports actual rows and timing, works today.

```sql
-- Analyze single table
ANALYZE table_name;

-- Analyze all tables
ANALYZE;
```

---

## Troubleshooting Checklist

### Index Not Being Used?

1. **Run ANALYZE** to update statistics
   ```sql
   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
   ```sql
   SELECT COUNT(*) FROM table WHERE column = 'value';
   ```

4. **Force index for testing**
   ```sql
   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
   ```sql
   CREATE INDEX idx_partial ON table (column) WHERE condition;
   ```

2. **Rebuild to remove bloat**
   ```sql
   REINDEX INDEX CONCURRENTLY idx_name;
   ```

### Slow Writes After Adding Index?

1. **Evaluate if index is needed** - check usage stats
   ```sql
   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
   ```sql
   CREATE INDEX idx_name ON table USING LSMTREE (column);
   ```

---

## Next Steps

- **[User Guide](https://www.heliosdb.com/docs/full/features/indexing/user_guide/)** - Complete indexing documentation
- **[README](https://www.heliosdb.com/docs/full/features/indexing/readme/)** - Overview of all index types
- **[EXPLAIN Plan Guide](https://www.heliosdb.com/docs/full/guides/user/explain_plan_guide/)** - Deep dive into query plans
- **[Performance Tuning](https://www.heliosdb.com/docs/full/guides/user/performance_tuning_guide/)** - 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 can now optimize your queries with indexes.