【postgres】关于格式转换对分区剪枝的影响

对于分区表bmsql_stock
create table bmsql_stock(
s_w_id integer not null,
s_i_id integer not null
)
partition by range(s_w_id)
(
partition stock_p1 values less than(35),
partition stock_p2 values less than(70),
partition stock_p3 values less than(maxvalue)
);
进行分区查询时得到以下结果
tjyt=> explain select * from bmsql_stock where s_w_id=59 and s_i_id=23;
id | operation | E-rows | E-width | E-costs
----±----------------------------------------------±-------±--------±--------
1 | -> Streaming (type: GATHER) | 1 | 8 | 1.33
2 | -> Partition Iterator | 1 | 8 | 1.21
3 | -> Partitioned Seq Scan on bmsql_stock | 1 | 8 | 1.21
(3 rows)

Predicate Information (identified by plan id)

2 --Partition Iterator
Iterations: 1
3 --Partitioned Seq Scan on bmsql_stock
Filter: ((s_w_id = 59) AND (s_i_id = 23))
Selected Partitions: 2
(5 rows)
当对分区键进行检索时，正常触发了分区剪枝，只查询了2个分区
tjyt=> explain select * from bmsql_stock where s_w_id=59.1 and s_i_id=23;
id | operation | E-rows | E-width | E-costs
----±----------------------------------------------±-------±--------±--------
1 | -> Streaming (type: GATHER) | 1 | 8 | 14.37
2 | -> Partition Iterator | 1 | 8 | 14.24
3 | -> Partitioned Seq Scan on bmsql_stock | 1 | 8 | 14.24
(3 rows)

     Predicate Information (identified by plan id)          

---

2 --Partition Iterator
Iterations: 3
3 --Partitioned Seq Scan on bmsql_stock
Filter: ((s_i_id = 23) AND ((s_w_id)::numeric = 59.1))
Selected Partitions: 1…3
(5 rows)
当分区键发生了格式转换后未能正常剪枝，查询了全部的分区
tjyt=> explain select * from bmsql_stock where s_w_id=‘59.1a’ and s_i_id=23;
id | operation | E-rows | E-width | E-costs
----±----------------------------------------------±-------±--------±--------
1 | -> Streaming (type: GATHER) | 1 | 8 | 1.33
2 | -> Partition Iterator | 1 | 8 | 1.21
3 | -> Partitioned Seq Scan on bmsql_stock | 1 | 8 | 1.21
(3 rows)

Predicate Information (identified by plan id)

2 --Partition Iterator
Iterations: 1
3 --Partitioned Seq Scan on bmsql_stock
Filter: ((s_w_id = 59) AND (s_i_id = 23))
Selected Partitions: 2
(5 rows)
当使用单引号，由于数据库会将该char中符合int的部分摘出，所以又正常剪枝了。
总结，对分区表进行检索是，如果分区条件只有部分分区满足就会进行剪枝，如果发生了格式转换就无法触发。