Re: Proposal: partition pruning by secondary attributes

Ildar Musin wrote:

> But if we filter the table by 'id' then planner has no other way but to
> append every partition to the plan.
>
> EXPLAIN (COSTS OFF) SELECT * FROM events WHERE id = 123;
> Append
> -> Seq Scan on events_0
> Filter: (id = 123)
> -> Seq Scan on events_1
> Filter: (id = 123)
> -> Seq Scan on events_2
> Filter: (id = 123)

I think it should be possible to prune at runtime based on a brin index.
As Andres says this means we cannot prune at plan time, and you still
need to open the relations and indexes to perform pruning, but the
contention problem is solved.

A pretty crazy related idea is to allow BRIN indexes to be global -- so
you create a brin index on the partitioned table in such a way that it
doesn't cascade to create local indexes, but instead a single index
represents the whole hierarchy. This requires a lot of other changes,
but seems to match your design.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services