Query Tuning Techniques (Part 2)
Introduction Continuing from the previous article, here are other Query Tuning techniques based on common errors encountered when querying data and the process you can apply: Optimizing JOIN Statements Ensure columns used for JOIN have Indexes (usually Foreign Key connecting to Primary Key ). If Postgres uses a Nested Loop Join with a matching Index, it speeds up finding rows needed for comparison during JOIN If using a Merge Join , it skips the step of sorting two lists before joining Try filtering data using WHERE beforehand or directly inside the ON condition of the JOIN to reduce the row count matching between tables. The result of using JOIN is multiplication: A Join B = A x B Therefore, if conditions can minimize lists A and B, the generated dataset will be as small as possible Joining Too Many Tables By default, if joining more than 12 tables , Postgres cannot analyze and find the optimal JOIN execution plan because the number of combinations to process is too large At this ...