统计信息调优

CREATE TABLE LINEITEM

(L_ORDERKEY BIGINT NOT NULL , L_PARTKEY BIGINT NOT NULL , L_SUPPKEY BIGINT NOT NULL3 , L_LINENUMBER BIGINT NOT NULL , L_QUANTITY DECIMAL(15,2) NOT NULL , L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL , L_DISCOUNT DECIMAL(15,2) NOT NULL

) with (orientation = column, COMPRESSION = MIDDLE) distribute by hash(L_ORDERKEY);

CREATE TABLE ORDERS

(O_ORDERKEY BIGINT NOT NULL , O_CUSTKEY BIGINT NOT NULL , O_ORDERSTATUS CHAR(1) NOT NULL , O_TOTALPRICE DECIMAL(15,2) NOT NULL , O_ORDERDATE DATE NOT NULL , O_ORDERPRIORITY CHAR(15) NOT NULL , O_CLERK CHAR(15) NOT NULL , O_SHIPPRIORITY BIGINT NOT NULL , O_COMMENT VARCHAR(79) NOT NULL

)with (orientation = column, COMPRESSION = MIDDLE) distribute by hash(O_ORDERKEY);

查询语句如下所示：

explain verbose select count(*) as numwait fromlineitem l1,

orders where

o_orderkey = l1.l_orderkey and o_orderstatus = 'F'

and l1.l_receiptdate > l1.l_commitdate and not exists (

select

*from

lineitem l3 where

l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate )order by

numwait desc;

当出现该问题时，可以通过如下方法确认查询中涉及到的表或列有没有做过analyze收 集统计信息。

1. 通过explain verbose执行query分析执行计划时会提示WARNING信息，如下所 示：WARNING:Statistics in some tables or columns(public.lineitem.l_receiptdate,

public.lineitem.l_commitdate, public.lineitem.l_orderkey, public.lineitem.l_suppkey, public.orders.o_orderstatus, public.orders.o_orderkey) are not collected.

HINT:Do analyze for them in order to generate optimized plan.

2. 可以通过在pg_log目录下的日志文件中查找以下信息来确认是当前执行的query是 否由于没有收集统计信息导致查询性能变差。

2017-06-14 17:28:30.336 CST 140644024579856 20971684 [BACKEND] LOG:Statistics in some tables or columns(public.lineitem.l_receiptdate, public.lineitem.l_commitdate, public.lineitem.l_orderkey, public.linei

tem.l_suppkey, public.orders.o_orderstatus, public.orders.o_orderkey) are not collected.

2017-06-14 17:28:30.336 CST 140644024579856 20971684 [BACKEND] HINT:Do analyze for them in order to generate optimized plan.

当通过以上方法查看到哪些表或列没有做analyze，可以通过对WARNING或日志中上

FROMb_zyk_wbswxx A, b_zyk_wbswxx B, b_zyk_wbcs C WHERE

A.ZJHM = '522522******3824' AND A.WBDM = B.WBDM AND A.WBDM = C.WBDM

AND abs(to_date(A.SWKSSJ,'yyyymmddHH24MISS') - to_date(B.SWKSSJ,'yyyymmddHH24MISS')) <

INTERVAL '15 MINUTES' ORDER BY

B.SWKSSJ, B.ZJHM limit 10 offset 0

;

执行计划如图3-2所示。该查询实际耗时约12秒。

图3-2 应用 unlogged table 案例（一）

优化分析：分析过程如下：

1. 分析该执行计划发现，扫描节点已使用Index Scan，耗时主要在最外层Nest Loop Join的Join Filter计算中，且该计算执行了字符串的加减法和不等值比较。

2. 考虑使用unlogged table保存目标人的上网信息，且在插入时处理上网开始时间 和终止时间，以避免后续进行时间加减。

//创建临时unlogged table

CREATE UNLOGGED TABLE temp_tsw (ZJHM NVARCHAR2(18), WBDM NVARCHAR2(14), SWKSSJ_START NVARCHAR2(14), SWKSSJ_END NVARCHAR2(14), WBM NVARCHAR2(70), DZQH NVARCHAR2(6), DZ NVARCHAR2(70), IPDZ NVARCHAR2(39) );

//插入目标人的上网记录，并处理上网开始和结束时间。

INSERT INTO temp_tsw SELECT A.ZJHM, A.WBDM,

to_char((to_date(A.SWKSSJ,'yyyymmddHH24MISS') - INTERVAL '15 MINUTES'),'yyyymmddHH24MISS'),

to_char((to_date(A.SWKSSJ,'yyyymmddHH24MISS') + INTERVAL '15 MINUTES'),'yyyymmddHH24MISS'),

B.WBM,B.DZQH,B.DZ,B.IPDZ FROMb_zyk_wbswxx A,

b_zyk_wbcs B WHERE

A.ZJHM='522522******3824' AND A.WBDM = B.WBDM

;

//查询和目标人在前后十五分钟内在同一网吧上网的人员信息，比较大小时强制转换为int8。

SELECT A.WBM, A.DZQH, A.DZ, A.IPDZ,

B.ZJHM, B.XM,

to_date(B.SWKSSJ,'yyyymmddHH24MISS') as SWKSSJ, to_date(B.XWSJ,'yyyymmddHH24MISS') as XWSJ, B.SWZDH

FROM temp_tsw A, b_zyk_wbswxx B WHERE

A.ZJHM <> B.ZJHM AND A.WBDM = B.WBDM

AND (B.SWKSSJ)::int8 > (A.swkssj_start)::int8 AND (B.SWKSSJ)::int8 < (A.swkssj_end)::int8 order by

B.SWKSSJ, B.ZJHM limit 10 offset 0

;

上述查询耗时约7秒，执行计划如图3-3所示。

图3-3 应用 unlogged table 案例（二）

3. 分析上述执行计划，发现执行了Hash Join，对大表b_zyk_wbswxx建立了Hash Table。由于该表数据量大，创建过程耗时较长。

由于temp_tsw中仅包含几百条记录，且temp_tsw和b_zyk_wbswxx均通过wbdm

（网吧代码）执行等值连接。因此，如果Join方式改为Nest Loop Join，则扫描节 点可以实现Index Scan，性能预计将会提升。

4. 执行如下语句，将Join方式改为Nest Loop Join。

SET enable_hashjoin = off;

执行计划如图3-4所示。查询耗时约3秒。

图3-4 应用 unlogged table 案例（三）

5. 使用unlogged table保存结果集并用于分页显示。

如果需要在上层应用页面实现分页显示，需要修改offset值确定显示目标页的结果 集。按此实现，每次翻页时均执行上面查询语句，耗时较长。

为解决上述问题，建议使用unlogged table保存结果集。

//创建保存结果集的unlogged table CREATE UNLOGGED TABLE temp_result (WBM NVARCHAR2(70),

SWZDH NVARCHAR2(32) );

to_date(B.SWKSSJ,'yyyymmddHH24MISS') as SWKSSJ, to_date(B.XWSJ,'yyyymmddHH24MISS') as XWSJ, B.SWZDH

FROM temp_tsw A, b_zyk_wbswxx B WHERE

A.ZJHM <> B.ZJHM AND A.WBDM = B.WBDM

AND (B.SWKSSJ)::int8 > (A.swkssj_start)::int8 AND (B.SWKSSJ)::int8 < (A.swkssj_end)::int8

;

ZJHMLIMIT 10 OFFSET 0;

注意