PG中的MCV统计信息

0    714    1

Tags:

👉 本文共约2918个字,系统预计阅读时间或需11分钟。

MCV

在pg12中可以建立mcv统计信息

MCV(Multi-Column-Values)用于追踪出现频率最高的值的组合。

相比函数依赖,MCV的两个优势:

第一:mcv存储了实际值,因此,可以更为精确的判断where条件的组合的预计返回记录数。

第二:mcv可以处理非等值的where条件。

参考:https://www.enterprisedb.com/edb-docs/d/postgresql/reference/manual/12.4/multivariate-statistics-examples.html

建立mcv和不建立mcv情况下的等值查询

如下是在testdb中,不建立mcv的情况:

可以看到在不建立mcv时,优化器预估rows=1,在建立mcv时,优化器预估rows=100,其实,符合该条件的记录是100条。

从上面的分析可以看出,建立mcv有助于优化器更加精确的评估SQL的成本。

建立mcv和不建立mcv情况下的不等值查询

首先来看建立mcv情况下的不等值查询:

再来看不建立mcv情况下的不等值查询:

可以看出来,从预计rows和Execution Time角度看,都是建立mcv要比不建立mcv要好。

CREATE、DROP和ALTER STATISTICS

CREATE STATISTICS

CREATE STATISTICS — 定义扩展统计

大纲

描述

CREATE STATISTICS将创建一个新的扩展统计对象, 追踪指定表、外部表或物化视图的数据。该统计对象将在当前数据库中创建, 被发出该命令的用户所有。

如果给定了模式名(比如,CREATE STATISTICS myschema.mystat ...), 那么在给定的模式中创建统计对象。否则在当前模式中创建。 统计对象的名称必须与相同模式中的任何其他统计对象不同。

参数

  • IF NOT EXISTS

    如果具有相同名称的统计对象已经存在,不会抛出一个错误,只会发出一个提示。 请注意,这里只考虑统计对象的名称,不考虑其定义细节。

  • statistics_name

    要创建的统计对象的名称(可以有模式限定)。

  • statistics_kind

    在此统计对象中计算的统计种类。目前支持的种类是启用n-distinct统计的 ndistinct,启用功能依赖性统计的dependencies,以及启用最常见的值列表的mcv。 如果省略该子句,则统计对象中将包含所有支持的统计类型。 有关更多信息,请参阅第 14.2.2 节第 70.2 节

  • column_name

    被计算的统计信息包含的表格列的名称。至少必须给出两个列名,列名的顺序可以忽略。

  • table_name

    包含计算统计信息的列的表的名称(可以是模式限定的)。

注意

你必须是表的所有者才能创建读取它的统计对象。不过,一旦创建, 统计对象的所有权与基础表无关。

示例

用两个功能相关的列创建表t1, 即第一列中的值的信息足以确定另一列中的值。然后, 在这些列上构建函数依赖关系统计信息:

如果没有函数依赖性统计,规划器会认为两个WHERE条件是独立的, 并且会将它们的选择性乘以一起,以致得到太小的行数估计。 通过这样的统计,规划器认识到WHERE条件是多余的,并且不会低估行数。

创建表t2与两个完全相关的列(包含相同的数据),并且在这些列上创建一个MCV列表:

MCV列表为计划器提供了关于表中普遍出现的特定值的更详细的信息,以及表中未显示的值组合的选择性上限,允许它在这两种情况下产生更好的估计值。

兼容性

SQL标准中没有CREATE STATISTICS命令。

ALTER STATISTICS

ALTER STATISTICS — 更改扩展统计对象的定义

大纲

描述

ALTER STATISTICS更改现有扩展统计对象的参数。 任何在ALTER STATISTICS命令中没有明确设定的参数保持它们之前的设置。

您必须拥有统计对象才能使用ALTER STATISTICS。 要更改统计对象的模式,还必须在新模式上具有CREATE权限。 要更改所有者,还必须是新所有者角色的直接或间接成员, 且该角色在统计对象的模式上必须具有CREATE权限。 (这些限制强制了通过删除和重新创建统计对象来改变所有者不会做任何你不能做的事情, 但是超级用户可以改变任何统计对象的所有权。)

参数

  • name

    要修改的统计对象的名称(可能有模式修饰)。

  • new_owner

    统计对象的新所有者的用户名。

  • new_name

    统计对象的新名称。

  • new_schema

    统计对象的新模式。

  • new_target

    此统计信息对象的统计信息收集目标,用于随后的 ANALYZE 操作。 可以在0到10000范围内设置目标。 或者,将其设置为-1以恢复为使用所引用列的统计目标的 最大值(如果已设置)或系统默认统计目标 (default_statistics_target)。 有关 PostgreSQL 查询计划使用统计信息的更多信息,请参考 第 14.2 节.

兼容性

SQL标准中没有ALTER STATISTICS命令。

DROP STATISTICS

DROP STATISTICS — 删除扩展统计

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

大纲

描述

DROP STATISTICS删除数据库中的统计对象。 只有统计对象的所有者、模式的所有者或超级用户可以删除统计对象。

参数

  • IF EXISTS

  • name

    要删除的统计对象的名称(可以有模式修饰)。

示例

删除不同模式中的两个统计对象,如果不存在时不会失败:

兼容性

SQL标准中没有DROP STATISTICS命令。

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

3 × 1 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部