Message-ID: <5350BC3C.5030700@tara-lu.com>
Date: Fri, 18 Apr 2014 01:46:36 -0400
From: Roxanne Reid-Bennett <rox@tara-lu.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
CC: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Approach to Data Summary and Analysis
References: 
 <CAAXGW-zzACjHfREYEo4FBmJXLHD7d=nMH-i4WeSi_DTWBLzezQ@mail.gmail.com>
	<534DB214.9030003@tara-lu.com>
	<CAAXGW-ySnmq587HQvxGCH0PkixhvqRE+NmEZJH0=inZCt9_iPg@mail.gmail.com>
	<534EA4CA.5070401@tara-lu.com>
 <CAAXGW-w+T890fuf+J3O8h4DyXf6UZnSdVv0XXuWPd_eW3jO=4A@mail.gmail.com>
In-Reply-To: 
 <CAAXGW-w+T890fuf+J3O8h4DyXf6UZnSdVv0XXuWPd_eW3jO=4A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

On 4/16/2014 2:40 PM, Robert DiFalco wrote:
> Thanks Roxanne, I suppose when it comes down to it -- for the current 
> use cases and data size -- my only concern is the "calling" query that 
> will need to use max to determine if a user has already had a call 
> today. For a large user set, for each user I would either have to MAX 
> on the answered timestamp to compare it against today or do an exist 
> query to see if any timestamp for that user is greater or equal than 
> "today".

I didn't go back to look at your original schema- but.. if your 500K 
records are coming in time ordered... You may be able to track "max" as 
an attribute  on an "SCD" based on the caller/callee table [or the 
caller/ee table itself if that table is only used by your app] with an 
update from a post-insert trigger on the appropriate table. Even if they 
aren't time ordered, you add the overhead of a single comparative in the 
trigger.  Downside is that you fire a trigger and an update for every 
insert. [or just an update depending on what is driving your load of the 
500K records]

Again - the proof on "value" of this overhead is a comparison of the 
cost for the updates vs the cost on the query to find max()  I suspect 
your once a day query can afford all sorts of other optimizations that 
are "better" than a trigger fired on every insert.  [such as the 
function index - that was already mentioned] I really suspect you just 
don't have enough load on the query side (complex queries * # of users) 
to justify the extra load on the write side (+1 trigger, +1 update / 
insert) to avoid a (potentially) heavy query load 1x/day.

Another option... if only worried about "today".. then keep only 
"today's" data in your query table, and migrate historical data nightly 
to a pseudo archive table for those "every once in a while" questions.  
I haven't played with table inheritance in Postgres - but that's a 
capability I might look at if I were doing a pseudo archive table.

Roxanne


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