OPRAVA CHYBY update vyzadoval vacuum

[Pavel Stehule]

Zdravim

V nove verzi PostgreSQL 7.4 (beta) jiz neni nutne provadet vacuum po
opakovanem provedeni update neklicoveho sloupce (probiralo se to tu tak pred
pul rokem). Na mem prastarem 2xP160, 192MB se celkova doba pohybovala od 47
sekundami az po 52 sekund. VACUUM FULL vliv ma, ale nijak velky.

Pavel Stehule



Muzete si vyzkouset:

CREATE TABLE testinfo (idx integer, hodnota integer);


CREATE OR REPLACE FUNCTION fill_testinfo() RETURNS integer AS '
  DECLARE i integer; k integer; v integer;
BEGIN
  i := 0; TRUNCATE TABLE testinfo;
  WHILE i < 9000 LOOP
    k := CAST(random() * 10000 AS integer);
    IF NOT EXISTS(SELECT idx FROM testinfo WHERE idx = k) THEN
      v := CAST(random() * 10000 AS integer);
      INSERT INTO testinfo VALUES (k, v);
      i := i + 1;
    END IF;
  END LOOP;
  RETURN 1;
END' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_updatespeed() RETURNS interval AS '
  DECLARE i integer; j integer; t1 time; t2 time;
    t3 time; t4 time; d interval; k integer; v integer;
BEGIN
  t1 := CAST(CAST(timeofday() AS timestamp) AS time);
  FOR i IN 1..100 LOOP
    t3 := CAST(CAST(timeofday() AS timestamp) AS time);
    FOR j IN 1..400 LOOP
      k := CAST(random() * 10000 AS integer);
      v := CAST(random() * 10000 AS integer);
      UPDATE testinfo SET hodnota = k WHERE idx = k;
    END LOOP;
    t4 := CAST(CAST(timeofday() AS timestamp) AS time);
    d := t4 - t3;
    RAISE NOTICE ''%'', d;
  END LOOP;
  t2 := CAST(CAST(timeofday() AS timestamp) AS time);
  RETURN t2 - t1;
END ' LANGUAGE plpgsql;