Inspect and index a query ​

Start with a query's intended filters and ordering. EXPLAIN QUERY PLAN describes the selected access path without running the query.

An index should match an actual column or expression used by that query. ANALYZE collects statistics for planning; after creating an index, inspect the plan and verify the selected rows again.

Prepare a filtered table ​

CREATE TABLE events(id INTEGER PRIMARY KEY,kind String);
INSERT INTO events VALUES(1,'view'),(2,'click');
CREATE INDEX events_kind ON events(kind);
ANALYZE;

Inspect the selected access path ​

EXPLAIN QUERY PLAN SELECT id FROM events WHERE kind='click';

┌─id─┬─parent─┬─notused─┬─detail───────────────────────────────────────────────────────┐
│  1 │      0 │       0 │ PROJECTION id                                                │
│  2 │      1 │       0 │ INDEX_SCAN Table: events; Index: events_kind; Covering: true │
└────┴────────┴─────────┴──────────────────────────────────────────────────────────────┘
(2 rows)

The plan below the projection identifies the selected table access and whether an index covers the required values. Plan text can change between versions; use it to understand this execution choice rather than treating it as a permanent output schema.

Indexes also add write and storage work. Use the comparison workflow and runtime measurements for a representative dataset.