PostgreSQL Skripts für „Die Where-Klausel“

Der Gleichheitsoperator

Künstliche Schlüssel

Das folgende Skript erstellt die EMPLOYEES Tabelle mit 1000 Einträgen.

CREATE TABLE employees (
   employee_id   NUMERIC       NOT NULL,
   first_name    VARCHAR(1000) NOT NULL,
   last_name     VARCHAR(1000) NOT NULL,
   date_of_birth DATE                   ,
   phone_number  VARCHAR(1000) NOT NULL,
   junk          CHAR(1000)             ,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

CREATE FUNCTION random_string(minlen NUMERIC, maxlen NUMERIC)
RETURNS VARCHAR(1000)
AS
$$
DECLARE
  rv VARCHAR(1000) := '';
  i  INTEGER := 0;
  len INTEGER := 0;
BEGIN
  IF maxlen < 1 OR minlen < 1 OR maxlen < minlen THEN
    RETURN rv;
  END IF;

  len := floor(random()*(maxlen-minlen)) + minlen;

  FOR i IN 1..floor(len) LOOP
    rv := rv || chr(97+CAST(random() * 25 AS INTEGER));
  END LOOP;
  RETURN rv;
END;
$$ LANGUAGE plpgsql;

INSERT INTO employees (employee_id,  first_name,
                       last_name,    date_of_birth, 
                       phone_number, junk)
SELECT GENERATE_SERIES
     , initcap(lower(random_string(2, 8)))
     , initcap(lower(random_string(2, 8)))
     , CURRENT_DATE - CAST(floor(random() * 365 * 10 + 40 * 365) AS NUMERIC) * INTERVAL '1 DAY'
     , CAST(floor(random() * 9000 + 1000) AS NUMERIC)
     , 'junk'
  FROM GENERATE_SERIES(1, 1000);

UPDATE employees 
   SET first_name='MARKUS', 
       last_name='WINAND'
 WHERE employee_id=123;

VACUUM ANALYZE employees;

Bemerkungen:

• Die JUNK-Spalte wird verwendet, um eine realistische Zeilenlänge zu erhalten. Im Gegensatz zu VARCHAR belegt der CHAR-Datentyp immer den angegebenen Platz ‒ 1000 Byte in diesem Fall. Ohne diese Spalte wäre die Tabelle unrealistisch klein und viele Beispiele würden nicht funktionieren.

• Die Tabelle wird mit Zufallsdaten gefüllt. Danach wird mein Eintrag durch ein update geändert.

• Die Tabellen und Index Statistiken werden erstellt, sodass der Optimizer einen Überblick über den Tabelleninhalt hat.

Zusammengesetzte Schlüssel

Das nächste Skript ändert die EMPLOYEES Tabelle so, dass sie die Situation nach der Zusammenlegung mit „Sehr Große Firma“ widerspiegelt.

-- add subsidiary_id and update existing records
ALTER TABLE employees ADD subsidiary_id NUMERIC;;
UPDATE      employees SET subsidiary_id = 30;;
ALTER TABLE employees ALTER COLUMN subsidiary_id SET NOT NULL;;

-- change the PK
ALTER TABLE employees DROP CONSTRAINT employees_pk;;
ALTER TABLE employees ADD CONSTRAINT employees_pk 
      PRIMARY KEY (employee_id, subsidiary_id);;

-- generate more records (Very Big Company)
INSERT INTO employees (employee_id,  first_name,
                       last_name,    date_of_birth, 
                       phone_number, subsidiary_id, junk)
SELECT GENERATE_SERIES
     , initcap(lower(random_string(2, 8)))
     , initcap(lower(random_string(2, 8)))
     , CURRENT_DATE - CAST(floor(random() * 365 * 10 + 40 * 365) AS NUMERIC) * INTERVAL '1 DAY'
     , CAST(floor(random() * 9000 + 1000) AS NUMERIC)
     , CAST(floor(random() * (generate_series)/9000*29) AS NUMERIC)
     , 'junk'
  FROM GENERATE_SERIES(1, 9000);;

VACUUM ANALYZE employees;

Bemerkungen:

• Der Primärschlüssel wurde um die SUBSIDIARY_ID Spalte erweitert. Die EMPLOYEE_ID Spalte bleibt an erster Stelle.

• Die neuen Einträge werden zufällig auf die Töchter 1 bis 29 aufgeteilt.

• Die Tabelle und Indizes werden neu analysiert, damit der Optimizer das neue Mengengerüst kennt.

Das nächste Skript erstellt den Index auf SUBSIDIARY_ID, um Abfragen nach allen Angestellten eines bestimmten Subunternehmens zu unterstützen:

CREATE INDEX emp_sub_id ON employees (subsidiary_id);

Obwohl der Index gute Antwortzeiten ermöglicht, ist es besser den Primärschlüssel-Index umzubauen, sodass er diese Abfrage ebenfalls unterstützen kann:

-- use tmp index to support the PK
CREATE UNIQUE INDEX employee_pk_tmp 
    ON employees (subsidiary_id, employee_id);;

 ALTER TABLE employees 
   ADD CONSTRAINT employees_pk_tmp
UNIQUE (subsidiary_id, employee_id);;

ALTER TABLE employees
 DROP CONSTRAINT employees_pk;;

ALTER TABLE employees
  ADD CONSTRAINT employees_pk
      PRIMARY KEY (subsidiary_id, employee_id);;

ALTER TABLE employees
 DROP CONSTRAINT employees_pk_tmp;;

-- drop old indexes
DROP INDEX employee_pk_tmp;;
DROP INDEX emp_sub_id;;

Bemerkungen:

• Ein temporärer Index wird angelegt, um temporär einen Uniqe-Constraint zu unterstützen.

• Der alte Primärschlüssel kann gelöscht, und durch die neue Definition ersetzt werden.

• Der Temporärindex, und auch der Index auf SUBSIDIARY_ID, können gelöscht werden.

Funktionen

Groß- und Kleinschreibung ignorieren

Die zufälligen Einträge wurden bereits mit Groß-, Kleinschreibung angelegt. Nur mein Eintrag muss korrigiert werden.

UPDATE employees 
   SET first_name = 'Markus'
     , last_name  = 'Winand'
 WHERE employee_id   = 123
   AND subsidiary_id = 30;

Den Funktionsbasierten-Index anlegen:

CREATE INDEX emp_up_name
    ON employees (UPPER(last_name) varchar_pattern_ops);;
DROP INDEX emp_name;;

VACUUM ANALYZE employees;;

Benutzerdefinierte Funktionen

Die folgende PL/pgSQL-Funktion berechnet das Alter. Die Verwendung dieser Funktion in einem Index ist unzulässig.

CREATE FUNCTION get_age(date_of_birth DATE) 
RETURNS NUMERIC
AS
$$
BEGIN
    RETURN DATE_PART('year', AGE(date_of_birth));
END;
$$ LANGUAGE plpgsql;

CREATE INDEX invalid ON EMPLOYEES (get_age(date_of_birth));

Das CREATE INDEX Statement sollte den Fehler „functions in index expression must be marked IMMUTABLE“ liefern.