Re: Window functions, partitioning, and sorting performance

From: Shaun Thomas <sthomas(at)optionshouse(dot)com>
To: Eli Naeher <enaeher(at)gmail(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Window functions, partitioning, and sorting performance
Date: 2014-08-21 14:05:41
Message-ID: 53F5FCB5.5090109@optionshouse.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On 08/21/2014 08:29 AM, Eli Naeher wrote:

> With around 1.2 million rows, this takes 20 seconds to run. 1.2 million
> rows is only about a week's worth of data, so I'd like to figure out a
> way to make this faster.

Well, you'll probably be able to reduce the run time a bit, but even
with really good hardware and all in-memory processing, you're not going
to see significant run-time improvements with that many rows. This is
one of the reasons reporting-specific structures, such as fact tables,
were designed to address.

Repeatedly processing the same week/month/year aggregate worth of
several million rows will just increase linearly with each iteration as
data size increases. You need to maintain up-to-date aggregates on the
metrics you actually want to measure, so you're only reading the few
hundred rows you introduce every update period. You can retrieve those
kind of results in a few milliseconds.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Shaun Thomas 2014-08-21 14:14:56 Re: Turn off Hyperthreading! WAS: 60 core performance with 9.3
Previous Message Marti Raudsepp 2014-08-21 14:02:07 Re: Window functions, partitioning, and sorting performance