pg_qualstats-1.0.x ‘s suggestions are not accurate, So use pg_qualstats-2.0.x

最新推荐文章于 2024-02-04 22:04:59 发布

江天水一泓

最新推荐文章于 2024-02-04 22:04:59 发布

阅读量172

点赞数

分类专栏： postgresql 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/international24/article/details/109614854

版权

postgresql 专栏收录该内容

25 篇文章 5 订阅

订阅专栏

pg_qualstats-1.0.x 's suggestions are not accurate, So use pg_qualstats-2.0.x

pg_qualstats is a PostgreSQL extension keeping statistics on predicates found in WHERE statements and JOIN clauses.

pg_qualstats is able to analyze what are the most-often executed quals (predicates) on your database. The powa project makes use of this to provide advances index suggestions.

It also allows you to identify correlated columns, by identifying which columns are most frequently queried together.

I installed pg_qualstats-1.0.9 (the latest version of 1.0.x) on one of my PostgreSQL platform. After the PostgreSQL has been running for several hours, it gave me some suggestions in pg_qualstats_indexes_ddl. Here are some of them :

  nspname |   nspname |      attnames        | idxtype | execution_count |                           ddl
---------------|----------------|-------------------------|----------|------------------------|----------------------------------------------------------------------------------
   public     |  tb_device  | {dev_addr,status} | btree |           949       | CREATE INDEX idx_tb_device_dev_addr_status ON public.tb_device USING btree (dev_addr, status)
   public     |  tb_device  | {dev_addr,status} | hash   |           949       | CREATE INDEX idx_tb_device_dev_addr_status ON public.tb_device USING hash (dev_addr, status)
   public     |  tb_device  | {dev_addr,status} | spgist |           949       | CREATE INDEX idx_tb_device_dev_addr_status ON public.tb_device USING spgist (dev_addr, status)
   public     |  tb_device  |        {status}          |   brin   |           184       | CREATE INDEX idx_tb_device_status ON public.tb_device USING brin (status)
   public     |  tb_device  |        {status}          |  btree |           184       | CREATE INDEX idx_tb_device_status ON public.tb_device USING btree (status)
   public     |  tb_device  |        {status}          |  hash |           184       | CREATE INDEX idx_tb_device_status ON public.tb_device USING hash (status)

As we can see, these suggestions are not available.
1. It suggested me creating several different kinds of indexes on the same group of columns.
2. It suggested me creating index on low-cardinality columns, for example, tb_device.status .
3. It suggested me to create a composite spgist index on (dev_addr, status), while "spgist" does not support multicolumn indexes.

So, pg_qualstats-1.0.9 is not useful. But after I uninstalled pg_qualstats-1.0.9 and installed pg_qualstats-2.0.2, these bugs haven't appear.
pg_qualstats-2.0.2 offers me available suggestions. So, use pg_qualstats-2.0.2 or later version.

pg_qualstats PostgreSQL 扩展，保持 WHERE 语句和 JOIN 子句中谓词的统计信息。

它能够分析数据库上执行最多的 quals（谓词）是什么。powa 项目利用它提供索引建议。

我在 PostgreSQL pg_qualstats-1.0.9（1.0.x 的最新版本）上安装了 pg_qualstats-1.0.9（PostgreSQL 运行了几个小时后，它给了我一些建议，pg_qualstats_indexes_ddl。下面是其中一些：

正如我们所看到的，这些建议没有什么价值。
1. 它建议我在同一组列上创建几种不同类型的索引。
2. 它建议我在低基数列上创建索引，例如 tb_device.status。
3. 建议我在（dev_addr 状态）上创建复合 spgist 索引，而 "spgist" 不支持多列索引。

因此，pg_qualstats-1.0.9 没有用。但是，在卸载 pg_qualstats-1.0.9 并安装了 pg_qualstats-2.0.2 后，这些错误没有出现。
pg_qualstats-2.0.2 给我提供了可用的建议。因此，使用 pg_qualstats-2.0.2 或更新版本。