/* ============================================================================= * ============================================================================= * Klassische Views * ============================================================================= * ============================================================================= */ DROP VIEW IF EXISTS haendler_liefert_ware_1; CREATE VIEW haendler_liefert_ware_1 (h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit) AS SELECT h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler JOIN liefert USING (h_id) JOIN ware USING (w_id) ; SELECT * FROM haendler_liefert_ware_1; /* ============================================================================= * ============================================================================= * Materialized Views * ============================================================================= * ============================================================================= */ DROP MATERIALIZED VIEW IF EXISTS haendler_liefert_ware_2; CREATE MATERIALIZED VIEW haendler_liefert_ware_2 (h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit) AS SELECT h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler JOIN liefert USING (h_id) JOIN ware USING (w_id) ; SELECT * FROM haendler_liefert_ware_2; CREATE INDEX haendler_liefert_ware_2_h_id ON haendler_liefert_ware_2 USING btree (h_id) ; SELECT * FROM haendler_liefert_ware_2 WHERE h_id = 1 ; /* ============================================================================= * ============================================================================= * WITH-Klausel: temporäre View für eine Anfrage * ============================================================================= * ============================================================================= */ WITH haendler_liefert_ware (h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit) AS -- NOT MATERIALIZED -- => Index-Zugriff möglich, gut für Optimierung, -- Query wird allerdings evtl. mehrfach ausgewertet, wenn die -- View mehrfach aufgerufen wird. (SELECT h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler JOIN liefert USING (h_id) JOIN ware USING (w_id) ) SELECT * FROM haendler_liefert_ware ; /* ============================================================================= * ============================================================================= * MATERIALIZED WITH-Klausel: temporäre View für eine Anfrage * ============================================================================= * ============================================================================= */ WITH haendler_liefert_ware (h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit) AS MATERIALIZED -- => kein Index-Zugriff möglich, schlecht für Optimierung, -- aber die Query wird nur einmal ausgeführt und das Ergebnis -- wird temporär gespeichert (SELECT h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler JOIN liefert USING (h_id) JOIN ware USING (w_id) ) SELECT * FROM haendler_liefert_ware ; /* ============================================================================= * ============================================================================= * Performance Tests * ============================================================================= * ============================================================================= */ -- Test-Daten erstellen INSERT INTO haendler(h_id, h_name, h_ortschaft) SELECT generate_series(100,10100), substr(md5(random()::text), 1, 30), substr(md5(random()::text), 1, 30) ON CONFLICT DO NOTHING; INSERT INTO ware(w_id, w_typ, w_bezeichnung) SELECT generate_series(100,10100), substr(md5(random()::text), 1, 30), substr(md5(random()::text), 1, 30) ON CONFLICT DO NOTHING; INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit) SELECT 100+(10000*random())::INT, 100+(10000*random())::INT, 1+(1000 *random())::INT, 1+(10 *random())::INT FROM generate_series(1,1000000) ON CONFLICT DO NOTHING; -- View 1 (NON MATERIALIZED) SELECT * FROM haendler_liefert_ware_1 WHERE h_id = 5555; SELECT * FROM haendler_liefert_ware_1 WHERE h_ortschaft = 'Königsbrunn'; EXPLAIN ANALYZE SELECT * FROM haendler_liefert_ware_1 WHERE h_id = 5555; --WHERE h_ortschaft = 'Königsbrunn'; -- View 2 (MATERIALIZED mit Index auf h_id) REFRESH MATERIALIZED VIEW haendler_liefert_ware_2; SELECT * FROM haendler_liefert_ware_2 WHERE h_id = 5555; SELECT * FROM haendler_liefert_ware_2 WHERE h_ortschaft = 'Königsbrunn'; EXPLAIN ANALYZE SELECT * FROM haendler_liefert_ware_2 WHERE h_id = 5555; --WHERE h_ortschaft = 'Königsbrunn'; -- View 2 (MATERIALIZED ohne Index) DROP INDEX IF EXISTS haendler_liefert_ware_2_h_id; EXPLAIN ANALYZE SELECT * FROM haendler_liefert_ware_2 WHERE h_id = 5555; -- With-Klausel (NOT MATERIALIZED) EXPLAIN ANALYZE WITH haendler_liefert_ware (h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit) AS --NOT MATERIALIZED (SELECT h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler JOIN liefert USING (h_id) JOIN ware USING (w_id) ) SELECT * FROM haendler_liefert_ware WHERE h_id = 5555; -- With-Klausel (MATERIALIZED) EXPLAIN ANALYZE WITH haendler_liefert_ware (h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit) AS MATERIALIZED (SELECT h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit FROM haendler JOIN liefert USING (h_id) JOIN ware USING (w_id) ) SELECT * FROM haendler_liefert_ware WHERE h_id = 5555;