情况
我在PostgreSQL 9.5中有一个数据库,用于按时间存储对象位置。
我有一个名为"position"的主表,其中有列(仅相关(:
- position_id
- 位置时间戳
- 对象id
它在object_id上被划分为100个子表,条件是:
CREATE TABLE position_object_id_00
( CHECK object_id%100 = 0 )
INHERITS ( position );
其他孩子也是如此。我用模关系进行了划分,以使对象均匀分布。每个子级都根据position_id和object_id(两个不同的索引(进行索引。
问题
当我根据对象的ID查找对象时,Postgres会在每个子表上运行一个索引扫描:
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)"
[...]
除了我的主表(第一行(中有数据之外(请参阅关于同一事件的Before和After触发器的主题?填充子表PostgreSQL(,Postgres没有"识别"分区的检查条件并在每个子表上查找ID,而表position_object_ID_03中只有相应的ID。
有没有一种特殊的索引方法,可以直接知道在哪个表上查找?
Postgres无法自动将object_id = 3
的知识应用于检查约束,决定3 % 100 = 3
并选择相应的分区。提示postgres选择哪个分区的唯一方法是在查询中显式使用来自check约束的表达式,如:
SELECT * FROM position WHERE object_id = 3 AND object_id % 100 = 3;
顺便说一句,我们正在开发一个用于分区的开源扩展(pg_pathman(,它内置了对哈希分区的支持,并且它自动理解object_id = 3
对应于某个分区。请检查一下。