Reading a Real Plan

SQLite told you what it intended. Postgres tells you what happened.

EXPLAIN shows the plan and the planner's estimates. EXPLAIN ANALYZE actually runs the query and shows real timings and real row counts alongside the estimates.

That comparison is the whole skill. When the estimate says 10 rows and reality was 400,000, the planner chose its strategy on bad information, and that is usually why the query is slow.

What to look for, in order.

A sequential scan on a large table in a query you run often.

A large gap between estimated and actual rows, which usually means statistics are stale: run ANALYZE.

A nested loop over many rows, where a hash join would have been better, which is normally the same stale-statistics problem.

A sort that spills to disk, visible as external merge, which means work_mem was too small for it.

And the honest one: a query that is simply fast enough. Not everything needs attention.

✦ Measure on data the size you will actually have

A sequential scan over 50 rows is instant, so a plan taken against a development database with a handful of rows tells you almost nothing. Load a realistic volume before optimizing, or you will optimize the wrong query and miss the real one.