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@xxxxxxxxxxxxxxx / +44(0)7814 828608 / skype:okohll www.agilebase.co.uk - software www.gtwm.co.uk - company |
