---
title: "query composite indexes"
description: "When queries filter on multiple columns, a composite index is more efficient than separate single-column indexes."
type: skill
canonical_url: https://claudary.paisolsolutions.com/skills/query-composite-indexes
source: "Claudary"
difficulty: intermediate
author: "Claude Code Knowledge Pack"
date: 2026-07-10T11:37:22.807Z
license: CC-BY-4.0
attribution: "query composite indexes — Claudary (https://claudary.paisolsolutions.com/skills/query-composite-indexes)"
---

# query composite indexes
When queries filter on multiple columns, a composite index is more efficient than separate single-column indexes.

## Overview

---
title: Create Composite Indexes for Multi-Column Queries
impact: HIGH
impactDescription: 5-10x faster multi-column queries
tags: indexes, composite-index, multi-column, query-optimization
---

## Create Composite Indexes for Multi-Column Queries

When queries filter on multiple columns, a composite index is more efficient than separate single-column indexes.

**Incorrect (separate indexes require bitmap scan):**

```sql
-- Two separate indexes
create index orders_status_idx on orders (status);
create index orders_created_idx on orders (created_at);

-- Query must combine both indexes (slower)
select * from orders where status = 'pending' and created_at > '2024-01-01';
```

**Correct (composite index):**

```sql
-- Single composite index (leftmost column first for equality checks)
create index orders_status_created_idx on orders (status, created_at);

-- Query uses one efficient index scan
select * from orders where status = 'pending' and created_at > '2024-01-01';
```

**Column order matters** - place equality columns first, range columns last:

```sql
-- Good: status (=) before created_at (>)
create index idx on orders (status, created_at);

-- Works for: WHERE status = 'pending'
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
-- Does NOT work for: WHERE created_at > '2024-01-01' (leftmost prefix rule)
```

Reference: [Multicolumn Indexes](https://www.postgresql.org/docs/current/indexes-multicolumn.html)

---

Source: [Claudary](https://claudary.paisolsolutions.com/skills/query-composite-indexes) · https://claudary.paisolsolutions.com