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.

Example:
SELECT foo FROM table WHERE bar=1
Of course, bar should carry an index.

• Make sure that JOIN left and right colums have indexes.

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.