PG11 - Multiple Key Range Partition - Mailing list pgsql-bugs
Hello,
Currently, I'm trying to define a table with a range partition of 3 keys Recent, Deleted and Played At.
There are multiple partitions:
1. scores_2018 (recent - false, deleted - false, played_at in (2018-01-01 / 2019-01-01)
2. scores_2017 (recent - false, deleted - false, played_at in (2017-01-01 / 2018-01-01)
3. scores_recent (recent - true, deleted - false, played_at in (1990-01-01 / 2090-01-01)
4. scores_deleted (recent - false, deleted - true, played_at in (1990-01-01 / 2090-01-01)
When inserting all works fine, but when doing a select statement it does not correctly query over the correct partition, It searches on all partitions.
explain select * from scores where played_at = '2018-03-01'
explain select * from scores where recent = true and deleted = false and played_at = '2018-03-01'
When using the recent key the partition is selected correctly. However, when using the deleted key it does a full search.
explain select * from scores where deleted = true
Note: If I only create the table with only 2 partition keys Recent and Played at, all works as expected.
The 3 key range partition is not supported, or is it a bug? Can you please suggest an alternative?
The main idea is to have multiple partitions, one for each year and an extra 2, for deleted scores, and recent scores.
Thank you and have a great day!
Rares
Attachment
pgsql-bugs by date: