A covering index contains the columns needed to evaluate a particular query and return its requested values. In InnoDB, secondary-index records also carry the primary-key columns, so a query can be covered without explicitly listing the primary key again in the secondary-index definition. Coverage is a relationship between one query and an index, not a permanent property that applies to every query against the table. ...
Partition pruning excludes entire partitions that cannot contain matching rows. It is different from using an index to find rows within a remaining partition, and it is not a page-level cache trick. PostgreSQL can perform this exclusion while planning a query or later when parameter values become available during execution. ...
A common table expression gives a query result a name within one SQL statement. It can make a complex report readable by naming populations and intermediate calculations before the final SELECT. That name is not a persistent table, and it does not by itself promise a cached result, a particular scan count, or a sequence of procedural steps. ...
A slow query is a route problem before it is a tuning problem. EXPLAIN shows the planned route node by node, so the fix targets the step that actually costs time rather than a guess. Read the plan as a route map EXPLAIN prints a tree, and the root node is the answer while the leaves do the real work. Each node names an operation, a row estimate and an estimated cost, so instead of guessing you can look for the outlier: a scan whose row estimate dwarfs the number it eventually returns, or an estimate that is wildly off what the query actually touches. The arrow of blame is worth reading the way a map is: follow the node that accounts for most of the work, not the first one listed in the output. Comparing EXPLAIN alongside EXPLAIN ANALYZE shows the gap between what the planner predicted and what actually ran, which is where slow queries usually hide. ...