On 2020-05-26 1:45 PM, David Rowley wrote:
> On Tue, 26 May 2020 at 22:31, Frank Millman <frank@chagford.com> wrote:
>
>> Thank you David. I tried that and it produced the correct result in
>> 53ms, which is what I am looking for.
>>
>> It will take me some time to understand it fully, so I have some
>> homework to do!
>
> The main problem with your previous query was that the subquery was
> being executed 11088 times and could only ever find anything 167
> times. The remaining number of times nothing would be found.
>
> I just changed the subquery which would be executed once per output
> row and altered it so it became a subquery that's joined and only
> executed once. The ROW_NUMBER() is a windowing function, which is
> explained in [1]. I used this to get the row_id of the record with
> the lowest tran_date, just like you were doing with the ORDER BY
> tran_date DESC LIMIT 1, but the subquery with the windowing function
> gets them all at once, rather than doing it in a way that requires it
> to be executed once for each row in the top-level query. In this case,
> the functionality that the LIMIT 1 does in your query is achieved with
> "AND a.row_num = 1;" in my version. This is pretty fast to execute
> once due to there only being 167 rows.
>
> It's also important to know that there may be cases where the method I
> proposed is slower. For example, if my_table was very large and
> contained rows that were not in table_1 to table_4. Since the subquery
> in my version calculates everything then it could be wasteful to do
> that for values that would never be used. For you, you have foreign
> keys that ensure my_table does not contain records that are not in the
> other tables, but you could still see this issue if you were to add
> some restrictive WHERE clause to the outer query. Perhaps this won't
> be a problem for you, but it's likely good to know.
>
> [1] https://www.postgresql.org/docs/current/tutorial-window.html
>
Thanks very much for the explanation. I will go through it carefully.
For the record, your query works without modification in both Sql Server
and sqlite3. It is also much faster in all three cases - all around
0.005 sec instead of 0.05 sec.
Frank
