CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Christian P.
Christian P.

Reputation: 4884

Optimization of aggregate SQL query

I am running a query against a table in postgressql 9.2.

The table has a lot of fields, but the ones relevant to this is:

The query takes this form:

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')

The point of the query is to find the playthrough_average (a score of how much of the video the average person sees, between 0 and 100) of all videos, weighted by the downloads each video has (so the average playthrough of a video with 100 downloads weighs more than that of a video with 10 downloads).

The table uses the following index (among others):

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

Doing an EXPLAIN ANALYZE on the query gives me this:

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

This seems like everything is dandy, but this is only when I test with a query that has already been performed. When my program is running I get a lot of queries taking 10-30 seconds (usually every few seconds). I am running it with 6-10 simultaneous processes making these queries (among others).

Is there something I can tweak in the postgresql settings to get better performance out of this? The table is updated constantly, although maybe only once or twice per hour per video_id, with both INSERT and UPDATE queries.

Upvotes: 0

Views: 1202

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

Your summing does not make sense to me. I think what you want is

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

Upvotes: 1

Related Questions