39 lines
1.5 KiB
SQL
39 lines
1.5 KiB
SQL
-- topology_api/migrations/003_indexes.sql
|
|
-- Performance indexes for snapshot queries
|
|
|
|
-- missions lookup
|
|
CREATE INDEX IF NOT EXISTS idx_missions_base_status
|
|
ON topology.missions(base_id, occurrence_status);
|
|
|
|
-- events time ordering
|
|
CREATE INDEX IF NOT EXISTS idx_events_mission_time
|
|
ON topology.mission_events(mission_id, effective_at DESC, event_order DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_events_effective_time
|
|
ON topology.mission_events(effective_at DESC);
|
|
|
|
-- components lookup
|
|
CREATE INDEX IF NOT EXISTS idx_components_base
|
|
ON topology.components(base_id);
|
|
|
|
-- versions time range
|
|
CREATE INDEX IF NOT EXISTS idx_versions_component_time
|
|
ON topology.component_versions(component_id, valid_from DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_versions_current
|
|
ON topology.component_versions(component_id) WHERE valid_to IS NULL;
|
|
|
|
-- states time range
|
|
CREATE INDEX IF NOT EXISTS idx_states_component_kind_time
|
|
ON topology.component_states(component_id, state_kind, valid_from DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_states_current
|
|
ON topology.component_states(component_id, state_kind) WHERE valid_to IS NULL;
|
|
|
|
-- connections time range
|
|
CREATE INDEX IF NOT EXISTS idx_connections_base_time
|
|
ON topology.topology_connections(base_id, valid_from DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_connections_current
|
|
ON topology.topology_connections(base_id) WHERE valid_to IS NULL;
|
|
|
|
-- ports
|
|
CREATE INDEX IF NOT EXISTS idx_ports_component
|
|
ON topology.ports(component_id);
|