Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 076C6B5DBD9
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 18 Aug 2011 02:40:04 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 80290-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 18 Aug 2011 05:39:57 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pz0-f42.google.com (mail-pz0-f42.google.com
	[209.85.210.42])
	by mail.postgresql.org (Postfix) with ESMTP id 8E06AB5DBD6
	for <pgsql-performance@postgresql.org>;
	Thu, 18 Aug 2011 02:39:57 -0300 (ADT)
Received: by pzk37 with SMTP id 37so3992367pzk.15
	for <pgsql-performance@postgresql.org>;
	Wed, 17 Aug 2011 22:39:57 -0700 (PDT)
Received: by 10.142.61.29 with SMTP id j29mr190175wfa.178.1313645563062;
	Wed, 17 Aug 2011 22:32:43 -0700 (PDT)
Received: from [192.168.3.155] (office1.postnewspapers.com.au
	[150.101.171.128])
	by mx.google.com with ESMTPS id d4sm1189321pbe.68.2011.08.17.22.32.40
	(version=SSLv3 cipher=OTHER); Wed, 17 Aug 2011 22:32:42 -0700 (PDT)
Message-ID: <4E4CA3F2.1080604@ringerc.id.au>
Date: Thu, 18 Aug 2011 13:32:34 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: Anish Kejariwal <anishkej@gmail.com>
CC: Pavel Stehule <pavel.stehule@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Calculating statistic via function rather than with
	query is slowing my query
References: 
 <CAOpcnr9q2-q-n3TkuQFFgwc2UANFikZoFqhPwfmBJFu6b_MVrw@mail.gmail.com>
	<CAFj8pRDVTgF6RQwOEs1wJLHWy-FVO-mFGFRkZF70rmrxY8GTCQ@mail.gmail.com>
	<CAOpcnr-1=YGsp=OAV58cdEfNw4EEuuzEqo53+9C7kURZLmX4Ew@mail.gmail.com>
	<4E4C575D.6080008@ringerc.id.au>
	<CAOpcnr-vYZgT+TMmv58rHCSzeDX9eu_+FmR16pUAkL-DxkrM4w@mail.gmail.com>
In-Reply-To: 
 <CAOpcnr-vYZgT+TMmv58rHCSzeDX9eu_+FmR16pUAkL-DxkrM4w@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201108/221
X-Sequence-Number: 44668

On 18/08/2011 9:03 AM, Anish Kejariwal wrote:
> Thanks for the help Pavel and Craig.  I really appreciate it.  I'm 
> going to try a couple of these different options (write a c function, 
> use a sql function with case statements, and use plperl), so I can see 
> which gives me the realtime performance that I need, and works best 
> for clean code in my particular case.
Do you really mean "realtime"? Or just "fast"?

If you have strongly bounded latency requirements, any SQL-based, 
disk-based system is probably not for you. Especially not one that 
relies on a statics-based query planner, caching, and periodic 
checkpoints. I'd be looking into in-memory databases designed for 
realtime environments where latency is critical.

Hard realtime: If this system fails to respond within <x> milliseconds, 
all the time, every time, then something will go "smash" or "boom" 
expensively and unrecoverably.

Soft realtime: If this system responds late, the late response is 
expensive or less useful. Frequent late responses are unacceptable but 
the occasional one might be endurable.

Just needs to be fast: If it responds late, the user gets irritated 
because they're sitting and waiting for a response. Regular long stalls 
are unacceptable, but otherwise the user can put up with it. You're more 
concerned with average latency than maximum latency.

--
Craig Ringer