postgresql - 如何提高大型表上基于日期的查询性能?
问题描述
这与我发布的其他 2 个问题有关(听起来我应该将此作为新问题发布) - 反馈有所帮助,但我认为下次我需要插入数据时同样的问题会再次出现。事情进展缓慢,这迫使我暂时删除一些较旧的数据,以便我正在查询的表中只剩下 2 个月的价值。
这次提供更多细节 - 希望它有助于查明问题:
- PG 10.7 版(在 heroku 上运行
- 总 DB 大小:18.4GB(这包含 2 个月的数据,并且每个月都会以大致相同的速度增长)
- 15GB 内存
- 总可用存储空间:512GB
- 最大的表(执行最慢查询的表)为 9.6GB(它是整个数据库中最大的一块)——大约 1000 万条记录
最大表的架构:
-- Table Definition ----------------------------------------------
CREATE TABLE reportimpression (
datelocal timestamp without time zone,
devicename text,
network text,
sitecode text,
advertisername text,
mediafilename text,
gender text,
agegroup text,
views integer,
impressions integer,
dwelltime numeric
);
-- Indices -------------------------------------------------------
CREATE INDEX reportimpression_feb2019_index ON reportimpression(datelocal timestamp_ops) WHERE datelocal >= '2019-02-01 00:00:00'::timestamp without time zone AND datelocal < '2019-03-01 00:00:00'::timestamp without time zone;
CREATE INDEX reportimpression_mar2019_index ON reportimpression(datelocal timestamp_ops) WHERE datelocal >= '2019-03-01 00:00:00'::timestamp without time zone AND datelocal < '2019-04-01 00:00:00'::timestamp without time zone;
CREATE INDEX reportimpression_jan2019_index ON reportimpression(datelocal timestamp_ops) WHERE datelocal >= '2019-01-01 00:00:00'::timestamp without time zone AND datelocal < '2019-02-01 00:00:00'::timestamp without time zone;
慢查询:
SELECT
date_part('hour', datelocal) AS hour,
SUM(CASE WHEN gender = 'male' THEN views ELSE 0 END) AS male,
SUM(CASE WHEN gender = 'female' THEN views ELSE 0 END) AS female
FROM reportimpression
WHERE
datelocal >= '3-1-2019' AND
datelocal < '4-1-2019'
GROUP BY date_part('hour', datelocal)
ORDER BY date_part('hour', datelocal)
此查询中的日期范围通常是整个月(它接受来自基于 Web 的报告的用户输入) - 正如您所看到的,我尝试为每个月的数据创建一个索引。这有帮助,但据我所知,除非最近运行了查询(将结果放入缓存中),否则它仍然可能需要一分钟才能运行。
解释分析结果:
Finalize GroupAggregate (cost=1035890.38..1035897.86 rows=1361 width=24) (actual time=3536.089..3536.108 rows=24 loops=1)
Group Key: (date_part('hour'::text, datelocal))
-> Sort (cost=1035890.38..1035891.06 rows=1361 width=24) (actual time=3536.083..3536.087 rows=48 loops=1)
Sort Key: (date_part('hour'::text, datelocal))
Sort Method: quicksort Memory: 28kB
-> Gather (cost=1035735.34..1035876.21 rows=1361 width=24) (actual time=3535.926..3579.818 rows=48 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Partial HashAggregate (cost=1034735.34..1034740.11 rows=1361 width=24) (actual time=3532.917..3532.933 rows=24 loops=2)
Group Key: date_part('hour'::text, datelocal)
-> Parallel Index Scan using reportimpression_mar2019_index on reportimpression (cost=0.09..1026482.42 rows=3301168 width=17) (actual time=0.045..2132.174 rows=2801158 loops=2)
Planning time: 0.517 ms
Execution time: 3579.965 ms
我认为处理 1000 万条记录不会太多,特别是考虑到我最近提高了我正在尝试投入资源的 PG 计划,所以我认为问题仍然只是我的索引或者我的查询效率不高。
解决方案
物化视图是实现您概述的内容的方法。查询过去几个月的只读数据无需刷新即可工作。如果您也需要涵盖当前月份,则可能需要对当前月份进行特殊处理。
底层查询仍然可以从索引中受益,您可以采取两个方向:
首先,像你现在这样的部分索引在你的场景中不会买太多,不值得。如果您收集更多月的数据并且主要按月查询(并按月添加/删除行)表分区可能是一个想法,那么您的索引也会自动分区。不过,我会考虑 Postgres 11 甚至是即将推出的 Postgres 12。)
如果您的行很宽,请创建一个允许仅索引扫描的索引。喜欢:
CREATE INDEX reportimpression_covering_idx ON reportimpression(datelocal, views, gender);
有关的:
或INCLUDE
Postgres 11 或更高版本中的其他列:
CREATE INDEX reportimpression_covering_idx ON reportimpression(datelocal) INCLUDE (views, gender);
否则,如果您的行是按物理排序的datelocal
,请考虑使用BRIN 索引。对于您的情况,它非常小,可能与 B 树索引一样快。(但是太小了,它会更容易保持缓存,并且不会将其他数据推送出去。)
CREATE INDEX reportimpression_brin_idx ON reportimpression USING BRIN (datelocal);
您可能对表格行感兴趣CLUSTER
或pg_repack
对表格行进行物理排序。pg_repack
可以在没有对表的排他锁甚至没有 btree 索引(由 要求CLUSTER
)的情况下做到这一点。但它是 Postgres 标准发行版未附带的附加模块。
有关的:
推荐阅读
- javascript - Express.js 在每个请求事件上?
- python - AttributeError:“set”对象没有属性“keys”
- php - 如何使用 php 为我的 wordpress 函数获取当前年份和下一年?
- python - python pandas替换所有浮点值
- autodesk-forge - 使用 LeafletLoader 而不是 PDFLoader 将 PDF 加载到伪造查看器中不起作用
- django - 如何减少 Django 中模型方法生成的 SQL 查询计数?
- c# - 如何使用 await 简单安全地调用可为空的委托
- delphi - Delphi - 查找内存泄漏
- css - 我正在尝试将 css 文件链接到 https
- javascript - 为什么 console.time 显示的时间比实际时间短得多?