In trying to address a query performance problem, I reviewed the explain analyze output and found a couple subqueries were scanning the whole table. This is done to get only the most recent record in those tables related to an appointment. After some research, I decided that it would be reasonable to use a lateral join on those subqueries to dramatically reduce the amount of data scanned. Explain analyze suggested the cost of the whole query with lateral joins would be about a quarter of the original. So we proceeded. Within two hours of deploying the query change, our DB server was maxed out at 100% and basically unresponsive. Reverting the query to use subqueries scanning the tables restored the CPU usage to something sane. Our DB is running in AWS RDS for PostgreSQL using a t2.xlarge. Performance insights showed a substantial increase in ClientWrite. See .
The query using subqueries along with the explain output: https://explain.depesz.com/s/wES6.
The query using lateral join along with the explain output: https://explain.depesz.com/s/B2vp.
So it is obvious to me I did not understand what the explain output was telling me about the queries. What did I miss that could have told me the DB load would be higher with the lateral join query despite a lower cost?