提高PostgreSQL查询性能

发布时间：2020-12-13 16:03:23 所属栏目：百科来源：网络整理

导读：在我的服务器中运行此查询时,它非常慢,我无法理解为什么.任何人都可以帮我搞清楚吗？查询： SELECT "t_dat"."t_year" AS "c0","t_dat"."t_month" AS "c1","t_dat"."t_week" AS "c2","t_dat"."t_day" AS "c3","t_purs"."p_id" AS "c4",sum("t_purs"."days")

在我的服务器中运行此查询时,它非常慢,我无法理解为什么.任何人都可以帮我搞清楚吗？
查询：

SELECT
    "t_dat"."t_year" AS "c0","t_dat"."t_month" AS "c1","t_dat"."t_week" AS "c2","t_dat"."t_day" AS "c3","t_purs"."p_id" AS "c4",sum("t_purs"."days") AS "m0",sum("t_purs"."timecreated") AS "m1"
FROM "t_dat","t_purs"
WHERE "t_purs"."created" = "t_dat"."t_key"
  AND "t_dat"."t_year" = 2013
  AND "t_dat"."t_month" = 3
  AND "t_dat"."t_week" = 9
  AND "t_dat"."t_day" IN (1,2)
  AND "t_purs"."p_id" IN (
      '4','15','18','19','20','29','31','35','46','56','72','78')
GROUP BY
    "t_dat"."t_year","t_dat"."t_month","t_dat"."t_week","t_dat"."t_day","t_purs"."p_id"

解释分析：

HashAggregate  (cost=12252.04..12252.04 rows=1 width=28) (actualtime=10212.374..10212.384 rows=10 loops=1)
  ->  Nested Loop  (cost=0.00..12252.03 rows=1 width=28) (actual time=3016.006..10212.249 rows=14 loops=1)
        Join Filter: (t_dat.t_key = t_purs.created)
        ->  Seq Scan on t_dat  (cost=0.00..129.90 rows=1 width=20) (actual time=0.745..2.040 rows=48 loops=1)
              Filter: ((t_day = ANY ('{1,2}'::integer[])) AND (t_year = 2013) AND (t_month = 3) AND (t_week = 9))
        ->  Seq Scan on t_purs  (cost=0.00..12087.49 rows=9900 width=16) (actual time=0.018..201.630 rows=14014 loops=48)
              Filter: (p_id = ANY ('{4,15,18,19,20,29,31,35,46,56,72,78}'::integer[]))
Total runtime: 10212.470 ms

解决方法

很难说你到底错过了什么,但如果我是你,我会确保跟踪索引存在：

CREATE INDEX t_dat_id_date_idx
    ON t_dat (t_key,t_year,t_month,t_week,t_day);

对于t_purs,创建此索引：

CREATE INDEX t_purs_created_p_id_idx
    ON t_purs (created,p_id);

（编辑：李大同）

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