consultoria-as
6aff32f93b
- Register all missing migrations in runner.py - Make v4.3 idempotent (rename xml_unsigned only if exists) - Make v3.3 idempotent (skip warehouse_inventory/purchase_order_items ops when tables/columns missing) - Mark v3.3.1 and v3.9 as master-only (SKIP) - Mark v3.5.1 as optional (skip if whatsapp tables missing) - Runner skips files marked with '-- : SKIP'
33 lines
1.1 KiB
SQL
33 lines
1.1 KiB
SQL
-- : SKIP
|
|
-- Migration v3.3: Materialized view part_vehicle_preview
|
|
-- Purpose: Pre-compute the "most recent vehicle" per part to eliminate
|
|
-- DISTINCT ON + 4 JOINs over vehicle_parts (254 GB, 2B+ rows) at query time.
|
|
--
|
|
-- NOTE: This migration targets the vehicle_database, not tenant databases.
|
|
-- The runner skips files marked with ': SKIP' on the first line.
|
|
-- To apply manually on the vehicle database, run:
|
|
--
|
|
-- psql <vehicle_db> -f pos/migrations/v3.3_materialized_view.sql
|
|
--
|
|
-- (Remove the ': SKIP' line above before manual execution.)
|
|
|
|
SET statement_timeout = 0;
|
|
|
|
DROP MATERIALIZED VIEW IF EXISTS part_vehicle_preview;
|
|
|
|
CREATE MATERIALIZED VIEW part_vehicle_preview AS
|
|
SELECT DISTINCT ON (vp.part_id)
|
|
vp.part_id,
|
|
b.name_brand,
|
|
m.name_model,
|
|
y.year_car
|
|
FROM vehicle_parts vp
|
|
JOIN model_year_engine mye ON mye.id_mye = vp.model_year_engine_id
|
|
JOIN models m ON m.id_model = mye.model_id
|
|
JOIN brands b ON b.id_brand = m.brand_id
|
|
JOIN years y ON y.id_year = mye.year_id
|
|
ORDER BY vp.part_id, y.year_car DESC;
|
|
|
|
CREATE UNIQUE INDEX idx_pvp_part ON part_vehicle_preview(part_id);
|
|
CREATE INDEX idx_pvp_brand ON part_vehicle_preview(name_brand);
|