agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Best way to aggregate sum for each month
Date: Sat, 18 Apr 2015 00:10:34 +0200 (CEST)
Message-ID: <VisenaEmail.1c.5b6edc86fa70768a.14cc965f560@tc7-visena> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Hi all. I'm using PG-9.4. I'm having a table, activity_log, which holds
activities with duration for a given date. I'm trying to sum the duration for
each month in a given year and am currently doing it like this: select
q.start_date ,sum(log.duration) / (3600 * 1000)::NUMERIC as total_duration FROM
(SELECT cast(generate_series('2014-01-01' :: DATE, '2014-12-01' :: DATE, '1
month') AS DATE) as start_date) AS q LEFT OUTER JOIN activity_log log ON
date_trunc('month', log.start_date::timestamp without time zone) = q.start_date
GROUP BYq.start_date ORDER BY q.start_date ; I have the current index defined:
create indexactivity_start_month ON activity_log(date_trunc('month',
start_date::timestamp without time zone)); Here's the explain plan:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=65.27..91102.17 rows=200 width=12) (actual
time=179.983..344.517 rows=12 loops=1)
Group Key: ((generate_series(('2014-01-01'::date)::timestamp with time
zone, ('2014-12-01'::date)::timestamp with time zone, '1 mon'::interval))::date)
-> Merge Left Join (cost=65.27..82950.79 rows=1629675 width=12) (actual
time=163.894..310.011 rows=143551 loops=1)
Merge Cond: (((generate_series(('2014-01-01'::date)::timestamp with
time zone, ('2014-12-01'::date)::timestamp with time zone, '1
mon'::interval))::date) = date_trunc('month'::text, (log.start_date)::timestamp
without time zone))
-> Sort (cost=64.84..67.34 rows=1000 width=4) (actual
time=0.045..0.052 rows=12 loops=1)
Sort Key: ((generate_series(('2014-01-01'::date)::timestamp
with time zone, ('2014-12-01'::date)::timestamp with time zone, '1
mon'::interval))::date)
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..5.01 rows=1000 width=0) (actual
time=0.017..0.031 rows=12 loops=1)
-> Materialize (cost=0.42..51097.29 rows=325935 width=12) (actual
time=0.032..227.692 rows=323180 loops=1)
-> Index Scan using activity_start_month on activity_log log
(cost=0.42..50282.45 rows=325935 width=12) (actual time=0.029..185.746
rows=323180 loops=1)
Planning time: 0.201 ms
Execution time: 344.645 ms
(12 rows) Are there any ways to improve this? Thanks. -- Andreas
Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com;
<https://www.visena.com;
view thread (5+ messages) latest in thread
Message-ID: <VisenaEmail.1c.5b6edc86fa70768a.14cc965f560@tc7-visena>
Permalink: ../VisenaEmail.1c.5b6edc86fa70768a.14cc965f560@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.1c.5b6edc86fa70768a.14cc965f560@tc7-visena
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com
Subject: Re: Best way to aggregate sum for each month
In-Reply-To: <VisenaEmail.1c.5b6edc86fa70768a.14cc965f560@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox