A shipment timeline query had a compound index yet still took 2.4 seconds. explain() showed a blocking SORT stage and 180,000 documents examined for 50 results. Reordering the index by Equality, Sort, Range fixed it.
EXPLAIN ANALYZE prints a wall of nodes, costs and loops that most of us skim for the word Seq Scan. This guide walks a real slow query node by node: estimates vs actuals, loops, buffers, and the fixes each pattern points to.
Our monthly-partitioned orders table was supposed to make the daily report touch one partition. EXPLAIN showed all 36. A date cast on the partition key and a join-driven filter were blocking pruning, and two small query rewrites brought it back.