Przejdź do zawartości

Procedury składowane w PostgreSQL/Planer

Dodaj linki
Z Wikibooks, biblioteki wolnych podręczników.

Plany funkcji pisanych w PL/pgSQL są pamiętane, plany te są parametryzowane ze względu na zmienne i argumenty funkcji, więc niekiedy wydajność wykonywanych zapytań może być gorsza niż gdyby wartości były wpisane wprost.

Natomiast tworząc procedurę w PL/pgSQL lub SQL można mieć w pewnym stopniu wpływ na to, jak będzie wywoływana podczas wykonywania zapytań, które ją zawierają. Poniżej zaznaczono istotne parametry polecenia CREATE FUNCTION. 

CREATE FUNCTION nazwa ( lista_argumentów ) RETURNS typ zwracanej wartości [...]
  -- rodzaj funkcji
  | IMMUTABLE | STABLE | VOLATILE
  -- czy wywoływać, gdy jeden z argumentów jest NULL?
  | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
  -- szacowany czas wykonania
  | COST czas
  -- szacowana liczba wyjściowych
  | ROWS liczba wierszy

Rodzaje funkcji

[edytuj]

Przy deklarowaniu procedury można określić, jak jej wykonanie wpływa na system, co pomaga optymalizatorowi lepiej zaplanować wykonanie zapytań, w szczególności nie wywoływać niepotrzebnie funkcji.

Są trzy rodzaje procedur:

  • VOLATILE - procedura ma efekty uboczne, tj. zmienia bazę danych, np. używane są wprost zapytania w rodzaju UPDATE, DELETE, bądź wywoływane inne procedury tego typu. Domyślnie, pesymistycznie, przyjmowany jest taki rodzaj.
  • STABLE - procedura nie zmienia bazy danych i zawsze zwraca ten sam wynik pod warunkiem, że dane się nie zmieniły. Np. funkcja, która wyłącznie wyszukuje pracowników wg numeru PESEL zwróci zawsze ten sam zestaw rekordów jeśli pomiędzy jej wywołaniami nie zmieniono tabeli pracowników.
  • IMMUTABLE - procedura nie zmienia bazy danych i wynik przez nią zwracany nie zależy od zawartości bazy. Np. funkcja wykonująca operacje arytmetyczne ma taką cechę.
Uwaga!
 Uwaga!
PostgreSQL nie sprawdza na etapie kompilacji zbyt dokładnie, czy funkcja spełnia warunki nakładane przez deklarację, należy więc samemu dbać aby np. funkcja zadeklarowana jako STABLE nie wywoływała procedur VOLATILE. Błędy mogą pojawić się dopiero w trakcie wykonywania kodu.

Przykład

[edytuj]

Wbudowana funkcja generate_series zwraca kolejne liczby z podanego zakresu; w przykładzie z 1..10.

Zostaną utworzone dwie identyczne funkcje, różniące się jedynie rodzajem. Dla pierwszego zapytania funkcja test_immutable wykona się dokładnie raz, w drugim zapytaniu funkcja test_stable wywołana zostanie dla każdego wiersza, czyli 10 razy. 

CREATE OR REPLACE FUNCTION test_immutable() RETURNS integer AS $$
	BEGIN
		RAISE NOTICE 'test_immutable()';
		RETURN 5;
	END
$$ LANGUAGE 'plpgsql'
   IMMUTABLE;

SELECT i FROM generate_series(1, 10) g(i) WHERE i > test_immutable();

psql:1.sql:18: NOTICE:  test_immutable()
 i  
----
  6
  7
  8
  9
 10
(5 rows)

CREATE OR REPLACE FUNCTION test_stable() RETURNS integer AS $$
	BEGIN
		RAISE NOTICE 'test_stable()';
		RETURN 5;
	END
$$ LANGUAGE 'plpgsql'
   STABLE;

SELECT i FROM generate_series(1, 10) g(i) WHERE i > test_stable();

psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
psql:1.sql:21: NOTICE:  test_stable()
 i  
----
  6
  7
  8
  9
 10
(5 rows)

Czy wywoływać, gdy jeden z argumentów jest NULL?

[edytuj]

Funkcję wystarczy utworzyć jako STRICT (RETURN NULL ON NULL INPUT), wówczas gdy przynajmniej jeden argument jest pusty, automatycznie zwracany jest NULL, bez wywoływania funkcji.


Informacja
 Może to mieć bardzo istotny wpływ na wydajność procedury, wynik jest znany od razu. Odpada cały koszt wywołania funkcji, w szczególności tworzenie transakcji.

Z punktu widzenia kodu, zamiast pisać 

CREATE FUNCTION foo(x integer, y text, z real) AS $$\
	BEGIN
		IF x IS NULL OR y IS NULL OR z IS NULL THEN
			RETURN NULL;
		END IF;
		
		-- treść procedury
	END;
$$ LANGUAGE 'plpgsql';

wystarczy 

CREATE FUNCTION foo(x integer, y text, z real) AS $$\
	BEGIN
		-- treść procedury
	END;
$$ LANGUAGE 'plpgsql' STRICT;

STRICT a zmienna liczba argumentów (VARIADIC)

[edytuj]

Procedury ze zmienną liczbą argumentów są uruchamiane nawet jeśli na liście argumentów VARIADIC są wartości puste. Funkcja nie zostanie uruchomiona tylko jeśli lista będzie mieć wartość NULL. 

CREATE OR REPLACE FUNCTION test_strict(integer, VARIADIC text[]) RETURNS integer AS $$
    BEGIN
        RETURN array_length($2, 1);
    END;
$$ LANGUAGE 'plpgsql'
   STRICT
;

-- funkcja zostanie wywołana!
SELECT test_strict(3, NULL, NULL, NULL);

-- dopiero taki zapis powoduje honorowanie STRICT
SELECT test_strict(4, VARIADIC NULL::text[]);

Szacowany czas wykonania procedury

[edytuj]

Wartość COST czas podawana przy tworzeniu procedury musi być liczbą rzeczywistą nieujemną.

Jeśli procedurę charakteryzuje duży czas wykonania, optymalizator zapytań może przekształcić tak warunki logiczne - ale tylko w klauzuli WHERE - żeby procedura nie była wywoływana za każdym razem. Wykorzystuje się tutaj leniwe wartościowanie wyrażeń logicznych (ang. short circuit evaluation) zawierających operator AND.

Przykład

[edytuj]

Najpierw zostaną utworzone dwie funkcjonalnie identyczne procedury, różniące się jedynie kosztem. 

CREATE OR REPLACE FUNCTION predykat_szybki(integer) RETURNS boolean
	LANGUAGE 'plpgsql'
	COST 0.01
AS $$
	BEGIN
		RETURN $1 > 90;
	END;
$$;

CREATE OR REPLACE FUNCTION predykat_powolny(integer) RETURNS boolean
	LANGUAGE 'plpgsql'
	COST 1000
AS $$
	BEGIN
		RETURN $1 > 90;
	END;
$$;

Po czym wykonane zostaną proste zapytania: 

CREATE TABLE test (x integer);
INSERT INTO test (SELECT 100*random() FROM generate_series(1, 1000));

-- 1.
EXPLAIN ANALYZE SELECT COUNT(*) FROM test
 WHERE predykat_szybki(x) AND x > 30;

-- 2.
EXPLAIN ANALYZE SELECT COUNT(*) FROM test
 WHERE predykat_powolny(x) AND x > 30;

Wynik EXPLAIN ANALYZE dla 1. przypadku 

 Aggregate  (cost=43.39..43.40 rows=1 width=0) (actual time=2.757..2.758 rows=1 loops=1)
   ->  Seq Scan on test  (cost=0.00..40.06 rows=1333 width=0) (actual time=0.050..2.499 rows=706 loops=1)
         Filter: (predykat_szybki(x) AND (x > 30))
  Total runtime: 2.181 ms

oraz dla 2. przypadku 

 Aggregate  (cost=6040.67..6040.68 rows=1 width=0) (actual time=1.639..1.639 rows=1 loops=1)
   ->  Seq Scan on test  (cost=0.00..6040.00 rows=267 width=0) (actual time=0.072..1.610 rows=95 loops=1)
         Filter: ((x > 30) AND predykat_powolny(x))
 Total runtime: 1.666 ms

Najistotniejszy jest przedostatni wiersz - Filter. Dla 1. przypadku najpierw wołana jest funkcja predykat_szybki, następnie wykonywane porównanie. Dla 2. przypadku optymalizator zmienił kolejność ewaluacji warunków, najpierw wykonywane jest porównanie i dopiero gdy okazuje się prawdziwe, wywoływana zostaje funkcja predykat_powolny.

Niewielki zysk z rzadszego wołania funkcji widać w czasie wykonania zapytań.

Szacowana liczba zwracanych wierszy

[edytuj]

Wartość ROWS liczba wierszy podawana przy tworzeniu procedury musi być liczbą całkowitą.

Od liczby wierszy zależy plan zapytania, np. zostanie wykorzystany indeks (dla dużej liczby wierszy) lub też sekwencyjne skanowanie (dla małej).

Przykład

[edytuj]
CREATE OR REPLACE FUNCTION indeksy1() RETURNS SETOF integer AS $$
	BEGIN
		RETURN NEXT (SELECT 123);
	END;
$$ LANGUAGE 'plpgsql'
   ROWS 1;

CREATE OR REPLACE FUNCTION indeksy2() RETURNS SETOF integer AS $$
	BEGIN
		RETURN NEXT (SELECT 123);
	END;
$$ LANGUAGE 'plpgsql'
   ROWS 1000000;

Planer bierze pod uwagę te wartości tworząc złączenia (wyniki dla PostgerSQL 9.1). 

CREATE TABLE test (x integer);
INSERT INTO test (SELECT i FROM generate_series(1, 10000) AS i);

-- zapytanie 1.
EXPLAIN SELECT sum(x) FROM test WHERE x IN (SELECT i FROM indeksy1() AS i);

-- zapytanie 2.
EXPLAIN SELECT sum(x) FROM test WHERE x IN (SELECT i FROM indeksy2() AS i);

Plan dla zapytania 1. 

                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Aggregate  (cost=184.76..184.77 rows=1 width=4)
   ->  Hash Join  (cost=0.29..172.76 rows=4800 width=4)
         Hash Cond: (test.x = i.i)
         ->  Seq Scan on test  (cost=0.00..136.00 rows=9600 width=4)
         ->  Hash  (cost=0.27..0.27 rows=1 width=4)
               ->  HashAggregate  (cost=0.26..0.27 rows=1 width=4)
                     ->  Function Scan on indeksy1 i  (cost=0.25..0.26 rows=1 width=4)

Plan dla zapytania 2, jak widać wartość ROWS (1000000) została uwzględniana w koszcie. 

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Aggregate  (cost=29744.65..29744.66 rows=1 width=4)
   ->  Hash Semi Join  (cost=25918.25..29732.65 rows=4800 width=4)
         Hash Cond: (test.x = i.i)
         ->  Seq Scan on test  (cost=0.00..136.00 rows=9600 width=4)
         ->  Hash  (cost=10000.25..10000.25 rows=1000000 width=4)
               ->  Function Scan on indeksy2 i  (cost=0.25..10000.25 rows=1000000 width=4)

Zamiana rekordu na kolumny

[edytuj]

W PostgrSQL można zamienić pola dowolnego rekordu na osobne kolumny: 

SELECT (rekord).* FROM ...

Problem pojawia się, gdy rekord zwraca funkcja, tj. używany jest zapis SELECT (funkcja()).* FROM... - w takim przypadku zostanie wywołana dla każdego pola. Może to znacząco rzutować na wydajność, nie mówiąc o przypadku, gdy funkcja nie jest stabilna, tzn. może zwracać różne wyniki dla tych samych argumentów w kolejnych wywołaniach.

Aby temu zapobiec można zastosować nieudokumentowany trik: 

SELECT (f).* FROM (SELECT funkcja() AS f FROM ... OFFSET 0) AS tmp

Kluczowe jest dodanie OFFSET 0, które powoduje, że planer wstawia pojedyncze wywołanie funkcji.

Źródło: „https://pl.wikibooks.org/w/index.php?title=Procedury_składowane_w_PostgreSQL/Planer&oldid=399168”