提高PostgreSQL查询性能

Question

当我在服务器上运行这个查询时，速度非常慢，而且我不明白为什么会这样。有人可以帮我找出原因吗？
查询语句：

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 行=1 宽度=28) (实际时间=10212.374..10212.384 行=10 循环=1)
  ->  嵌套循环 (Nested Loop)  (cost=0.00..12252.03 行=1 宽度=28) (实际时间=3016.006..10212.249 行=14 循环=1)
        合并连接过滤器：(t_dat.t_key = t_purs.created)
        ->  序列扫描（Seq Scan）在 t_dat 上  (cost=0.00..129.90 行=1 宽度=20) (实际时间=0.745..2.040 行=48 循环=1)
              过滤器：((t_day = ANY ('{1,2}'::integer[])) AND (t_year = 2013) AND (t_month = 3) AND (t_week = 9))
        ->  序列扫描（Seq Scan）在 t_purs 上  (cost=0.00..12087.49 行=9900 宽度=16) (实际时间=0.018..201.630 行=14014 循环=48)
              过滤器：(p_id = ANY ('{4,15,18,19,20,29,31,35,46,56,72,78}'::integer[]))
总运行时间：10212.470 毫秒

- Eli_Rozen

---

这些表有多少记录？索引是否完成？ - Travis G

t_purs大约有600K条记录，t_dat大约有9K。索引设置在t_purs.id，t_dat.t_key上。 - Eli_Rozen

你需要向我们展示表格和索引的定义。诊断缓慢的查询需要完整的表格和索引定义，而不仅仅是描述或释义。也许你的表格定义不够好。可能索引没有正确创建。也可能你认为在那一列上有一个索引，但事实并非如此。若没有查看表格和索引的定义，我们无法判断。 - Andy Lester

此外，在引号中放置“列”和“表”名称是完全不必要的。 - Andy Lester

Answer 1

很难确定您缺少什么，但如果我是您，我会确保以下索引存在：

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

对于表，创建如下索引:

CREATE INDEX t_purs_created_p_id_idx
    ON t_purs (created, p_id);

- mvp

---

嘿，我在索引中做了一些更改，现在速度飞快！谢谢！！ - Eli_Rozen

很高兴听到这个消息。不要低估复合索引的威力！ :) - mvp

Answer 2

您的查询在t_purs和t_dat上进行了顺序扫描。创建适当的索引将有助于加快此查询的速度并避免顺序扫描。

create index index_name on t_purs(created) where created is not null;
create index index_name on t_dat using btree(t_key, t_year, t_month, t_week, t_day)

在运行上述两个查询之后，运行 explain analyze。您将看到计划时间和执行时间将被缩短。

- Pratyush Srivastava

Answer 3

考虑在您的表格中使用单列：

t_date date

使用date数据类型代替(t_year, t_month, t_week, t_day)。日期数据类型占用4个字节。这将使您的表格变小，索引更小更快，分组更容易。

年、月、周和日可以很容易地从日期中提取，使用extract()函数。然后您的查询可能如下所示，并且速度更快：

SELECT extract (year  FROM t_date) AS c0
      ,extract (month FROM t_date) AS c1
      ,extract (week  FROM t_date) AS c2
      ,extract (day   FROM t_date) AS c3
      ,p.p_id                      AS c4
      ,sum(p.days)                 AS m0
      ,sum(p.timecreated)          AS m1
FROM   t_dat  d
JOIN   t_purs p ON p.created = d.t_key
WHERE  d.t_date IN ('2013-03-01'::date, '2013-03-02'::date)
AND    p.p_id IN (4,15,18,19,20,29,31,35,46,56,72,78)
GROUP  BY d.t_date, p.p_id;

更重要的是性能，索引如下所示：

CREATE INDEX t_dat_date_idx ON t_dat (t_key, t_date);

或者，根据数据分布情况：

CREATE INDEX t_dat_date_idx ON t_dat (t_date, t_key);

列的顺序很重要。你甚至可以同时创建两个。

- Erwin Brandstetter