Reading EXPLAIN ANALYZE PostgreSQL for query optimization

EXPLAIN vs. EXPLAIN ANALYZE

EXPLAIN displays the execution plan based on estimates from table statistics, without actually running the query. EXPLAIN ANALYZE actually execute the query and display the real execution time along with the actual number of rows.

EXPLAIN ANALYZE
SELECT * FROM pesanan
WHERE pelanggan_id = 105
ORDER BY dibuat_pada DESC
LIMIT 20;

Reading the results

Example of result snippet:

Limit (cost=0.42..8.60 rows=20 width=64) (actual time=0.05..0.09 rows=20 loops=1)
  -> Index Scan Backward using idx_pesanan_pelanggan_tanggal on pesanan
       (cost=0.42..120.30 rows=294 width=64) (actual time=0.05..0.08 rows=20 loops=1)
     Index Cond: (pelanggan_id = 105)

Score cost is an estimate, whereas actual time is real time in milliseconds. If rows estimates are much different from actual rows, table statistics may be outdated.

Sequential scans aren't always bad

For small tables or when the query fetches most of the rows, Seq Scan can be faster than using an index because it avoids random read jumps to data pages.

Updating stats

ANALYZE pesanan;

Obsolete statistics make the query planner choose a less-than-optimal plan. Execute ANALYZE after changes in large amounts of data.

practice;

Go EXPLAIN ANALYZE on frequently used queries in your app, then identify if there are any Seq Scan on a large table that can be helped with a new index.