The PostgreSQL optimizer is an amazing thing, getting only more amazing with each release. It is able to take information about your data definitions,

Postgres Query Optimization: LEFT JOIN vs UNION ALL

submited by

Style Pass

2022-05-20 21:00:08

The PostgreSQL optimizer is an amazing thing, getting only more amazing with each release. It is able to take information about your data definitions, your data distribution, constraints, and the specific queries and come up with the generally most efficient way to return the results of that query.

Since SQL is a declarative language, we're explicitly giving up defining how the database determines the results and trusting it to get the correct results in whatever method it deems most efficient. Sometimes we can structure queries in a certain way to get a better result than we get with the straightforward approach.

I was working with one of our clients hosted on Crunchy Bridge, looking at the top queries in pg_stat_statements by mean time spent. The top outlier was a query of the form:

The average time on this query, as reported by pg_stat_statements was in the 2-3 second range, which for this client was too long. The tables involved here were decently-sized, with millions of rows per table.