聚合SQL查询的优化

Question

我正在对postgressql 9.2中的一个表运行一个查询。

该表包含许多字段，但与此相关的字段是：

• video_id BIGINT不为空
• day_date日期不为空
• total_plays BIGINT默认值0
• total_playthrough_average双精度
• total_downloads BIGINT默认值0

查询采用以下形式：

SELECT
  SUM(total_plays) AS total_plays
  CASE SUM(total_downloads)
    WHEN 0 THEN 100
    ELSE SUM(total_playthrough_average * total_downloads) / SUM(total_downloads) END AS total_playthrough_average
FROM
  mytable
WHERE
  video_id = XXXX

# Date parameter - examplified by current month
AND day_date >= DATE('2013-09-01') AND day_date <= DATE('2013-09-30')

查询的重点是找到playthrough_average (一般人看到多少视频，在0到100之间)，按每个视频的下载来加权(因此，下载100次的视频的平均播放量比下载10次的视频重)。

该表使用以下索引(除其他外)：

"video_index1" btree (video_id, day_date, textfield1, textfield2, textfield3)

在查询上执行EXPLAIN ANALYZE将给我以下结果：

Aggregate  (cost=153.33..153.35 rows=1 width=24) (actual time=6.219..6.221 rows=1 loops=1)
   ->  Index Scan using video_index1 on mytable  (cost=0.00..152.73 rows=40 width=24) (actual time=0.461..5.387 rows=105 loops=1)
         Index Cond: ((video_id = 6702200) AND (day_date >= '2013-01-01'::date) AND (day_date <= '2013-12-31'::date))
Total runtime: 6.757 ms

这似乎一切都很好，但这只是当我用已经执行的查询进行测试时。当我的程序运行时，我会收到很多查询，需要花费10-30 秒(通常每隔几秒钟)。我使用6-10个同时进行这些查询的进程运行它(除其他外)。

我是否可以在postgresql设置中进行一些调整，以获得更好的性能？该表会不断更新，尽管每个video_id可能每小时只更新一次或两次，同时使用INSERT和UPDATE查询。

Answer 1

你的总结对我来说毫无意义。我想你要的是

select
    sum(total_plays) as total_plays,
    sum(total_downloads) as total_downloads,
    sum(total_playthrough_average * total_downloads) as total_playthrough_average
from mytable
where
    video_id = 1
    and day_date between '2013-09-01' and '2013-09-30'

SQL Fiddle