Ustawiać
Buduję na @ konfiguracji Jacka , aby ułatwić ludziom śledzić i porównywać. Testowane z PostgreSQL 9.1.4 .
CREATE TABLE lexikon (
lex_id serial PRIMARY KEY
, word text
, frequency int NOT NULL -- we'd need to do more if NULL was allowed
, lset int
);
INSERT INTO lexikon(word, frequency, lset)
SELECT 'w' || g -- shorter with just 'w'
, (1000000 / row_number() OVER (ORDER BY random()))::int
, g
FROM generate_series(1,1000000) g
Odtąd wybieram inną trasę:
ANALYZE lexikon;
Stolik pomocniczy
To rozwiązanie nie dodaje kolumn do oryginalnej tabeli, potrzebuje jedynie niewielkiej tabeli pomocniczej. Umieściłem go w schemacie public, użyj dowolnego wybranego schematu.
CREATE TABLE public.lex_freq AS
WITH x AS (
SELECT DISTINCT ON (f.row_min)
f.row_min, c.row_ct, c.frequency
FROM (
SELECT frequency, sum(count(*)) OVER (ORDER BY frequency DESC) AS row_ct
FROM lexikon
GROUP BY 1
) c
JOIN ( -- list of steps in recursive search
VALUES (400),(1600),(6400),(25000),(100000),(200000),(400000),(600000),(800000)
) f(row_min) ON c.row_ct >= f.row_min -- match next greater number
ORDER BY f.row_min, c.row_ct, c.frequency DESC
)
, y AS (
SELECT DISTINCT ON (frequency)
row_min, row_ct, frequency AS freq_min
, lag(frequency) OVER (ORDER BY row_min) AS freq_max
FROM x
ORDER BY frequency, row_min
-- if one frequency spans multiple ranges, pick the lowest row_min
)
SELECT row_min, row_ct, freq_min
, CASE freq_min <= freq_max
WHEN TRUE THEN 'frequency >= ' || freq_min || ' AND frequency < ' || freq_max
WHEN FALSE THEN 'frequency = ' || freq_min
ELSE 'frequency >= ' || freq_min
END AS cond
FROM y
ORDER BY row_min;
Tabela wygląda następująco:
row_min | row_ct | freq_min | cond
--------+---------+----------+-------------
400 | 400 | 2500 | frequency >= 2500
1600 | 1600 | 625 | frequency >= 625 AND frequency < 2500
6400 | 6410 | 156 | frequency >= 156 AND frequency < 625
25000 | 25000 | 40 | frequency >= 40 AND frequency < 156
100000 | 100000 | 10 | frequency >= 10 AND frequency < 40
200000 | 200000 | 5 | frequency >= 5 AND frequency < 10
400000 | 500000 | 2 | frequency >= 2 AND frequency < 5
600000 | 1000000 | 1 | frequency = 1
Ponieważ kolumna condbędzie dalej używana w dynamicznym SQL, musisz zabezpieczyć tę tabelę . Zawsze kwalifikuj się do schematu tabeli, jeśli nie masz pewności co do odpowiedniego prądu search_pathi cofnij uprawnienia do zapisu z public(i jakiejkolwiek innej niezaufanej roli):
REVOKE ALL ON public.lex_freq FROM public;
GRANT SELECT ON public.lex_freq TO public;
Tabela lex_freqsłuży trzem celom:
- Automatycznie twórz potrzebne indeksy częściowe .
- Podaj kroki dla funkcji iteracyjnej.
- Meta informacje do strojenia.
Indeksy
Ta DOinstrukcja tworzy wszystkie potrzebne indeksy:
DO
$$
DECLARE
_cond text;
BEGIN
FOR _cond IN
SELECT cond FROM public.lex_freq
LOOP
IF _cond LIKE 'frequency =%' THEN
EXECUTE 'CREATE INDEX ON lexikon(lset) WHERE ' || _cond;
ELSE
EXECUTE 'CREATE INDEX ON lexikon(lset, frequency DESC) WHERE ' || _cond;
END IF;
END LOOP;
END
$$
Wszystkie te częściowe indeksy razem obejmują tabelę jeden raz. Są mniej więcej tego samego rozmiaru co jeden podstawowy indeks na całej tabeli:
SELECT pg_size_pretty(pg_relation_size('lexikon')); -- 50 MB
SELECT pg_size_pretty(pg_total_relation_size('lexikon')); -- 71 MB
Dotychczas tylko 21 MB indeksów dla tabeli 50 MB.
Tworzę większość indeksów częściowych (lset, frequency DESC). Druga kolumna pomaga tylko w szczególnych przypadkach. Ponieważ obie zaangażowane kolumny są tego samego typu integer, ze względu na specyfikę wyrównywania danych w połączeniu z MAXALIGN w PostgreSQL, druga kolumna nie powiększa indeksu. To niewielka wygrana za niewielką opłatą.
Nie ma sensu tego robić w przypadku indeksów częściowych, które obejmują tylko jedną częstotliwość. Te są po prostu włączone (lset). Utworzone indeksy wyglądają następująco:
CREATE INDEX ON lexikon(lset, frequency DESC) WHERE frequency >= 2500;
CREATE INDEX ON lexikon(lset, frequency DESC) WHERE frequency >= 625 AND frequency < 2500;
-- ...
CREATE INDEX ON lexikon(lset, frequency DESC) WHERE frequency >= 2 AND frequency < 5;
CREATE INDEX ON lexikon(lset) WHERE freqency = 1;
Funkcjonować
Funkcja jest nieco podobna stylem do rozwiązania @ Jacka:
CREATE OR REPLACE FUNCTION f_search(_lset_min int, _lset_max int, _limit int)
RETURNS SETOF lexikon
$func$
DECLARE
_n int;
_rest int := _limit; -- init with _limit param
_cond text;
BEGIN
FOR _cond IN
SELECT l.cond FROM public.lex_freq l ORDER BY l.row_min
LOOP
-- RAISE NOTICE '_cond: %, _limit: %', _cond, _rest; -- for debugging
RETURN QUERY EXECUTE '
SELECT *
FROM public.lexikon
WHERE ' || _cond || '
AND lset >= $1
AND lset <= $2
ORDER BY frequency DESC
LIMIT $3'
USING _lset_min, _lset_max, _rest;
GET DIAGNOSTICS _n = ROW_COUNT;
_rest := _rest - _n;
EXIT WHEN _rest < 1;
END LOOP;
END
$func$ LANGUAGE plpgsql STABLE;
Kluczowe różnice:
dynamiczny SQL z RETURN QUERY EXECUTE.
Gdy wykonujemy kolejne kroki, beneficjentem może być inny plan zapytań. Plan zapytań dla statycznego SQL jest generowany raz, a następnie ponownie wykorzystywany - co może zaoszczędzić trochę narzutu. Ale w tym przypadku zapytanie jest proste, a wartości są bardzo różne. Dynamiczny SQL będzie wielką wygraną.
DynamicznyLIMIT dla każdego kroku zapytania.
Pomaga to na wiele sposobów: Po pierwsze, wiersze są pobierane tylko w razie potrzeby. W połączeniu z dynamicznym SQL może to na początku generować różne plany zapytań. Po drugie: nie ma potrzeby wprowadzania dodatkowego LIMITwywołania funkcji w celu przycięcia nadwyżki.
Reper
Ustawiać
Wybrałem cztery przykłady i każdy z nich przeprowadziłem trzy różne testy. Wziąłem najlepsze z pięciu, aby porównać z ciepłą pamięcią podręczną:
Surowe zapytanie SQL formularza:
SELECT *
FROM lexikon
WHERE lset >= 20000
AND lset <= 30000
ORDER BY frequency DESC
LIMIT 5;
To samo po utworzeniu tego indeksu
CREATE INDEX ON lexikon(lset);
Potrzebuje mniej więcej tej samej przestrzeni, co wszystkie moje częściowe indeksy razem:
SELECT pg_size_pretty(pg_total_relation_size('lexikon')) -- 93 MB
Funkcja
SELECT * FROM f_search(20000, 30000, 5);
Wyniki
SELECT * FROM f_search(20000, 30000, 5);
1: Całkowity czas działania: 315,458 ms
2: Całkowity czas działania: 36,458 ms
3: Całkowity czas działania: 0,330 ms
SELECT * FROM f_search(60000, 65000, 100);
1: Całkowity czas działania: 294,819 ms
2: Całkowity czas działania: 18,915 ms
3: Całkowity czas działania: 1,414 ms
SELECT * FROM f_search(10000, 70000, 100);
1: Całkowity czas działania: 426,831 ms
2: Całkowity czas działania: 217,874 ms
3: Całkowity czas działania: 1,611 ms
SELECT * FROM f_search(1, 1000000, 5);
1: Całkowity czas działania: 2458.205 ms
2: Całkowity czas działania: 2458.205 ms - dla dużych zakresów lset skanowanie seq jest szybsze niż indeks.
3: Całkowity czas działania: 0,266 ms
Wniosek
Zgodnie z oczekiwaniami, korzyści płynące z funkcji rosną wraz z większymi zakresami lseti mniejszymi LIMIT.
Przy bardzo małych zakresachlset zapytanie surowe w połączeniu z indeksem jest w rzeczywistości szybsze . Będziesz chciał przetestować, a może rozgałęzić: surowe zapytanie dla małych zakresów lset, w przeciwnym razie wywołanie funkcji. Możesz nawet po prostu wbudować to w funkcję „najlepszego z obu światów” - tak bym zrobił.
W zależności od dystrybucji danych i typowych zapytań więcej kroków lex_freqmoże poprawić wydajność. Przetestuj, aby znaleźć najsłodsze miejsce. Dzięki narzędziom przedstawionym tutaj testowanie powinno być łatwe.