-- TASK-0135 perf hot-path indexes, round 2 (EXPLAIN-proven; see
-- docs/development/perf-report.md §3). Companion to
-- 20261003210000_perf_indexes_hot_paths (audit + file listing).
--
-- The first round only covered the audit-listing and file-listing queries. A
-- follow-up EXPLAIN (ANALYZE, BUFFERS) pass over the remaining top-5 queries
-- found TWO more Sort nodes no index served:
--
--   1. Page tree load (depth=all — the "< 2 s for 100k blocks" path):
--        SELECT * FROM blocks WHERE "pageId"=$1 AND "deletedAt" IS NULL
--        ORDER BY "position" ASC LIMIT 1000
--      Before: Bitmap Heap Scan (blocks_workspaceId_pageId_idx, 10k rows) +
--      top-N heapsort — 12.7 ms on the 10k-block seed page.
--      After:  backward-free Index Scan on (pageId, position) — 0.67 ms,
--      Sort node GONE. The existing (pageId, parentId, position) index cannot
--      serve a parentId-less range in position order, and
--      (workspaceId, pageId) has no position key at all.
--
--   2. 100k-record database table list:
--        SELECT * FROM database_records WHERE "databaseId"=$1
--        AND "archivedAt" IS NULL ORDER BY "position" ASC LIMIT 50
--      Before: parallel Bitmap Heap Scan (databaseId, archivedAt) + Gather
--      Merge + Sort — 170 ms on the 100k seed DB.
--      After:  Index Scan on (databaseId, position) — 0.16 ms, Sort node
--      GONE. (databaseId, archivedAt) prunes archived rows but offers no
--      position ordering; the low-selectivity archivedAt filter is applied as
--      an index recheck.
--
-- No speculative indexes: every one here is tied to a named query + a
-- before/after EXPLAIN in the report.

CREATE INDEX IF NOT EXISTS "blocks_pageId_position_idx"
  ON "blocks" ("pageId", "position");

CREATE INDEX IF NOT EXISTS "database_records_databaseId_position_idx"
  ON "database_records" ("databaseId", "position");