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
David