pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Shaun Thomas <sthomas@optionshouse.com>
To: Eli Naeher <enaeher@gmail.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Window functions, partitioning, and sorting performance
Date: Thu, 21 Aug 2014 09:05:41 -0500
Message-ID: <53F5FCB5.5090109@optionshouse.com> (raw)
In-Reply-To: <CAJVWyAwqnyAw0XvM5tbGFMVuP51YYJXJJoGaRF2gHxhoQn_BFg@mail.gmail.com>
References: <CAJVWyAwqnyAw0XvM5tbGFMVuP51YYJXJJoGaRF2gHxhoQn_BFg@mail.gmail.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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@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



view thread (6+ messages)  latest in thread

Message-ID: <53F5FCB5.5090109@optionshouse.com>
Permalink:  ../53F5FCB5.5090109@optionshouse.com/
Also on:    postgresql.org/message-id/53F5FCB5.5090109@optionshouse.com

 · 

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-performance@postgresql.org
  Cc: sthomas@optionshouse.com, enaeher@gmail.com
  Subject: Re: Window functions, partitioning, and sorting performance
  In-Reply-To: <53F5FCB5.5090109@optionshouse.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox