提高PostgreSQL查询性能
在我的服务器中运行此查询时,速度非常慢,我不明白为什么。任何人都可以帮我弄明白吗?
查询:提高PostgreSQL查询性能
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);
嘿,我在索引中做了一些改动,现在速度就像光!谢谢!! – 2013-03-03 19:42:05
很高兴听到这个消息。不要低估复合指数的力量! :) – mvp 2013-03-03 19:42:48
考虑你的表使用单列:
t_date date
,而不是(t_year, t_month, t_week, t_day)
。数据类型date
占用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);
The sequence of column matters.你甚至可以创建两个。
这些表中有多少条记录?索引编制完成了吗? – 2013-03-03 19:14:12
t_purs约600K记录,t_dat约9K。索引在t_purs.id中设置,t_dat.t_key – 2013-03-03 19:15:36
**您需要向我们展示表和索引定义。**诊断慢查询需要全表和索引定义,而不仅仅是描述或释义。也许你的表格定义不好。也许索引没有正确创建。也许你没有一个你认为你做过的那个专栏的索引。没有看到表和索引定义,我们不能说。 – 2013-03-04 04:41:54