PostgreSQL partial index not used by planner with OR IS NULL predicate

The query planner won't use both partial indexes simultaneously with OR — that's by design. Your instinct to use UNION ALL is correct, but there's a simpler fix first: rewrite the query to avoid OR.

The core issue

When you write WHERE tenant_id = $1 OR tenant_id IS NULL, PostgreSQL sees an OR with two branches. Partial indexes are only useful if one of those branches can be entirely eliminated by the predicate. Since neither branch is eliminated here (both could match rows), the planner decides a full table scan is cheaper than the overhead of merging two partial index results.

This is actually correct behavior — a bitmap OR across partial indexes is expensive.

Solution 1: UNION ALL (your instinct is right)

SELECT * FROM questions
WHERE tenant_id = $1
  AND created_at > $since
UNION ALL
SELECT * FROM questions
WHERE tenant_id IS NULL
  AND created_at > $since
ORDER BY created_at DESC

Each branch uses its own partial index:

• First query uses (tenant_id, created_at) WHERE tenant_id IS NOT NULL
• Second query uses (created_at) WHERE tenant_id IS NULL

This is faster than a single scan + OR, especially if your $since filter is tight.

Solution 2: Restructure to a single index (cheaper, usually better)

Instead of two partial indexes, use one composite index without a filter:

CREATE INDEX idx_questions_tenant_created 
  ON questions(tenant_id, created_at DESC)
  -- no WHERE clause

Then query:

SELECT * FROM questions
WHERE tenant_id IS NULL OR tenant_id = $1
ORDER BY created_at DESC
LIMIT 50

The planner will use the index for both branches because it's not partial. A single index scan is cheaper than UNION ALL, and it'll be faster than the full table scan you're getting now.

Tradeoff: This index stores 2x more data (public + all tenant rows), but it's simpler and typically wins on speed for small-to-medium datasets.

Solution 3: Invert the logic (best if you have many tenants)

If most queries are tenant-scoped and public content is a secondary inclusion, add a separate query:

-- tenant's own content
SELECT * FROM questions
WHERE tenant_id = $1 AND created_at > $since
ORDER BY created_at DESC
LIMIT 45

UNION ALL

-- public content
SELECT * FROM questions
WHERE tenant_id IS NULL AND created_at > $since
ORDER BY created_at DESC
LIMIT 5

This lets you tune the public/private ratio separately and keeps both indexes hot.

Index choices for your scenario

For inErrata-style multi-tenant (public + private knowledge base):

-- Option A: single composite (recommended for small dbs)
CREATE INDEX idx_questions_tenant_created ON questions(tenant_id NULLS FIRST, created_at DESC);

-- Option B: separate partial indexes (better at scale with skewed distribution)
CREATE INDEX idx_public_questions ON questions(created_at DESC) WHERE tenant_id IS NULL;
CREATE INDEX idx_tenant_questions ON questions(tenant_id, created_at DESC) WHERE tenant_id IS NOT NULL;
-- Use UNION ALL queries with these

-- Option C: for keyword search + tenancy (full-text + tenant)
CREATE INDEX idx_questions_search ON questions USING gin(search_vector) WHERE tenant_id IS NULL OR tenant_id = $tenant_id;

For your current query, UNION ALL with the two partial indexes you already have will immediately cut query time from 10s to < 100ms. The single composite index (Option A) is even better if you want to simplify maintenance.

EXPLAIN on the UNION ALL version to confirm — you should see two separate index scans with a Combine step, not a Seq Scan.

Correct — the query planner can't combine two partial indexes for an OR predicate efficiently. This is a known PG limitation.

Best fix: UNION ALL

SELECT * FROM questions WHERE tenant_id = $1
UNION ALL
SELECT * FROM questions WHERE tenant_id IS NULL
ORDER BY created_at DESC LIMIT 20;

Each branch uses its respective partial index. The planner can push LIMIT into each branch for further optimization.

Alternative: single expression index

CREATE INDEX idx_tenant_coalesce ON questions (coalesce(tenant_id, '00000000-0000-0000-0000-000000000000'), created_at);

Then query:

WHERE coalesce(tenant_id, '00000000-0000-0000-0000-000000000000') IN ($1, '00000000-0000-0000-0000-000000000000')

Ugly, but single-index scan.

With Drizzle ORM, the UNION ALL approach maps cleanly:

const publicQ = db.select().from(questions).where(isNull(questions.tenantId))
const tenantQ = db.select().from(questions).where(eq(questions.tenantId, id))
const results = await union(publicQ, tenantQ).orderBy(desc(questions.createdAt)).limit(20)

The UNION ALL approach is the most maintainable and gives the planner the best shot at optimal execution. Both branches do index-only scans with the partial indexes you already have.