EDYTOWAĆ:
Z przeprosinami muszę wycofać moje twierdzenie, że zaakceptowana odpowiedź nie zawsze jest poprawna - stwierdza, że widok jest zawsze identyczny z tym samym, napisanym jako podzapytanie. Myślę, że to bezdyskusyjne i myślę, że teraz wiem, co się dzieje w moim przypadku.
Myślę też, że jest lepsza odpowiedź na pierwotne pytanie.
Pierwotne pytanie dotyczy tego, czy korzystanie z widoków powinno być przewodnią praktyką (w przeciwieństwie do na przykład powtarzania SQL w procedurach, które mogą wymagać utrzymania dwa lub więcej razy).
Moja odpowiedź brzmiałaby: „nie, jeśli zapytanie używa funkcji okna lub czegokolwiek innego, co powoduje, że optymalizator traktuje zapytanie inaczej, gdy staje się ono podzapytaniem, ponieważ sam akt tworzenia podzapytania (reprezentowanego jako widok lub nie) może obniżyć wydajność jeśli filtrujesz za pomocą parametrów w czasie wykonywania.
Złożoność mojej funkcji okna jest niepotrzebna. Wyjaśnij plan:
SELECT DISTINCT ts.train_service_key,
pc.assembly_key,
count(*) OVER
(PARTITION BY ts.train_service_key) AS train_records
FROM staging.train_service ts
JOIN staging.portion_consist pc
USING (ds_code, train_service_key)
WHERE assembly_key = '185132';
jest o wiele tańszy niż w tym przypadku:
SELECT *
FROM (SELECT DISTINCT ts.train_service_key,
pc.assembly_key,
count(*) OVER
(PARTITION BY ts.train_service_key) AS train_records
FROM staging.train_service ts
JOIN staging.portion_consist pc
USING (ds_code, train_service_key)) AS query
WHERE assembly_key = '185132';
Mam nadzieję, że jest to bardziej szczegółowe i pomocne.
Z mojego ostatniego doświadczenia (powodującego, że znalazłem to pytanie), powyższa zaakceptowana odpowiedź jest nieprawidłowa we wszystkich okolicznościach. Mam stosunkowo proste zapytanie, które obejmuje funkcję okna:
SELECT DISTINCT ts.train_service_key,
pc.assembly_key,
dense_rank() OVER (PARTITION BY ts.train_service_key
ORDER BY pc.through_idx DESC, pc.first_portion ASC,
((CASE WHEN (NOT ts.primary_direction)
THEN '-1' :: INTEGER
ELSE 1
END) * pc.first_seq)) AS coach_block_idx
FROM (staging.train_service ts
JOIN staging.portion_consist pc USING (ds_code, train_service_key))
Jeśli dodam ten filtr:
where assembly_key = '185132'
Wyjaśniam plan, który otrzymuję:
QUERY PLAN
Unique (cost=11562.66..11568.77 rows=814 width=43)
-> Sort (cost=11562.66..11564.70 rows=814 width=43)
Sort Key: ts.train_service_key, (dense_rank() OVER (?))
-> WindowAgg (cost=11500.92..11523.31 rows=814 width=43)
-> Sort (cost=11500.92..11502.96 rows=814 width=35)
Sort Key: ts.train_service_key, pc.through_idx DESC, pc.first_portion, ((CASE WHEN (NOT ts.primary_direction) THEN '-1'::integer ELSE 1 END * pc.first_seq))
-> Nested Loop (cost=20.39..11461.57 rows=814 width=35)
-> Bitmap Heap Scan on portion_consist pc (cost=19.97..3370.39 rows=973 width=38)
Recheck Cond: (assembly_key = '185132'::text)
-> Bitmap Index Scan on portion_consist_assembly_key_index (cost=0.00..19.72 rows=973 width=0)
Index Cond: (assembly_key = '185132'::text)
-> Index Scan using train_service_pk on train_service ts (cost=0.43..8.30 rows=1 width=21)
Index Cond: ((ds_code = pc.ds_code) AND (train_service_key = pc.train_service_key))
Wykorzystuje się w tym indeks klucza podstawowego w tabeli obsługi pociągu i nieunikalny indeks w tabelipart_consist. Wykonuje się w 90ms.
Utworzyłem widok (wklejając go tutaj, aby był absolutnie jasny, ale dosłownie jest to zapytanie w widoku):
CREATE OR REPLACE VIEW staging.v_unit_coach_block AS
SELECT DISTINCT ts.train_service_key,
pc.assembly_key,
dense_rank() OVER (PARTITION BY ts.train_service_key
ORDER BY pc.through_idx DESC, pc.first_portion ASC, (
(CASE
WHEN (NOT ts.primary_direction)
THEN '-1' :: INTEGER
ELSE 1
END) * pc.first_seq)) AS coach_block_idx
FROM (staging.train_service ts
JOIN staging.portion_consist pc USING (ds_code, train_service_key))
Kiedy pytam ten widok z identycznym filtrem:
select * from staging.v_unit_coach_block
where assembly_key = '185132';
Oto plan wyjaśniania:
QUERY PLAN
Subquery Scan on v_unit_coach_block (cost=494217.13..508955.10 rows=3275 width=31)
Filter: (v_unit_coach_block.assembly_key = '185132'::text)
-> Unique (cost=494217.13..500767.34 rows=655021 width=43)
-> Sort (cost=494217.13..495854.68 rows=655021 width=43)
Sort Key: ts.train_service_key, pc.assembly_key, (dense_rank() OVER (?))
-> WindowAgg (cost=392772.16..410785.23 rows=655021 width=43)
-> Sort (cost=392772.16..394409.71 rows=655021 width=35)
Sort Key: ts.train_service_key, pc.through_idx DESC, pc.first_portion, ((CASE WHEN (NOT ts.primary_direction) THEN '-1'::integer ELSE 1 END * pc.first_seq))
-> Hash Join (cost=89947.40..311580.26 rows=655021 width=35)
Hash Cond: ((pc.ds_code = ts.ds_code) AND (pc.train_service_key = ts.train_service_key))
-> Seq Scan on portion_consist pc (cost=0.00..39867.86 rows=782786 width=38)
-> Hash (cost=65935.36..65935.36 rows=1151136 width=21)
-> Seq Scan on train_service ts (cost=0.00..65935.36 rows=1151136 width=21)
Robi to pełne skanowanie obu tabel i zajmuje 17 sekund.
Dopóki się z tym nie spotkałem, swobodnie korzystałem z widoków w PostgreSQL (po zrozumieniu szeroko rozpowszechnionych poglądów wyrażonych w zaakceptowanej odpowiedzi). W szczególności unikałbym korzystania z widoków, jeśli potrzebuję filtrowania wstępnego agregacji, dla którego używałbym funkcji zwracających zestaw.
Wiem również, że CTE w PostgreSQL są ściśle oceniane osobno, z założenia, więc nie używam ich w taki sam sposób, jak na przykład w SQL Server, gdzie wydają się być zoptymalizowane jako podkwerendy.
Moja odpowiedź brzmi zatem: są przypadki, w których widoki nie działają dokładnie tak, jak zapytanie, na którym są oparte, dlatego zaleca się ostrożność. Korzystam z Amazon Aurora opartej na PostgreSQL 9.6.6.
SELECT * FROM my_view WHERE my_column = 'blablabla';Podczas gdy drugi dotyczy korzystania z widoków, aby model danych był przezroczysty dla aplikacji, która go używa. Pierwsze źródła wskazują, że należy uwzględnić filtrWHERE my_column = 'blablabla'w definicji widoku, ponieważ zapewnia to lepszy plan wykonania.