From: Roxanne Reid-Bennett <rox@tara-lu.com>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: Approach to Data Summary and Analysis
Date: Fri, 18 Apr 2014 01:46:36 -0400
Message-ID: <5350BC3C.5030700@tara-lu.com> (raw)
In-Reply-To: <CAAXGW-w+T890fuf+J3O8h4DyXf6UZnSdVv0XXuWPd_eW3jO=4A@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
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
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: rox@tara-lu.com, pgsql-general@postgresql.org
Subject: Re: Approach to Data Summary and Analysis
In-Reply-To: <5350BC3C.5030700@tara-lu.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