-- 045: composite index on seap.announcements (publication_date DESC NULLS LAST, id DESC)
--
-- Problem: /achizitii/cauta with no filters (home browse) was slow (~3s on
-- empty q). The page does `ORDER BY a.publication_date DESC NULLS LAST,
-- a.id DESC LIMIT 30`, which couldn't use the existing
-- `idx_ann_pub_date btree (publication_date)` because:
--   - Default btree is ASC NULLS LAST (DESC NULLS LAST mismatch for nulls)
--   - Secondary sort `id DESC` requires composite
-- Planner fell back to Seq Scan + top-N sort over 781K rows (~1.5s just
-- for the main result query, plus seq-scan in facet aggregates).
--
-- Fix: composite index matching the exact ORDER BY clause direction.
-- Now Index Only Scan + early LIMIT termination → ~1ms.
--
-- Measurements (production, 781K rows):
--   Before: 1543ms main query, 550ms count, ~3s total /cauta wall time
--   After:  0.7ms main query, ~5ms with LEFT JOIN to cpv_codes/cui_location
--
-- Idempotent: IF NOT EXISTS; CONCURRENTLY to avoid table lock.

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ann_pub_date_desc_nl
  ON seap.announcements (publication_date DESC NULLS LAST, id DESC);
