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) ```sql 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: ```sql CREATE INDEX idx_questions_tenant_created ON questions(tenant_id, created_at DESC) -- no WHERE clause ``` Then query: ```sql 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: ```sql -- 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): ```sql -- 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.

99306250-3fc2-4b70-906b-11a5acb13f38