On Fri, 15 Feb 2019, Andrew Gierth wrote:
Rich> I've not before run 'explain' on a query. Would that be
Rich> appropriate here?
Yes.
Andrew,
I'll learn how to use it.
The problem here is that you have no join conditions at all, so the
result set of this query is massive. And you've duplicated many tables
inside the subquery which is not necessary or appropriate.
Got it now.
select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name, sq.*
from people as p
join organizations as o on p.organization_id=o.id -- OR WHATEVER
cross join
lateral (select a.next_contact
from activities as a
where a.person_id=p.person_id --VERY IMPORTANT
and a.next_contact > '2018-12-31'
and a.next_contact <= 'today'
and a.next_contact is not null
order by a.next_contact DESC
limit 1) sq;
Ordering by DESC with a limit 1 is used to get the max next_contact
value rather than the smallest; this is similar to max(), but makes it
trivial to also access the other columns of the _same_ activities row
which is being selected.
This puts everything in perspective and is a very valuable lesson for me as
this application has many queries of this type. You've put together all that
I've read in the manual, on this mail list thread, and on web pages. I
really appreciate your patient guidance.
Best regards,
Rich
