PG18 skip scan landed — review now-redundant single-column indexes

[gilgardosh]

Part 3.4 of the audit in docs/postgresql-v18-migration.md. The predicted benefit has landed — this issue is the residual cleanup question it opens.

What already happened, measured on production

PG18's B-tree skip scan uses a multicolumn index when a leading column is unconstrained but later ones are. Verified live after the upgrade (see #4363):

Query	Before (16.14)	After (18.6)
foreign-securities.provider.ts securities dedup	plain owner_id index + Filter, 262 rows discarded	poalim_securities_transactions_dedup_uindex, all predicates in Index Cond, 0 discarded
poalim-scraper-ingestion.provider.ts securities dedup	plain owner_id index + Filter, 261 discarded	same index, 0 discarded

The proof it is genuinely skip scan and not just a costing change: the Index Cond includes value_date (7th index column) while trade_date (6th) has no predicate at all. Using a column positioned after an unconstrained one is only possible with skip scan.

Two of the four candidates did not change: the poalim_ils dedup query still prefers its event_date index, and the otsar_hahayal one is on an empty table so there is nothing to optimise.

No code change was needed. That was the point.

The residual question: are some single-column indexes now redundant?

The audit warned that skip scan "may make some existing single-column indexes redundant against their composite siblings. Measure before dropping anything."

There is now concrete evidence for one candidate: poalim_securities_transactions_owner_id_idx was the index both dedup queries used on 16, and neither uses it on 18. If nothing else selects it, it is pure write and storage overhead.

What to do

Do not drop anything on the strength of the two plans above. Instead:

1. Let index usage accumulate for a few weeks post-upgrade.
2. Query pg_stat_user_indexes for idx_scan = 0 (or near-zero) within accounter_schema, paying attention to single-column indexes that have a composite sibling with the same leading column.
3. Check each candidate is not backing a constraint or a foreign key before proposing a drop.
4. Drop with DROP INDEX CONCURRENTLY.

Note the counters reset on the upgrade, so any reading taken before roughly late September 2026 covers too short a window to be trustworthy.