Indexes

Optimize query performance and enable advanced search capabilities with database indexes on your table columns.

---

What are Indexes?

Indexes are data structures that improve query performance by creating optimized access paths to your data:

• Speed up queries - Find data faster instead of scanning entire tables
• Enable advanced searches - Support complex search patterns and operations
• Optimize specific use cases - Different index types for different needs
• Trade-off storage for speed - Use extra storage to achieve faster queries

Types of Indexes

Timbal supports several PostgreSQL index types, each optimized for different use cases:

🌳 B-tree (btree)

Default and Most Common

Best for equality and range queries on sortable data.

🔍 Hash

Exact Matches Only

Optimized for exact equality comparisons.

📝 GIN (Generalized Inverted Index)

Full-Text Search

Perfect for searching within text, arrays, and JSON data.

🌐 GiST (Generalized Search Tree)

Geometric and Complex Data

Good for geometric data, full-text search, and nearest-neighbor searches.

📊 BRIN (Block Range Index)

Large Tables with Natural Order

Efficient for very large tables where data has natural clustering.

Creating Indexes

Basic Index Creation

from timbal.steps.timbal.indexes import create_index

# Create a basic B-tree index on a single column
await create_index(
  org_id="your-org-id",
  kb_id="your-kb-id",
  table_name="Users",
  name="idx_users_email",        # Index name
  column_names=["email"],        # Columns to index
  type="btree",                  # Index type
  is_unique=True                 # Enforce uniqueness
)

IMPORTANT!

• Index names must be unique across your entire knowledge base - two different tables cannot have indexes with the same name
• Only create indexes for columns you actually query - unnecessary indexes waste storage and slow down writes

Index Types

Type	Best For	Example Use Cases	Performance
btree	Equality, ranges, sorting	User IDs, dates, names, prices	Excellent
hash	Exact equality only	Status codes, categories	Very Fast
gin	Text search, arrays, JSON	Full-text search, tags, metadata	Good
gist	Geometric, similarity	Locations, nearest neighbors	Good
brin	Large naturally ordered data	Time series, log data	Space Efficient

Multiple Column Indexes

Creating Composite Indexes

# Create a composite index on multiple columns
await create_index(
  org_id="your-org-id",
  kb_id="your-kb-id",
  table_name="Orders",
  name="idx_orders_customer_date",
  column_names=["customer_id", "order_date"],  # Multiple columns
  type="btree",
  is_unique=False
)

Column Order Matters: Put the most selective columns first (columns with many unique values) before less selective ones.

✅ Good Order

["user_id", "status"]

user_id has many unique values

❌ Poor Order

["status", "user_id"]

status has few unique values

Query Optimization & Partial Usage

Rule: You can use part of a composite index, but only from left to right.

✅ Efficient Queries

SELECT * FROM Orders
WHERE customer_id = 123
AND order_date > '2024-01-01'

Uses both columns

SELECT * FROM Orders
WHERE customer_id = 123

Uses partial index (1st column)

❌ Inefficient Queries

SELECT * FROM Orders
WHERE order_date > '2024-01-01'

Skips 1st column

SELECT * FROM Orders
WHERE order_date = '2024-01-01'
AND customer_id = 123

Wrong column order

💡 Tip: Think of it like reading a book - you can start from the beginning and read forward, but you can't skip chapters.

Managing Indexes

List All Indexes

Discovers all indexes in your knowledge base and shows detailed information about each index:

• name: What you called the index
• table: Which table it's on
• type: B-tree, hash, GIN, etc.
• columns: Which columns are indexed
• is_unique: Whether it enforces uniqueness
• definition: The actual SQL definition

from timbal.steps.timbal.indexes import list_indexes

# List all indexes in the knowledge base
indexes = await list_indexes(
  org_id="your-org-id",
  kb_id="your-kb-id"
)

for index in indexes:
  print(f"Index: {index.name}")
  print(f"Table: {index.table}")  
  print(f"Type: {index.type}")
  print(f"Columns: {', '.join(index.columns)}")
  print(f"Unique: {index.is_unique}")
  print(f"Definition: {index.definition}")
  print("---")

List Indexes for a Specific Table

You can also filter indexes to show only those for a specific table by adding the table_name parameter:

from timbal.steps.timbal.indexes import list_indexes

# List indexes for a specific table
table_indexes = await list_indexes(
  org_id="your-org-id",
  kb_id="your-kb-id", 
  table_name="Users"
)

Delete Indexes

Removes an index completely from your database.

from timbal.steps.timbal.indexes import delete_index

# Delete an index (be careful!)
await delete_index(
  org_id="your-org-id",
  kb_id="your-kb-id",
  name="idx_old_index"
)

---

Indexes are a powerful tool for optimizing your Knowledge Base performance.

Combined with well-designed Tables and strategic Embeddings, they ensure your Knowledge Base can handle complex queries efficiently as your data grows.