A functional index stores keys computed from expressions over table data. It helps when queries repeatedly apply the same function, but it does not automatically replace an index on the underlying column. section 2.7
Matching the indexed expression
A direct timestamp predicate can use an ordinary index, while `date_trunc('s', tstamp) = ...` may trigger a sequential scan because the transformed value no longer matches the stored index keys. Create a matching expression index with `CREATE INDEX ON erp.payments(date_trunc('m', tstamp AT TIME ZONE 'UTC+1'));`, then use that same function and cast in the predicate. section 2.7
Use functional indexes cautiously:
- Prefer moving a transformation to the comparison value when an equivalent range predicate can preserve use of the ordinary index. section 2.7
- Treat the index as lossy for other query forms: different functions or casts may prevent it from applying. section 2.7
- Consider relational columns instead of many JSON-key expression indexes when JSON data is being queried as relational data. section 5.3
EXPLAIN (ANALYZE, FORMAT YAML)
SELECT * FROM erp.payments
WHERE tstamp = '2023-10-18 03:40:34.000';
QUERY PLAN
----------------------------------------------------------------------------
- Plan: +
Node Type: "Index Scan" +
Parallel Aware: false +
Async Capable: false +
Scan Direction: "Forward" +
Index Name: "payments_tstamp_idx" +
Relation Name: "payments" +
Alias: "payments" +
Startup Cost: 0.42 +
Total Cost: 8.46 +
Plan Rows: 2 +
Plan Width: 31 +
Actual Startup Time: 0.010 +
Actual Total Time: 0.011 +
Actual Rows: 0 +
Actual Loops: 1 +
Index Cond: "(tstamp = '2023-10-18 03:40:34+01'::timestamp with time
zone)" +
Rows Removed by Index Recheck: 0 +
Planning Time: 0.051 +
Triggers: +
Execution Time: 0.022
(1 row)
Compare the ordinary timestamp query plan with transformed predicates: `EXPLAIN` output shows when PostgreSQL chooses an Index Scan or a sequential scan. section 2.7
