/* =============================================================================
 * =============================================================================
 * 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;