From: james <james@mansionfamily.plus.com>
To: Jonathan Morra <jonmorra@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of complicated query
Date: Fri, 24 May 2013 00:22:03 +0100
Message-ID: <519EA49B.7030404@mansionfamily.plus.com> (raw)
In-Reply-To: <CAF8LAAXnSd+2x495baxzjbZhT0_g5sSER_b_qqjCzdYj3QFn4g@mail.gmail.com>
References: <CAF8LAAUN7mJ3d-x2qAFvq07BUtQydBkDmRLk_mX0OCa0sFaJZw@mail.gmail.com>
<519E563A.4010401@pinpointresearch.com>
<CAF8LAAXxB=RKcrgd0ggiPhvZEaTjKj3Wk6dzCK6_e8w_Q2cvJg@mail.gmail.com>
<519E75AC.80103@pinpointresearch.com>
<CAF8LAAXnSd+2x495baxzjbZhT0_g5sSER_b_qqjCzdYj3QFn4g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 23/05/2013 22:57, Jonathan Morra wrote:
> I'm not sure I understand your proposed solution. There is also the
> case to consider where the same patient can be assigned the same
> device multiple times. In this case, the value may be reset at each
> assignment (hence the line value - issued_value AS value from the
> original query).
>
Perhaps you could use triggers to help somewhat? At least for the
lifetime part.
For a given assignment of a device to a patient, only the last value is
useful, so you can maintain that easily enough (a bit like a
materialised view but before 9.3 I guess).
But, that might fix 'lifetime' but not some arbitrary windowed view. I
can see why an 'as at' end time is useful, but not why a start time is
so useful: if a device has readings before the window but not in the
window, is that 'no reading' or should the last reading prior to the
window apply?
It also seems to me that the solution you have is hard to reason about.
Its like a Haskell program done in one big inline fold rather than a
bunch of 'where' clauses, and I find these cause significant brain overload.
Perhaps you could break it out into identifiable chunks that work out
(both for lifetime if not using triggers, and for your date range
otherwise) the readings that are not superceded (ie the last in the date
bounds for a device assignment), and then work with those. Consider the
CTE 'WITH queries' for doing this?
It seems to me that if you can do this, then the problem might be easier
to express.
Failing that, I'd be looking at using temporary tables, and forcing a
series of reduce steps using them, but then I'm a nasty old Sybase
hacker at heart. ;-)
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: james@mansionfamily.plus.com, jonmorra@gmail.com
Subject: Re: Performance of complicated query
In-Reply-To: <519EA49B.7030404@mansionfamily.plus.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