Message-ID: <519EA49B.7030404@mansionfamily.plus.com>
Date: Fri, 24 May 2013 00:22:03 +0100
From: james <james@mansionfamily.plus.com>
Reply-To: james@mansionfamily.plus.com
User-Agent: Mozilla/5.0 (Windows NT 6.2; WOW64;
	rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
To: Jonathan Morra <jonmorra@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Performance of complicated query
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>
In-Reply-To: 
 <CAF8LAAXnSd+2x495baxzjbZhT0_g5sSER_b_qqjCzdYj3QFn4g@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------000306010605030907080109"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------000306010605030907080109
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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. ;-)


--------------000306010605030907080109
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 23/05/2013 22:57, Jonathan Morra
      wrote:<br>
    </div>
    <blockquote
cite="mid:CAF8LAAXnSd+2x495baxzjbZhT0_g5sSER_b_qqjCzdYj3QFn4g@mail.gmail.com"
      type="cite">
      <div class="moz-text-html" lang="x-western">
        <div dir="ltr">I'm not sure I understand your proposed solution.
          &nbsp;There is also the case to consider where the same patient can
          be assigned the same device multiple times. &nbsp;In this case, the
          value may be reset at each assignment (hence the line&nbsp;<span
            style="font-family:arial,sans-serif;font-size:13px">value -
            issued_value AS value from the original query).</span></div>
        <br>
      </div>
    </blockquote>
    <br>
    Perhaps you could use triggers to help somewhat?&nbsp; At least for the
    lifetime part.<br>
    <br>
    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).<br>
    <br>
    But, that might fix 'lifetime' but not some arbitrary windowed
    view.&nbsp; 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?<br>
    <br>
    It also seems to me that the solution you have is hard to reason
    about.&nbsp; 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.<br>
    <br>
    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.&nbsp; Consider the CTE 'WITH queries' for doing this?<br>
    <br>
    It seems to me that if you can do this, then the problem might be
    easier to express.<br>
    <br>
    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. ;-)<br>
    <br>
  </body>
</html>

--------------000306010605030907080109--