Understanding the logic behind EXPLAIN ANALYSE
When processing queries, the database has two possibilities:
- Access the data directly using indexes.
- Perform sequential scan on disc or in memory.
Sequential scans on large tables are considered evil, because they eat database memory and CPU time. A sequential scan on 100.000 rows will consume 100.000 CPU cycles . Even more when scanning from disc.
Therefore, the logic behind debugging queries is to run EXPLAIN on each slow query and hunt for sequential scans.
When you find a sequential scan, try to rewrite the query.
Rewriting queries takes both time and effort.
Here are examples for the most simple cases:
- Add an index on columns involded in sequential scans.
- Make sure that JOIN left and right colums have indexes.
SELECT foo FROM table WHERE bar=1
Of course, bar should carry an index.
SELECT foo, bar FROM table1 t1 INNER JOIN table2 t2 on t1.a = t2.a WHERE t2.c=1
Of course, t1.a and t2.a should carry indexes.
The guide covers detailed techniques for optimizing queries.
Usually, you can always transform a sequential query into a collection of INNER, LEFT and RIGHT JOINs which does not produce a sequential scan or produces it in the very last steps, when minimal information can be loaded in memory and computer fast.