Semab Tariq talks query tuning:
Performance optimization in a production database is crucial, but over-optimizing can complicate things without real improvements.
In this blog post, I’ll share two very basic EXPLAIN ANALYZE outputs from a production system. A user asked us to help optimize these plans (I’ve changed the table and column names for privacy).
We will look at how to spot slow parts of a query, improve performance the right way, and avoid unnecessary tuning.
One of the things you eventually learn as a performance tuner is that sometimes, it’s best not to try to optimize a particular query. This may seem a bit contradictory–who doesn’t want to go faster? But there are costs to actions, and spending a long time tuning an ad hoc query that somebody ran one time and probably won’t run again isn’t worth it. Ultimately, know how to tune, but also when to tune and what will give you the biggest marginal benefit.