postgresql – 对大表的第一次查询调用非常慢

发布时间：2020-12-13 15:58:41 所属栏目：百科来源：网络整理

导读：我有一个问题,感觉它需要花费更多的时间.这仅适用于给定参数集的第一个查询,因此在缓存时没有问题. 我不确定会发生什么,但是,考虑到设置和设置,我希望有人可以对几个问题有所了解,并提供一些有关如何加快查询速度的见解.该表格相当大,Postgres估计其中大约15

我有一个问题,感觉它需要花费更多的时间.这仅适用于给定参数集的第一个查询,因此在缓存时没有问题.

我不确定会发生什么,但是,考虑到设置和设置,我希望有人可以对几个问题有所了解,并提供一些有关如何加快查询速度的见解.该表格相当大,Postgres估计其中大约155963000(14 GB).

询问

select ts,sum(amp) as total_amp,sum(230 * factor) as wh
    from data_cbm_aggregation_15_min
    where virtual_id in (1818) and ts between '2015-02-01 00:00:00' and '2015-03-31 23:59:59'
    and deleted is null
    group by ts
    order by ts

当我开始研究这个查询时花了大约15秒,经过一些更改后我已经达到了大约10秒,这对于像这样的简单查询似乎仍然很长.以下是explain analyze：http://explain.depesz.com/s/97V1的结果.请注意GroupAggregate返回相同行数的原因是此示例只使用了一个virtual_id,但可能会有更多.

表和索引

正在查询的表,它每15分钟插入一次值

CREATE TABLE data_cbm_aggregation_15_min (
  virtual_id integer NOT NULL,ts timestamp without time zone NOT NULL,amp real,recs smallint,min_amp real,max_amp real,deleted boolean,factor real DEFAULT 0.25,min_amp_ts timestamp without time zone,max_amp_ts timestamp without time zone
)

ALTER TABLE data_cbm_aggregation_15_min ALTER COLUMN virtual_id SET STATISTICS 1000;
ALTER TABLE data_cbm_aggregation_15_min ALTER COLUMN ts SET STATISTICS 1000;

查询中使用的索引

CREATE UNIQUE INDEX idx_data_cbm_aggregation_15_min_virtual_id_ts
ON data_cbm_aggregation_15_min USING btree (virtual_id,ts DESC);

ALTER TABLE data_cbm_aggregation_15_min
CLUSTER ON idx_data_cbm_aggregation_15_min_virtual_id_ts;

Postgres设置

其他设置是默认设置.

default_statistics_target = 100 
maintenance_work_mem = 2GB 
effective_cache_size = 11GB
work_mem = 256MB
shared_buffers = 3840MB
random_page_cost = 1

我试过了什么

在https://wiki.postgresql.org/wiki/Slow_Query_Questions发布之前,我一直在关注这些事情,结果更详细如下：

>摆弄Postgres设置,主要是自索引扫描以来降低random_page_cost,虽然看起来不太特别,但是当random_page_cost较高时,它尝试做的位图堆扫描提前了几英里.
>将增加的统计信息添加到索引和WHERE条件所基于的virtual_id和ts列.更改后,查询计划程序的估计行数更接近实际行数.
>对idx_data_cbm_aggregation_15_min_virtual_id_ts索引的聚类似乎没有太大变化,而不是我注意到的.
>手动运行VACUUM没有太大变化,我已经运行autovacuum所以这并不奇怪.
>在索引上运行REINDEX大大缩减了(差不多50％！)但它没有提高速度.

解决方法

一些小的改进

SELECT ts,sum(amp) AS total_amp,sum(factor) * 230  AS wh
FROM   data_cbm_aggregation_15_min
WHERE  virtual_id = 1818
AND    ts >= '2015-02-01 00:00'
AND    ts <  '2015-04-01 00:00'
AND    deleted IS NULL
GROUP  BY ts
ORDER  BY ts;

> sum(230 * factor) – 将总和乘以一次而不是乘以每个元素会更便宜：sum(factor)* 230结果相同,即使是NULL值也是如此.
‘2015-02-01 00:00:00’和’2015-03-31 23:59:59’之间的> ts可能不正确.要包括2015年3月的所有内容,请使用提供的替代方案.无论如何,BETWEEN被翻译为ts> = lower AND ts< = upper.拼写它总是稍微快一些.
>(1818)中的virtual_id只是一种不必要的复杂方式来说virtual_id = 1818.

更好的指数,可能更大的改善

CREATE INDEX data_cbm_aggregation_15_min_special_idx
ON data_cbm_aggregation_15_min (virtual_id,ts,amp,factor)
WHERE deleted IS NULL;

>我在你的问题中没有看到任何暗示你原始索引中的DESC的内容.虽然Index Scan Backward几乎与普通的Index Scan一样快,但删除修改器仍然更好.
>最重要的是,自Postgres 9.2以来有index-only scans个.附加的两个索引列(amp,factor)只有在从中获取仅索引扫描时才有意义.
>由于您显然对已删除的行不感兴趣,因此请将其设为部分索引.只有在表格中有多个已删除的行时才需要付费.
如果您可以排除表的其他大部分,请添加更多条件 – 并记住在查询中重复条件(即使它看起来多余),因此Postgres了解索引是适用的.

表定义

像这样重新排序表列将每行节省8个字节：

CREATE TABLE data_cbm_aggregation_15_min (
   virtual_id integer NOT NULL,ts timestamp NOT NULL,min_amp_ts timestamp,max_amp_ts timestamp
);

有关：

> Configuring PostgreSQL for read performance

最重要的信息

>对于非常大的表,第一次查询调用可能实质上更昂贵,因为整个表不能被缓存.后续调用从填充的缓存中获利. Postgres缓存块,不一定是整个表.
>对于第一次通话,还有一件事可能很重要.由于Postgres的MVCC模型,它必须保持可见性信息.当自上次写入操作以来第一次读取表的页面时,Postgres会机会性地更新可见性信息,这可能会为第一次访问带来一些额外的成本(并为后续调用提供大量帮助). More in the manual here.关于dba.SE的相关答案：

> Why does a SELECT statement dirty cache buffers in Postgres?

关于你到目前为止所尝试的内容

> ts和virtual_id的SET STATISTICS 1000是一个很好的想法,但是通过设置random_page_cost = 1,效果基本上无效,这基本上强制对该查询进行索引扫描.
> random_page_cost = 1告诉Postgres随机访问和顺序访问一样便宜.这对于(几乎)完全驻留在缓存中的DB是有意义的.对于像你这样有大表的数据库,这个设置似乎太极端了(即使它让Postgres支持所需的索引扫描).将其设置为random_page_cost = 1.1或更高.
>对于您呈现的查询的第一次调用,位图索引扫描通常是一个很好的计划 – 用于在表中随机分布的数据.由于您就像对此查询所需的那样对表进行聚类,因此索引扫描更有效.问题是：你的桌子会保持聚集状态吗？
>您对work_mem和其他资源的设置取决于您拥有多少RAM,磁盘速度,访问模式,您通常拥有的并发连接数,服务器上的其他程序竞争资源等等.work_mem = 256MB似乎太高.对于呈现的查询,您不需要那么多.将其设置为高可能实际上会损害性能,因为它减少了可用于缓存的RAM.
>在CLUSTER之后,REINDEX不会立即冗余,因为无论如何都会重新创建所有索引.您必须在群集之前运行REINDEX,否则您在表上拥有大量写入权限,以便再次获得如此多的膨胀.

各个

>升级到Postgres 9.4(或即将推出的9.5,目前为alpha).版本9.2现在已经有3年了,最新版本已经获得了很多改进.
> query plan表明实际上没有任何汇总.从索引中读取rows = 4,117,在GroupAggregate之后保留rows = 4,117.看起来行已经在ts上独一无二了？然后你可以完全删除聚合并使它成为一个简单的SELECT …
>如果这只是一个误导性的EXPLAIN输出,并且您通常输出的行数比读取的少得多,那么带有ts索引的MATERIALIZED VIEW将是另一种选择.特别是与引入REFRESH MATERIALIZED VIEW CONCURRENTLY的Postgres 9.4结合使用.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!