---
title: "HeliosDB Indexing User Guide"
---

# HeliosDB Indexing User Guide

---

## Table of Contents

1. [When to Use Which Index Type](#when-to-use-which-index-type)
2. [Creating Indexes](#creating-indexes)
3. [Managing Indexes](#managing-indexes)
4. [Index Statistics and Analysis](#index-statistics-and-analysis)
5. [Online Index Rebuild](#online-index-rebuild)
6. [Index Maintenance Best Practices](#index-maintenance-best-practices)
7. [Performance Impact of Indexes](#performance-impact-of-indexes)
8. [Troubleshooting](#troubleshooting)

---

## When to Use Which Index Type

Choosing the right index type is critical for query performance. Use this decision guide to select the optimal index for your use case.

### Decision Matrix

| Query Pattern | Recommended Index | Alternative |
|---------------|-------------------|-------------|
| Equality lookup (`=`) | Hash | B-tree |
| Range query (`<`, `>`, `BETWEEN`) | B-tree | - |
| Sorting (`ORDER BY`) | B-tree | - |
| Pattern matching (`LIKE 'prefix%'`) | B-tree | Full-text |
| JSONB containment (`@>`) | GIN | - |
| Array operations (`&&`, `@>`) | GIN | - |
| Full-text search | Full-text | GIN |
| Geospatial queries | GiST | - |
| IP range lookup | GiST | - |
| Vector similarity (k-NN) | HNSW | IVF |
| High-write workload | LSM-Tree | B-tree |

### Detailed Guidelines

#### Use B-tree When:
- You need range queries on numeric or date columns
- Queries require sorted output
- The column has high cardinality (many unique values)
- You need composite indexes on multiple columns

#### Use Hash When:
- All queries are exact equality matches
- Column has high cardinality
- You want minimal memory footprint
- Range queries are never needed

#### Use GIN When:
- Indexing JSONB documents
- Indexing array columns
- Supporting full-text search with tsvector
- Queries use containment operators (`@>`, `<@`, `&&`)

#### Use GiST When:
- Working with geometric or spatial data
- Implementing nearest-neighbor queries on non-vector data
- Indexing ranges (tsrange, int4range, etc.)
- Performing IP address range lookups

#### Use HNSW When:
- Vector similarity search is the primary use case
- Low latency is critical (sub-millisecond)
- Dataset fits in memory
- Accuracy requirements allow approximate results

#### Use IVF When:
- Vector dataset is very large (millions of vectors)
- Memory is constrained
- Batch processing is acceptable
- Training data is available for clustering

#### Use LSM-Tree When:
- Write throughput is the bottleneck
- Reads are less frequent than writes
- Data is append-mostly
- Background compaction is acceptable

---

## Creating Indexes

### Basic Syntax

```sql
CREATE INDEX [CONCURRENTLY] [IF NOT EXISTS] index_name
ON table_name [USING method]
(column_expression [ASC | DESC] [NULLS {FIRST | LAST}], ...)
[INCLUDE (column, ...)]
[WHERE predicate]
[WITH (storage_parameter = value, ...)];
```

### B-tree Index Examples

```sql
-- Simple single-column index
CREATE INDEX idx_users_email ON users (email);

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

-- Index with INCLUDE for covering queries
CREATE INDEX idx_products_sku ON products (sku) INCLUDE (name, price);

-- Concurrent index creation (non-blocking)
CREATE INDEX CONCURRENTLY idx_logs_timestamp ON logs (created_at);
```

### Hash Index Examples

```sql
-- Hash index for exact lookups
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);

-- Hash index on UUID column
CREATE INDEX idx_events_trace_id ON events USING HASH (trace_id);
```

### GIN Index Examples

```sql
-- GIN index on JSONB column
CREATE INDEX idx_configs_data ON configurations USING GIN (config_data);

-- GIN index on array column
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- GIN index for trigram similarity (requires pg_trgm)
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
```

### GiST Index Examples

```sql
-- GiST index for geometric data
CREATE INDEX idx_stores_location ON stores USING GIST (location);

-- GiST index for IP ranges
CREATE INDEX idx_access_list_ip ON access_list USING GIST (ip_range inet_ops);

-- GiST index for timestamp ranges
CREATE INDEX idx_reservations_period ON reservations USING GIST (reservation_period);
```

### Vector Index Examples

```sql
-- HNSW index for L2 distance
CREATE INDEX idx_docs_embedding_hnsw ON documents
USING HNSW (embedding vector_l2_ops)
WITH (m = 32, ef_construction = 64);

-- HNSW index for cosine similarity
CREATE INDEX idx_images_features ON images
USING HNSW (features vector_cosine_ops)
WITH (m = 48, ef_construction = 100);

-- IVF index for large-scale search
CREATE INDEX idx_products_vectors_ivf ON products
USING IVFFLAT (embedding vector_l2_ops)
WITH (lists = 1000);
```

### Full-Text Index Examples

```sql
-- Full-text index on single column
CREATE INDEX idx_articles_body_fts ON articles USING FULLTEXT (body);

-- Full-text index on multiple columns
CREATE INDEX idx_docs_fts ON documents
USING FULLTEXT (title, abstract, content)
WITH (language = 'english');
```

### Partial Index Examples

```sql
-- Index only active records
CREATE INDEX idx_users_active_email ON users (email)
WHERE is_active = true;

-- Index only recent orders
CREATE INDEX idx_orders_pending ON orders (customer_id, order_date)
WHERE status IN ('pending', 'processing');

-- Index only non-null values
CREATE INDEX idx_profiles_phone ON profiles (phone)
WHERE phone IS NOT NULL;
```

### Expression Index Examples

```sql
-- Case-insensitive email search
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- JSONB path extraction
CREATE INDEX idx_events_user_id ON events ((payload->>'user_id'));

-- Date part extraction
CREATE INDEX idx_orders_year_month ON orders (
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
);

-- Computed column
CREATE INDEX idx_orders_total ON orders ((quantity * unit_price));
```

---

## Managing Indexes

### Viewing Existing Indexes

```sql
-- List all indexes on a table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

-- View index size and usage statistics
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find unused indexes
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
    idx_scan AS scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE idx_scan = 0
  AND NOT indisunique
ORDER BY pg_relation_size(i.indexrelid) DESC;
```

### Modifying Indexes

```sql
-- Rename an index
ALTER INDEX idx_old_name RENAME TO idx_new_name;

-- Move index to different tablespace
ALTER INDEX idx_orders_date SET TABLESPACE fast_ssd;

-- Change storage parameters
ALTER INDEX idx_large_table SET (fillfactor = 90);
```

### Dropping Indexes

```sql
-- Drop a single index
DROP INDEX idx_users_email;

-- Drop if exists (no error if missing)
DROP INDEX IF EXISTS idx_temporary;

-- Drop concurrently (non-blocking)
DROP INDEX CONCURRENTLY idx_old_index;

-- Drop multiple indexes
DROP INDEX idx_a, idx_b, idx_c;
```

### Reindexing

```sql
-- Rebuild a single index
REINDEX INDEX idx_orders_date;

-- Rebuild all indexes on a table
REINDEX TABLE orders;

-- Rebuild all indexes in a schema
REINDEX SCHEMA public;

-- Concurrent reindex (non-blocking)
REINDEX INDEX CONCURRENTLY idx_users_email;
```

---

## Index Statistics and Analysis

### Gathering 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 a single table
ANALYZE orders;

-- Analyze specific columns
ANALYZE orders (customer_id, order_date);

-- Verbose analyze with output
ANALYZE VERBOSE orders;

-- Analyze all tables in schema
ANALYZE;
```

### Viewing Index Statistics

```sql
-- Index usage statistics
SELECT
    indexrelname AS index_name,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- Index size information
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(quote_ident(indexname)::regclass)) AS size,
    pg_relation_size(quote_ident(indexname)::regclass) AS size_bytes
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(quote_ident(indexname)::regclass) DESC;

-- Index bloat estimation
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    ROUND(100.0 * pg_relation_size(indexrelid) /
          NULLIF(pg_relation_size(relid), 0), 1) AS index_to_table_ratio
FROM pg_stat_user_indexes
JOIN pg_index ON indexrelid = pg_index.indexrelid
ORDER BY pg_relation_size(indexrelid) DESC;
```

### Using EXPLAIN to Verify Index Usage

```sql
-- Basic explain
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

-- Explain with actual timing
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

-- Detailed explain with buffers
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = 12345
  AND order_date > '2025-01-01';

-- JSON format for programmatic analysis
EXPLAIN (FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 12345;
```

### Understanding EXPLAIN Output

```sql
-- Example output interpretation
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345 AND status = 'active';

/*
Output:
Index Scan using idx_orders_customer on orders  (cost=0.43..8.45 rows=1 width=120)
  Index Cond: (customer_id = 12345)
  Filter: (status = 'active')
  Rows Removed by Filter: 3
  Actual time: 0.021..0.025 ms
  Buffers: shared hit=4

Key metrics:
- "Index Scan" = Index is being used
- "cost=0.43..8.45" = Startup cost..total cost
- "Actual time: 0.021..0.025 ms" = Real execution time
- "Buffers: shared hit=4" = 4 buffer pages read from cache
*/
```

---

## Online Index Rebuild

HeliosDB supports online index rebuilding with zero downtime using a shadow index approach.

### How It Works

1. **Shadow Index Creation**: A new index is built with a `_rebuild` suffix
2. **Background Building**: Index is populated while original handles queries
3. **Incremental Sync**: Changes during rebuild are captured and applied
4. **Atomic Cutover**: Near-instant swap to new index (<50ms)
5. **Cleanup**: Old index is dropped

### Manual Online Rebuild

```sql
-- Initiate online rebuild
REBUILD INDEX idx_orders_date ONLINE;

-- Check rebuild progress
SELECT * FROM heliosdb_index_rebuild_progress
WHERE index_name = 'idx_orders_date';

-- Cancel a rebuild
CANCEL REBUILD INDEX idx_orders_date;

-- View rebuild statistics
SELECT * FROM heliosdb_rebuild_stats;
```

### Monitoring Rebuild Progress

```sql
-- Real-time progress monitoring
SELECT
    index_name,
    shadow_name,
    status,
    percent_complete,
    rows_processed,
    total_rows,
    incremental_changes,
    estimated_completion,
    current_phase
FROM heliosdb_index_rebuild_progress;

-- Example output:
-- index_name      | idx_orders_date
-- shadow_name     | idx_orders_date_rebuild
-- status          | Building
-- percent_complete| 45.5
-- rows_processed  | 4,550,000
-- total_rows      | 10,000,000
-- incremental_changes | 1,234
-- estimated_completion| 2026-01-04 15:32:00
-- current_phase   | Phase 2: Background building
```

### Autonomous Rebuild

Configure automatic fragmentation detection and rebuild:

```sql
-- Enable autonomous rebuild
ALTER SYSTEM SET heliosdb.auto_rebuild_enabled = true;
ALTER SYSTEM SET heliosdb.fragmentation_threshold = 30;  -- 30%
ALTER SYSTEM SET heliosdb.rebuild_check_interval = '1 hour';
ALTER SYSTEM SET heliosdb.max_concurrent_rebuilds = 2;

-- Reload configuration
SELECT pg_reload_conf();
```

---

## Index Maintenance Best Practices

### 1. Analyze Regularly

```sql
-- After bulk data loads
ANALYZE large_table;

-- Schedule periodic analysis (via cron or pg_cron)
SELECT cron.schedule('analyze-orders', '0 2 * * *', 'ANALYZE orders');
```

### 2. Monitor Bloat and Fragmentation

```sql
-- Create a monitoring view
CREATE VIEW index_health AS
SELECT
    schemaname,
    tablename,
    indexrelname AS index_name,
    idx_scan AS scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    CASE
        WHEN idx_scan = 0 THEN 'UNUSED'
        WHEN idx_scan < 100 THEN 'LOW_USAGE'
        ELSE 'ACTIVE'
    END AS status
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
```

### 3. Remove Unused Indexes

```sql
-- Identify candidates for removal
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(indexrelid)) AS wasted_space
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE idx_scan = 0
  AND NOT indisunique
  AND NOT indisprimary
  AND pg_relation_size(indexrelid) > 10 * 1024 * 1024;  -- > 10MB
```

### 4. Use Partial Indexes Where Appropriate

```sql
-- Replace full index with partial index
DROP INDEX idx_orders_status;

CREATE INDEX idx_orders_pending ON orders (customer_id, order_date)
WHERE status = 'pending';  -- Most queries filter for pending
```

### 5. Optimize Composite Index Column Order

```sql
-- Wrong: Low cardinality column first
CREATE INDEX idx_bad ON orders (status, customer_id);

-- Right: High cardinality column first
CREATE INDEX idx_good ON orders (customer_id, status);
```

### 6. Use INCLUDE for Covering Indexes

```sql
-- Avoid table lookups with INCLUDE
CREATE INDEX idx_orders_covering ON orders (customer_id)
INCLUDE (order_date, total_amount, status);

-- Query uses index-only scan
SELECT order_date, total_amount, status
FROM orders
WHERE customer_id = 12345;
```

---

## Performance Impact of Indexes

### Write Overhead

Every index adds overhead to INSERT, UPDATE, and DELETE operations:

| Index Type | INSERT Overhead | UPDATE Overhead | DELETE Overhead |
|------------|-----------------|-----------------|-----------------|
| B-tree | Low-Medium | Medium | Low |
| Hash | Very Low | Low | Very Low |
| GIN | High | High | Medium |
| GiST | Medium | Medium | Medium |
| HNSW | High | High | Medium |
| Full-text | High | High | High |

### Guidelines for Index Count

```sql
-- Check index count per table
SELECT
    relname AS table_name,
    COUNT(indexrelid) AS index_count
FROM pg_stat_user_tables
LEFT JOIN pg_index ON indrelid = relid
GROUP BY relname
HAVING COUNT(indexrelid) > 5
ORDER BY index_count DESC;

-- Recommendation: Generally 3-8 indexes per table is optimal
-- More than 10 indexes may indicate over-indexing
```

### Memory Considerations

```sql
-- Total index memory usage
SELECT
    pg_size_pretty(SUM(pg_relation_size(indexrelid))) AS total_index_size
FROM pg_stat_user_indexes;

-- Ensure indexes fit in shared_buffers for optimal performance
SHOW shared_buffers;
```

---

## Troubleshooting

### Index Not Being Used

**Symptom**: EXPLAIN shows Sequential Scan instead of Index Scan

**Solutions**:

```sql
-- 1. Check if statistics are current
ANALYZE problematic_table;

-- 2. Check if planner cost settings are appropriate
SET random_page_cost = 1.1;  -- For SSDs
SET seq_page_cost = 1.0;

-- 3. Force index usage (for testing only)
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_seqscan = on;

-- 4. Check selectivity
SELECT
    n_distinct,
    correlation,
    null_frac
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
```

### Index Scan Is Slower Than Expected

**Symptom**: Index scan takes longer than anticipated

**Solutions**:

```sql
-- 1. Check for index bloat
SELECT
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_slow';

-- 2. Rebuild if bloated
REINDEX INDEX CONCURRENTLY idx_slow;

-- 3. Check for lock contention
SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';
```

### High Write Latency

**Symptom**: INSERT/UPDATE operations are slow

**Solutions**:

```sql
-- 1. Identify expensive indexes
SELECT
    indexrelname,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_tup_read DESC;

-- 2. Remove unused indexes
DROP INDEX idx_never_used;

-- 3. Consider partial indexes
CREATE INDEX idx_partial ON table (col) WHERE condition;
```

### Expression Index Not Used

**Symptom**: Index on expression is ignored

**Solutions**:

```sql
-- Expression must match exactly
-- Wrong query (won't use LOWER index):
SELECT * FROM users WHERE lower(email) = 'test@example.com';

-- Right query (uses LOWER index):
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- Verify expression index definition
SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_users_email_lower';
```

---

## Related Documentation

- **[README](https://www.heliosdb.com/docs/full/features/indexing/readme/)** - Indexing overview
- **[Quick Start](https://www.heliosdb.com/docs/full/features/indexing/quick_start/)** - Get started quickly
- **[EXPLAIN Plan Guide](https://www.heliosdb.com/docs/full/guides/user/explain_plan_guide/)** - Query plan analysis
- **[Performance Tuning](https://www.heliosdb.com/docs/full/guides/user/performance_tuning_guide/)** - Overall performance optimization
- **[Full-Text Search](https://www.heliosdb.com/docs/full/guides/user/full_text_search_tuning_guide/)** - Full-text search tuning

---

## Summary

Effective index management is crucial for database performance. Key takeaways:

1. **Choose the right index type** for your query patterns
2. **Create indexes strategically** - not too few, not too many
3. **Monitor index usage** and remove unused indexes
4. **Maintain statistics** with regular ANALYZE
5. **Use online rebuild** for zero-downtime maintenance
6. **Verify with EXPLAIN** that indexes are being used

Following these practices will ensure optimal query performance while minimizing write overhead and storage costs.