sql - 随着时间的推移，一系列选择(使用分组依据)、删除+插入和稳定的解释计划正在变慢

Question

在 Postgresql 10.4 数据库上，我根据包含 2,129,498 条记录并具有这些索引的表中包含的数据计算了一些关于城市的统计数据:

CREATE INDEX idx_activites_des_communes_code_commune_salaries ON activites_des_communes(code_commune, nombre_salaries DESC);
CREATE UNIQUE INDEX idx_activites_des_communes_code_commune_ape ON activites_des_communes(code_commune, ape);
CREATE INDEX idx_activites_des_communes_ape ON activites_des_communes(ape);
CREATE INDEX idx_activites_des_communes_nombre_entreprises ON activites_des_communes(nombre_entreprises);
CREATE INDEX idx_activites_des_communes_nombre_salaries ON activites_des_communes(nombre_salaries);

ANALYZE 已经在这个大表上完成了。

在循环中，对于 36,000 个城市，我依次为每个城市执行这些语句:

SELECT code_commune, nom, section_naf, 
       SUM(nombre_salaries) as nombre_salaries, sum(nombre_entreprises) as nombre_entreprises 
FROM activites_des_communes 
WHERE code_commune = :code_commune
GROUP BY code_commune, nom, section_naf 
ORDER by code_commune, nom, sum(nombre_salaries) DESC;

这个请求有这个EXPLAIN计划:

"Sort  (cost=2141.72..2143.14 rows=568 width=36)"
"  Sort Key: nom, (sum(nombre_salaries)) DESC"
"  ->  HashAggregate  (cost=2110.05..2115.73 rows=568 width=36)"
"        Group Key: code_commune, nom, section_naf"
"        ->  Bitmap Heap Scan on activites_des_communes  (cost=28.84..2102.94 rows=569 width=28)"
"              Recheck Cond: ((code_commune)::text = '18205'::text)"
"              ->  Bitmap Index Scan on idx_activites_des_communes_code_commune_ape  (cost=0.00..28.70 rows=569 width=0)"
"                    Index Cond: ((code_commune)::text = '18205'::text)"

在这里，我在内存中做了一些计算，我准备在一个可能为空或不为空的表上写入一条记录。我首先删除目标记录，以便之后替换它。

DELETE FROM activites_section_des_communes_par_salaries WHERE code_commune = :code_commune

我已经检查了它的相关 SELECT :它有 EXPLAIN 计划:

"Index Scan using idx_activites_section_des_communes_par_salaries_code_commune on activites_section_des_communes_par_salaries  (cost=0.29..8.30 rows=1 width=762)"
"  Index Cond: ((code_commune)::text = '18205'::text)"

然后，我执行 INSERT 并前往下一个城市。

一开始，我的程序运行速度很快:

2018-12-21 18:18:17.001  INFO 6040 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Arboys en Bugey (01015) : Calcul des statistiques en cours.
2018-12-21 18:18:17.018  INFO 6040 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Arbigny (01016) : Calcul des statistiques en cours.
2018-12-21 18:18:17.026  INFO 6040 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Argis (01017) : Calcul des statistiques en cours.
2018-12-21 18:18:17.051  INFO 6040 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Armix (01019) : Calcul des statistiques en cours.
2018-12-21 18:18:17.068  INFO 6040 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Ars-sur-Formans (01021) : Calcul des statistiques en cours.

但是在完成它的工作时，它会随着时间的推移而减慢和减慢:

2018-12-21 18:53:11.088  INFO 5632 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Lapouyade (33230) : Calcul des statistiques en cours.
2018-12-21 18:53:11.379  INFO 5632 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Laroque (33231) : Calcul des statistiques en cours.
2018-12-21 18:53:11.671  INFO 5632 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Lartigue (33232) : Calcul des statistiques en cours.
2018-12-21 18:53:12.254  INFO 5632 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Latresne (33234) : Calcul des statistiques en cours..
2018-12-21 18:53:12.546  INFO 5632 --- [nio-9090-exec-1] f.c.f.m.application.ActivitesController  : Lavazan (33235) : Calcul des statistiques en cours.

这是我的问题:
1) 我选择的索引似乎无法处理我要运行的请求:具有 GROUP BY 和 ORDER BY 的请求。

2)然而，如何解释放缓？

问候，

Answer 1

对于这个查询:

SELECT code_commune, nom, section_naf, 
       SUM(nombre_salaries) as nombre_salaries, sum(nombre_entreprises) as nombre_entreprises 
FROM activites_des_communes 
WHERE code_commune = :code_commune
GROUP BY code_commune, nom, section_naf 
ORDER by code_commune, nom, sum(nombre_salaries) DESC;

最好的索引在 activites_des_communes(code_commune, nom, section_naf) 上。这对 where 子句和聚合都有效。