mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:31:13 -05:00
19 lines
1.0 KiB
SQL
19 lines
1.0 KiB
SQL
-- Phase 1: Optimize temp ref lookups with covering index
|
|
-- This enables index-only scans, reducing query time by 2-3x
|
|
|
|
-- Drop redundant indexes that require table lookups
|
|
DROP INDEX IF EXISTS public.idx_submission_item_temp_refs_type;
|
|
DROP INDEX IF EXISTS public.idx_submission_item_temp_refs_item_type;
|
|
|
|
-- Create covering index that includes all frequently accessed columns
|
|
-- This allows PostgreSQL to satisfy queries entirely from the index without table access
|
|
CREATE INDEX idx_submission_item_temp_refs_covering
|
|
ON public.submission_item_temp_refs(submission_item_id)
|
|
INCLUDE (ref_type, ref_order_index);
|
|
|
|
COMMENT ON INDEX public.idx_submission_item_temp_refs_covering IS
|
|
'Covering index for temp ref lookups during approval processing. Enables index-only scans by including ref_type and ref_order_index columns.';
|
|
|
|
-- Verification: This query should now use index-only scan
|
|
-- EXPLAIN (ANALYZE, BUFFERS) SELECT ref_type, ref_order_index
|
|
-- FROM submission_item_temp_refs WHERE submission_item_id = 'some-uuid'; |