Message-ID: <53F5FCB5.5090109@optionshouse.com>
Date: Thu, 21 Aug 2014 09:05:41 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: Eli Naeher <enaeher@gmail.com>, pgsql-performance
 <pgsql-performance@postgresql.org>
Subject: Re: Window functions, partitioning, and sorting performance
References: 
 <CAJVWyAwqnyAw0XvM5tbGFMVuP51YYJXJJoGaRF2gHxhoQn_BFg@mail.gmail.com>
In-Reply-To: 
 <CAJVWyAwqnyAw0XvM5tbGFMVuP51YYJXJJoGaRF2gHxhoQn_BFg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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@optionshouse.com

______________________________________________

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance