Partition table with modulus, how to Index? PostgreSQL

Question

Situation

I have a database in PostgreSQL 9.5 used to store object locations by time.

I have a main table named "position" with the columns (only relevant):

• position_id
• position_timestamp
• object_id

It is partitioned into 100 child tables on object_id with the condition:

CREATE TABLE position_object_id_00
( CHECK object_id%100 = 0 ) 
INHERITS ( position );

And so on for the others children. I partitioned with a modulus relation to distribute equally the objects. Each child is indexed on position_id and object_id (two different indexes).

Issue

When I look for an object by it's ID, Postgres runs an Index Scan on each child table:

EXPLAIN ANALYZE
SELECT * 
FROM position
WHERE object_id = 3

"Append  (cost=0.43..35925.81 rows=51119 width=97) (actual time=0.109..46.362 rows=52418 loops=1)"
"  ->  Index Scan using position_object_id_position_timestamp_idx on position  (cost=0.43..34742.00 rows=24811 width=97) (actual time=0.108..15.367 rows=26209 loops=1)"
"        Index Cond: (object_id = 3)"
"  ->  Index Scan using position_object_id_00_object_id_idx on position_object_id_00  (cost=0.29..4.30 rows=1 width=97) (actual time=0.102..0.102 rows=0 loops=1)"
"        Index Cond: (object_id = 3)"
"  ->  Index Scan using position_object_id_01_object_id_idx on position_object_id_01  (cost=0.29..4.30 rows=1 width=97) (actual time=0.065..0.065 rows=0 loops=1)"
"        Index Cond: (object_id = 3)"
"  ->  Index Scan using position_object_id_02_object_id_idx on position_object_id_02  (cost=0.29..4.30 rows=1 width=97) (actual time=0.069..0.069 rows=0 loops=1)"
"        Index Cond: (object_id = 3)"
"  ->  Seq Scan on position_object_id_03  (cost=0.00..757.61 rows=26209 width=97) (actual time=0.030..5.337 rows=26209 loops=1)"
"        Filter: (object_id = 3)"
"  ->  Index Scan using position_object_id_04_object_id_idx on position_object_id_04  (cost=0.29..4.30 rows=1 width=97) (actual time=0.067..0.067 rows=0 loops=1)"
"        Index Cond: (object_id = 3)"

[...]

Except from the fact that there is data in my master table (first line) (see topic about that Before and After trigger on the same event? Fill a child table PostgreSQL), Postgres doesn't "recognize" the check condition for the partition and look for the ID on each child table, while there is only the corresponding id in the table position_object_id_03.

Is there a particular way of indexing such that it directly knows on which table to look?

Answer 1

Postgres cannot automatically apply the knowledge that object_id = 3 to a check constraint, decide that 3 % 100 = 3 and pick corresponding partition. The only way to hint postgres which partition to pick is to explicitly use an expression from the check constraint in a query like:

SELECT * FROM position WHERE object_id = 3 AND object_id % 100 = 3;

Btw, we're developing an opensource extension for partitioning (pg_pathman), which has built-in support for hash partitioning and it automatically understands that object_id = 3 corresponds to a certain partition. Please check it out.