postgresql - Postgresql 仅索引扫描在 Group By 上无法正常工作
问题描述
我有一张像这样的桌子:
CREATE TABLE summary
(
id serial NOT NULL,
user_id bigint NOT NULL,
country character varying(5),
product_id bigint NOT NULL,
category_id bigint NOT NULL,
text_id bigint NOT NULL,
text character varying(255),
product_type integer NOT NULL,
event_name character varying(255),
report_date date NOT NULL,
currency character varying(5),
revenue double precision,
last_event_time timestamp
);
我的表大小为 1786 MB(索引除外)。在这里,我创建了如下索引:
CREATE INDEX "idx_as_type_usr_productId_eventTime"
ON summary USING btree
(product_type, user_id, product_id, last_event_time)
INCLUDE(event_name);
我的简单查询如下所示:
select
event_name,
max(last_event_time)
from summary s
where s.user_id = ? and s.product_id = ? and s.product_type = ?
and s.last_event_time > '2020-03-01' and s.last_event_time < '2020-03-25'
group by event_name;
当我解释它时,它看起来像;
HashAggregate (cost=93.82..96.41 rows=259 width=25) (actual time=9187.533..9187.536 rows=10 loops=1)
Group Key: event_name
Buffers: shared hit=70898 read=10579 dirtied=22650
I/O Timings: read=3876.367
-> Index Only Scan using "idx_as_type_usr_productId_eventTime" on summary s (cost=0.56..92.36 rows=292 width=25) (actual time=0.485..9153.812 rows=87322 loops=1)
Index Cond: ((product_type = 2) AND (product_id = ?) AND (product_id = ?) AND (last_event_time > '2020-03-01 00:00:00'::timestamp without time zone) AND (last_event_time < '2020-03-25 00:00:00'::timestamp without time zone))
Heap Fetches: 35967
Buffers: shared hit=70898 read=10579 dirtied=22650
I/O Timings: read=3876.367
Planning Time: 0.452 ms
Execution Time: 9187.583 ms
在这里,一切看起来都很好。但是当我执行它时,它需要超过 10 秒,有时需要超过 30 秒。
- 在这里,如果我在没有 Group By 的情况下执行它,它会很快返回,不到 2 秒。Group By 的作用是什么?选择部分是如此之少(如 500 行)。
- 该表具有每秒 30 次的插入/更新操作。这可能与这个索引问题有关吗?
更新:
查询没有 - GroupBy:
select
event_name,
last_event_time
from summary s
where s.user_id = ? and s.product_id = ? and s.product_type = ?
and s.last_event_time > '2020-03-01' and s.last_event_time < '2020-03-25';
不解释 - 分组方式:
Index Only Scan using "idx_as_type_usr_productId_eventTime" on summary s (cost=0.56..92.36 rows=292 width=25) (actual time=0.023..79.138 rows=87305 loops=1)
Index Cond: ((product_type = ?) AND (user_id = ?) AND (product_id = ?) AND (last_event_time > '2020-03-01 00:00:00'::timestamp without time zone) AND (last_event_time < '2020-03-25 00:00:00'::timestamp without time zone))
Heap Fetches: 22949
Buffers: shared hit=37780 read=12143 dirtied=15156
I/O Timings: read=4418.930
Planning Time: 0.639 ms
Execution Time: 4625.213 ms
解决方案
有几个问题:
PostgreSQL 必须设置提示位,这会弄脏页面并导致写入。
PostgreSQL 必须从磁盘获取表行以获取它们的可见性。
PostgreSQL 必须扫描 80000 个页面才能获得 87000 行,因此索引必须完全膨胀。
前两个可以通过运行来处理
VACUUM summary;
在大量装载后这总是一个好主意,并且膨胀可以通过以下方式治愈
REINDEX INDEX "idx_as_type_usr_productId_eventTime";
推荐阅读
- linux - 如何在 bash 中创建动态命令?
- javascript - 如果功能组件没有构造函数,酶如何找到功能?
- matlab - MATLAB 2019a 产生数百个进程
- javascript - 您如何使用 gremlin javascript 中的克隆功能?
- python - 其他 python 库不适用于 macos
- r - R 中的这个套索回归实现有什么问题?
- python - EC2 上的 Python 脚本返回错误的签名类型错误
- android - 我无法在 Android 上完成启动时运行任何代码
- python - 不合需要地绘制了日期字符串的共享 xaxis
- apache-spark - 我如何知道 spark 进程挂在代码的哪一部分?