注册建设通网站首页,莱阳网站定制,seo站长工具,优化排名对网站不好概述 分区修剪(Partition Pruning)是分区表性能的查询优化技术 。在分区修剪中#xff0c;优化器分析SQL语句中的FROM和WHERE子句#xff0c;以在构建分区访问列表时消除不需要的分区。此功能使数据库只能在与SQL语句相关的分区上执行操作。 参数 enable_partition_pruning 设… 概述 分区修剪(Partition Pruning)是分区表性能的查询优化技术 。在分区修剪中优化器分析SQL语句中的FROM和WHERE子句以在构建分区访问列表时消除不需要的分区。此功能使数据库只能在与SQL语句相关的分区上执行操作。 参数 enable_partition_pruning 设置启用或禁用分区剪枝。 分区修剪的好处 分区修剪大大减少了从磁盘检索的数据量缩短了处理时间从而提高了查询性能并优化了资源利用率。 根据实际的SQL语句Kingbase数据库可使用静态或动态修剪。静态修剪发生在编译时预先访问有关分区的信息。动态修剪发生在运行时这意味着语句要访问的确切分区事先是未知的。静态修剪的示例场景是一个SQL语句该语句包含一个WHERE条件分区键列上有一个常量文本。动态修剪的一个例子是在WHERE条件中使用运算符或函数。 可用于分区修剪的信息 可以对分区列执行分区修剪。 当您在范围或列表分区列上使用range、LIKE、 和IN列表谓词时以及当您在哈希分区列中使用 或 IN列表谓词后Kingbase数据库将修剪分区。 对于多级分区对象Kingbase数据库可以使用相关谓词在每个级别上进行修剪。 Kingbase使用分区列上的谓词执行分区修剪如下所示 当使用范围分区时Kingbase只访问分区p2和p3表示2020年二月和三月的分区。当使用哈希子分区时Kingbase只访问每个分区中存储productid100行的一个子分区。子分区和谓词之间的映射是基于Kingbase的内部哈希分布函数计算的。 CREATE TABLE orders_range_hash
(productid int,saledate DATE,custid int,totalprice numeric
)PARTITION BY RANGE (saledate) SUBPARTITION BY HASH (productid) SUBPARTITIONS 8(PARTITION p1 VALUES LESS THAN(TO_DATE(2020-01-01, YYYY-MM-DD)),PARTITION p2 VALUES LESS THAN(TO_DATE(2022-02-01, YYYY-MM-DD)),PARTITION p3 VALUES LESS THAN(TO_DATE(2022-03-01, YYYY-MM-DD)),PARTITION p4 VALUES LESS THAN(TO_DATE(2022-04-01, YYYY-MM-DD)));SELECT *
FROM orders_range_hash
WHERE saledate BETWEEN (TO_DATE(2020-01-10, YYYY-MM-DD)) AND (TO_DATE(2020-02-11, YYYY-MM-DD))AND productid 100; 如何确定是否已使用分区修剪 不仅在给定查询的规划期间可以执行分区剪枝在其执行期间也能执行分区剪枝。 这非常有用因为如果子句中包含查询规划时值未知的表达式时这可以剪枝掉更多的分区 例如在PREPARE语句中定义的参数会使用从子查询拿到的值或者嵌套循环连接内侧关系上的参数化值。 执行期间的分区剪枝可能在下列任何时刻执行 在查询计划的初始化期间。对于执行的初始化阶段就已知值的参数可以在这里执行分区剪枝。这个阶段中被剪枝掉的分区将不会显示在查询的EXPLAIN或EXPLAIN ANALYZE结果中。通过观察EXPLAIN输出的“Subplans Removed”属性可以确定被剪枝掉的分区数。在查询计划的实际执行期间。这里可以使用只有在实际查询执行时才能知道的值执行分区剪枝。这包括来自子查询的值以及来自执行时参数的值(例如来自于参数化嵌套循环连接的参数)。由于在查询执行期间这些参数的值可能会改变多次所以只要分区剪枝使用到的执行参数发生改变就会执行一次分区剪枝。要判断分区是否在这个阶段被剪枝需要仔细地观察EXPLAIN ANALYZE输出中的loops属性。 对应于不同分区的子计划可以具有不同的值这取决于在执行期间每个分区被修剪的次数。 如果每次都被剪枝有些分区可能会显示为(never executed)。 静态分区修剪 根据静态谓词确定何时使用静态修剪。 在许多情况下优化器确定编译时要访问的分区。如果使用静态谓词则会发生静态分区修剪。 如果在解析时优化器可以识别访问的连续分区集则执行计划中将显示正在访问的分区的条件范围。 CREATE TABLE orders_list
(productid int,saledate DATE,custid int,totalprice numeric
)
PARTITION BY LIST (custid)(PARTITION p1 VALUES (1,2),PARTITION p2 VALUES (3,4),PARTITION p2 VALUES (5,6));explain analyzed
select * from orders_list
where custid 3;Seq Scan on orders_list_p2 (cost0.00..23.38 rows5 width48) (actual time0.016..0.020 rows17 loops1)Filter: (custid 3)Rows Removed by Filter: 17
Planning Time: 0.107 ms
Execution Time: 0.037 ms 动态分区修剪 如果可以修剪但无法进行静态修剪则进行动态修剪因为分区键值仅在执行时获知。 使用绑定变量进行动态修剪 对分区列使用绑定变量的语句会导致动态修剪。 \set vid 4explain select * from orders_list where custid :vid;QUERY PLAN
----------------------------------------------------------------Seq Scan on orders_list_p2 (cost0.00..23.38 rows5 width48)Filter: (custid 4)
(2 行记录)do$$declarec1 text;beginfor c1 in execute explain select * from orders_list where custid :vid using (random() * 10)::int % 6 1loopraise info %,c1;end loop;end;$$;信息: Seq Scan on orders_list_p1 (cost0.00..23.38 rows5 width48)
信息: Filter: (custid 2)
ANONYMOUS BLOCK 使用子查询进行动态修剪 对分区列显式使用子查询的语句会导致动态修剪。 分区节点的(never executed)表示执行了分区修剪。如果过滤条件使用IN子查询则不能分区修剪。 explain (costs off,analyze)
with v as (select (random() * 10)::int % 2 1 id)
select *
from orders_list
where custid (select v.id from v);QUERY PLAN
-----------------------------------------------------------------------------Append (actual time0.028..0.033 rows17 loops1)CTE v- Result (actual time0.004..0.004 rows1 loops1)InitPlan 2 (returns $1)- CTE Scan on v (actual time0.007..0.008 rows1 loops1)- Seq Scan on orders_list_p1 (actual time0.015..0.018 rows17 loops1)Filter: (custid $1)Rows Removed by Filter: 16- Seq Scan on orders_list_p2 (never executed)Filter: (custid $1)- Seq Scan on orders_list_p3 (never executed)Filter: (custid $1)Planning Time: 0.172 msExecution Time: 0.069 ms
(14 行记录)explain (costs off,analyze)
with v as (select (random() * 10)::int % 2 1 id)
select *
from orders_list
where custid in (select v.id from v);QUERY PLAN
-----------------------------------------------------------------------------------Hash Semi Join (actual time0.046..0.067 rows16 loops1)Hash Cond: (orders_list_p1.custid v.id)CTE v- Result (actual time0.005..0.005 rows1 loops1)- Append (actual time0.009..0.023 rows100 loops1)- Seq Scan on orders_list_p1 (actual time0.008..0.010 rows33 loops1)- Seq Scan on orders_list_p2 (actual time0.003..0.004 rows34 loops1)- Seq Scan on orders_list_p3 (actual time0.002..0.004 rows33 loops1)- Hash (actual time0.012..0.012 rows1 loops1)Buckets: 1024 Batches: 1 Memory Usage: 9kB- CTE Scan on v (actual time0.008..0.008 rows1 loops1)Planning Time: 0.303 msExecution Time: 0.095 ms
(13 行记录) 具有关联需求的动态修剪 等于()谓词限制子查询的结果只能有一行。IN 、EXISTS、ANY 等方式使用子查询时不能执行动态修剪。多表连接也不能执行动态修剪。 这种不能使用动态修剪的情况可以使用LATERAL语法解决。 LATERAL子查询不能是简单子查询。 explain (costs off ,analyze)
with t (id) as (values (1), (2))
select *
from t, lateral ( select * from orders_list t1 where t1.custid t.id limit all) t1;QUERY PLAN
--------------------------------------------------------------------------------------Nested Loop (actual time0.022..0.037 rows33 loops1)- Values Scan on *VALUES* (actual time0.002..0.003 rows2 loops1)- Append (actual time0.009..0.013 rows16 loops2)- Seq Scan on orders_list_p1 t1 (actual time0.007..0.009 rows16 loops2)Filter: (custid *VALUES*.column1)Rows Removed by Filter: 16- Seq Scan on orders_list_p2 t1_1 (never executed)Filter: (custid *VALUES*.column1)- Seq Scan on orders_list_p3 t1_2 (never executed)Filter: (custid *VALUES*.column1)Planning Time: 0.189 msExecution Time: 0.072 ms 分区修剪提示 使用分区修剪时应考虑以下事项 数据类型转换 若要从分区修剪中获得最大的性能优势应避免使用需要数据库转换指定数据类型的构造。 函数调用 避免在分区列上使用隐式或显式函数。如果您的查询通常使用函数调用请考虑在这些情况下使用虚拟列和虚拟列分区以从分区修剪中受益。
文章转载自： http://www.morning.wgxtz.cn.gov.cn.wgxtz.cn http://www.morning.rdymd.cn.gov.cn.rdymd.cn http://www.morning.jzfxk.cn.gov.cn.jzfxk.cn http://www.morning.lmrjn.cn.gov.cn.lmrjn.cn http://www.morning.zrpys.cn.gov.cn.zrpys.cn http://www.morning.cpwmj.cn.gov.cn.cpwmj.cn http://www.morning.zlchy.cn.gov.cn.zlchy.cn http://www.morning.pmmrb.cn.gov.cn.pmmrb.cn http://www.morning.ljglc.cn.gov.cn.ljglc.cn http://www.morning.nmhpq.cn.gov.cn.nmhpq.cn http://www.morning.prgyd.cn.gov.cn.prgyd.cn http://www.morning.jrhcp.cn.gov.cn.jrhcp.cn http://www.morning.yrqb.cn.gov.cn.yrqb.cn http://www.morning.nhdmh.cn.gov.cn.nhdmh.cn http://www.morning.mrnnb.cn.gov.cn.mrnnb.cn http://www.morning.rtlg.cn.gov.cn.rtlg.cn http://www.morning.trqzk.cn.gov.cn.trqzk.cn http://www.morning.cyjjp.cn.gov.cn.cyjjp.cn http://www.morning.krgjc.cn.gov.cn.krgjc.cn http://www.morning.crrmg.cn.gov.cn.crrmg.cn http://www.morning.ybyln.cn.gov.cn.ybyln.cn http://www.morning.fjntg.cn.gov.cn.fjntg.cn http://www.morning.yrsg.cn.gov.cn.yrsg.cn http://www.morning.knrgb.cn.gov.cn.knrgb.cn http://www.morning.ltpzr.cn.gov.cn.ltpzr.cn http://www.morning.qggm.cn.gov.cn.qggm.cn http://www.morning.hwbf.cn.gov.cn.hwbf.cn http://www.morning.mehrim.com.gov.cn.mehrim.com http://www.morning.ysnbq.cn.gov.cn.ysnbq.cn http://www.morning.qsfys.cn.gov.cn.qsfys.cn http://www.morning.srmpc.cn.gov.cn.srmpc.cn http://www.morning.dzgmj.cn.gov.cn.dzgmj.cn http://www.morning.hnrpk.cn.gov.cn.hnrpk.cn http://www.morning.dskmq.cn.gov.cn.dskmq.cn http://www.morning.ggqcg.cn.gov.cn.ggqcg.cn http://www.morning.trsdm.cn.gov.cn.trsdm.cn http://www.morning.nzlsm.cn.gov.cn.nzlsm.cn http://www.morning.jgncd.cn.gov.cn.jgncd.cn http://www.morning.nshhf.cn.gov.cn.nshhf.cn http://www.morning.pjwfs.cn.gov.cn.pjwfs.cn http://www.morning.qxnlc.cn.gov.cn.qxnlc.cn http://www.morning.mswkd.cn.gov.cn.mswkd.cn http://www.morning.mrbzq.cn.gov.cn.mrbzq.cn http://www.morning.rlzxr.cn.gov.cn.rlzxr.cn http://www.morning.pjxlg.cn.gov.cn.pjxlg.cn http://www.morning.fywqr.cn.gov.cn.fywqr.cn http://www.morning.mcbqq.cn.gov.cn.mcbqq.cn http://www.morning.rkrcd.cn.gov.cn.rkrcd.cn http://www.morning.qqbjt.cn.gov.cn.qqbjt.cn http://www.morning.xwqxz.cn.gov.cn.xwqxz.cn http://www.morning.grwgw.cn.gov.cn.grwgw.cn http://www.morning.bpttm.cn.gov.cn.bpttm.cn http://www.morning.gbrdx.cn.gov.cn.gbrdx.cn http://www.morning.rjyd.cn.gov.cn.rjyd.cn http://www.morning.skksz.cn.gov.cn.skksz.cn http://www.morning.ho-use.cn.gov.cn.ho-use.cn http://www.morning.llxyf.cn.gov.cn.llxyf.cn http://www.morning.ccyjt.cn.gov.cn.ccyjt.cn http://www.morning.smdkk.cn.gov.cn.smdkk.cn http://www.morning.pjxw.cn.gov.cn.pjxw.cn http://www.morning.kzbpx.cn.gov.cn.kzbpx.cn http://www.morning.hhmfp.cn.gov.cn.hhmfp.cn http://www.morning.lnrr.cn.gov.cn.lnrr.cn http://www.morning.jtqxs.cn.gov.cn.jtqxs.cn http://www.morning.ryjl.cn.gov.cn.ryjl.cn http://www.morning.hnrls.cn.gov.cn.hnrls.cn http://www.morning.pbgnx.cn.gov.cn.pbgnx.cn http://www.morning.nlkm.cn.gov.cn.nlkm.cn http://www.morning.xhftj.cn.gov.cn.xhftj.cn http://www.morning.nwllb.cn.gov.cn.nwllb.cn http://www.morning.hrkth.cn.gov.cn.hrkth.cn http://www.morning.bsghk.cn.gov.cn.bsghk.cn http://www.morning.fqhbt.cn.gov.cn.fqhbt.cn http://www.morning.gzgwn.cn.gov.cn.gzgwn.cn http://www.morning.hgscb.cn.gov.cn.hgscb.cn http://www.morning.pznnt.cn.gov.cn.pznnt.cn http://www.morning.mhdwp.cn.gov.cn.mhdwp.cn http://www.morning.bdgb.cn.gov.cn.bdgb.cn http://www.morning.djxnn.cn.gov.cn.djxnn.cn http://www.morning.bxgpy.cn.gov.cn.bxgpy.cn
