Re: Cumulative count (running total) window fn - Mailing list pgsql-general
| From | Oliver Kohll - Mailing Lists |
|---|---|
| Subject | Re: Cumulative count (running total) window fn |
| Date | |
| Msg-id | 4E5B72BE-D4AB-43B0-A9E2-43A05F59F00A@gtwm.co.uk Whole thread Raw |
| In response to | Re: Cumulative count (running total) window fn (Magnus Hagander <magnus@hagander.net>) |
| Responses |
Re: Cumulative count (running total) window fn
|
| List | pgsql-general |
Curious note - how does the non-subselect version and the subselect
version compare performance-wise?
Magnus,
On a test table with 12,000 rows there's not much in it, the subselect has a simpler plan but they both take practically the same time.
The two plans (note I've been rewriting the field names for readability until now but haven't here):
explain analyze SELECT year, sum(c) over (order by year)
FROM (
SELECT extract(year from a56b7a8d6de03f67b) AS year, count(a10e4ab8863c199f1) AS c
FROM a2e9a7e9e257153de
GROUP BY extract(year from a56b7a8d6de03f67b)
) as subq;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=851.49..874.06 rows=1290 width=16) (actual time=43.369..43.394 rows=5 loops=1)
-> Sort (cost=851.49..854.71 rows=1290 width=16) (actual time=43.340..43.342 rows=5 loops=1)
Sort Key: (date_part('year'::text, a2e9a7e9e257153de.a56b7a8d6de03f67b))
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=752.59..771.94 rows=1290 width=26) (actual time=43.300..43.317 rows=5 loops=1)
-> Seq Scan on a2e9a7e9e257153de (cost=0.00..689.56 rows=12605 width=26) (actual time=0.031..26.723 rows=12605 loops=1)
Total runtime: 43.549 ms
explain analyze SELECT extract(year from a56b7a8d6de03f67b), count(a10e4ab8863c199f1), sum(count(a10e4ab8863c199f1)) OVER (ORDER BY count(a10e4ab8863c199f1)) FROM a2e9a7e9e257153de GROUP BY 1 ORDER BY 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1382.39..1388.52 rows=2451 width=32) (actual time=44.229..44.230 rows=5 loops=1)
Sort Key: (date_part('year'::text, a56b7a8d6de03f67b))
Sort Method: quicksort Memory: 25kB
-> WindowAgg (cost=1195.39..1244.41 rows=2451 width=32) (actual time=44.171..44.208 rows=5 loops=1)
-> Sort (cost=1195.39..1201.52 rows=2451 width=32) (actual time=44.125..44.127 rows=5 loops=1)
Sort Key: (count(a10e4ab8863c199f1))
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1014.52..1057.41 rows=2451 width=32) (actual time=44.071..44.099 rows=5 loops=1)
-> Seq Scan on a2e9a7e9e257153de (cost=0.00..833.58 rows=24126 width=32) (actual time=0.032..26.683 rows=12605 loops=1)
Total runtime: 44.396 ms
Regards
Oliver Kohll
oliver@agilebase.co.uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software
www.gtwm.co.uk - company
pgsql-general by date: