StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimization of aggregate SQL query
text
Body
copied!<p>I am running a query against a table in postgressql 9.2.</p> <p>The table has a lot of fields, but the ones relevant to this is:</p> <ul> <li>video_id BIGINT NOT NULL</li> <li>day_date DATE NOT NULL</li> <li>total_plays BIGINT default 0</li> <li>total_playthrough_average DOUBLE PRECISION</li> <li>total_downloads BIGINT default 0</li> </ul> <p>The query takes this form:</p> <pre><code>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') </code></pre> <p>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).</p> <p>The table uses the following index (among others):</p> <pre><code>"video_index1" btree (video_id, day_date, textfield1, textfield2, textfield3) </code></pre> <p>Doing an <code>EXPLAIN ANALYZE</code> on the query gives me this:</p> <pre><code>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 </code></pre> <p>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 <strong>seconds</strong> (usually every few seconds). I am running it with 6-10 simultaneous processes making these queries (among others).</p> <p>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 <code>video_id</code>, with both <code>INSERT</code> and <code>UPDATE</code> queries.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload