我不确定会发生什么,但是,考虑到设置和设置,我希望有人可以对几个问题有所了解,并提供一些有关如何加快查询速度的见解.该表格相当大,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@H_502_16@
当我开始研究这个查询时花了大约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;@H_502_16@
查询中使用的索引
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;@H_502_16@
Postgres设置
其他设置是默认设置.
default_statistics_target = 100
maintenance_work_mem = 2GB
effective_cache_size = 11GB
work_mem = 256MB
shared_buffers = 3840MB
random_page_cost = 1@H_502_16@
我试过了什么
在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;@H_502_16@> 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;@H_502_16@>我在你的问题中没有看到任何暗示你原始索引中的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 );@H_502_16@有关：
> 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结合使用.
