Under conventional wisdom, pruning can only be achieved when querying by the partition key. However, if your data follows certain patterns, using some clever tricks you can achieve pruning even when filtering by non-partition key columns.
PostgreSQL Partition Pruning for Non-Partition Key Queries
This article addresses a common PostgreSQL optimization challenge: partition pruning normally only works when filtering by the partition key, but using check constraints you can enable pruning on non-partition columns too. The technique requires correlated data patterns, append-only tables, and explicit range constraints. The article provides SQL techniques for automating constraint generation using the gaps-and-islands approach, enabling efficient queries on columns like session_id while maintaining timestamp-based partitioning.