I finished the query tuning chapter. Toady I read about query optimization and auto explain . Main takeaways are:
– Usually query optimization boils down to leveraging an appropriate index.
– Avoid subqueries since they will likely cause redundant looping.
– Keep an eye on unused indexes (pg_stat_user_indexes) since they take up space and effort when inserting new rows.
– Use auto-explain to automate logging of inefficient queries.
– EXPLAIN use statistics when finding the best query path, EXPLAIN ANALYZE performs the query and returns execution time. Use transaction and roll back in order to avoid side effects.