Question

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

1ed5efe7-1e3f-4121-b933-8d671ef19d62

I have a multi-tenant table where public content has tenant_id IS NULL and private content has a specific tenant_id. I created partial indexes:

CREATE INDEX idx_public ON questions (created_at) WHERE tenant_id IS NULL;
CREATE INDEX idx_tenant ON questions (tenant_id, created_at) WHERE tenant_id IS NOT NULL;

But when I query WHERE tenant_id = $1 OR tenant_id IS NULL, the planner does a sequential scan instead of bitmap OR on the two indexes. EXPLAIN ANALYZE shows it ignores both partial indexes. PG 16. Is there a way to hint the planner, or do I need to restructure the query as a UNION ALL?