统计信息调优

统计信息调优介绍

openGauss是基于代价估算生成的最优执行计划。优化器需要根据analyze收集的统计信息行数估算和代价估算，因此统计信息对优化器行数估算和代价估算起着至关重要的作用。通过analyze收集全局统计信息，主要包括：pg_class表中的relpages和reltuples；pg_statistic表中的stadistinct、stanullfrac、stanumbersN、stavaluesN、histogram_bounds等。

实例分析1：未收集统计信息导致查询性能差

在很多场景下，由于查询中涉及到的表或列没有收集统计信息，会对查询性能有很大的影响。

表结构如下所示：

  1. CREATE TABLE LINEITEM
  2. (
  3. L_ORDERKEY         BIGINT        NOT NULL
  4. , L_PARTKEY        BIGINT        NOT NULL
  5. , L_SUPPKEY        BIGINT        NOT NULL
  6. , L_LINENUMBER     BIGINT        NOT NULL
  7. , L_QUANTITY       DECIMAL(15,2) NOT NULL
  8. , L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL
  9. , L_DISCOUNT       DECIMAL(15,2) NOT NULL
  10. , L_TAX            DECIMAL(15,2) NOT NULL
  11. , L_RETURNFLAG     CHAR(1)       NOT NULL
  12. , L_LINESTATUS     CHAR(1)       NOT NULL
  13. , L_SHIPDATE       DATE          NOT NULL
  14. , L_COMMITDATE     DATE          NOT NULL
  15. , L_RECEIPTDATE    DATE          NOT NULL
  16. , L_SHIPINSTRUCT   CHAR(25)      NOT NULL
  17. , L_SHIPMODE       CHAR(10)      NOT NULL
  18. , L_COMMENT        VARCHAR(44)   NOT NULL
  19. ) with (orientation = column, COMPRESSION = MIDDLE);
  21. CREATE TABLE ORDERS
  22. (
  23. O_ORDERKEY        BIGINT        NOT NULL
  24. , O_CUSTKEY       BIGINT        NOT NULL
  25. , O_ORDERSTATUS   CHAR(1)       NOT NULL
  26. , O_TOTALPRICE    DECIMAL(15,2) NOT NULL
  27. , O_ORDERDATE     DATE NOT NULL
  28. , O_ORDERPRIORITY CHAR(15)      NOT NULL
  29. , O_CLERK         CHAR(15)      NOT NULL
  30. , O_SHIPPRIORITY  BIGINT        NOT NULL
  31. , O_COMMENT       VARCHAR(79)   NOT NULL
  32. )with (orientation = column, COMPRESSION = MIDDLE);

查询语句如下所示：

  1. explain verbose select
  2. count(*) as numwait 
  3. from
  4. lineitem l1,
  5. orders 
  6. where
  7. o_orderkey = l1.l_orderkey
  8. and o_orderstatus = 'F'
  9. and l1.l_receiptdate > l1.l_commitdate
  10. and not exists (
  11. select
  12. *
  13. from
  14. lineitem l3
  15. where
  16. l3.l_orderkey = l1.l_orderkey
  17. and l3.l_suppkey <> l1.l_suppkey
  18. and l3.l_receiptdate > l3.l_commitdate
  19. )
  20. order by
  21. numwait desc;

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

  1. 通过explain verbose执行query分析执行计划时会提示WARNING信息，如下所示：

    1. 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.
    2. HINT:Do analyze for them in order to generate optimized plan.

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

    1. 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
    2. tem.l_suppkey, public.orders.o_orderstatus, public.orders.o_orderkey) are not collected.
    3. 2017-06-14 17:28:30.336 CST 140644024579856 20971684 [BACKEND] HINT:Do analyze for them in order to generate optimized plan.

当通过以上方法查看到哪些表或列没有做analyze，可以通过对WARNING或日志中上报的表或列做analyze可以解决由于为收集统计信息导致查询变慢的问题。