Индексы и анализ

Создадим таблицу для хранения двух текстовых строк.

CREATE TABLE t (t1 BIGSERIAL PRIMARY KEY, t2 TEXT, t3 TEXT);

Напишем функцию для получения случайного целого числа.

CREATE OR REPLACE FUNCTION get_random() RETURNS INTEGER AS $$
    DECLARE answer INTEGER;
BEGIN
    answer = CAST(floor( random() * 100000 ) AS INTEGER) % 10 + 2;   
    RETURN answer;
END;

$$ LANGUAGE plpgsql;

Напишем функцию для получения случайной текстовой строки.

CREATE OR REPLACE FUNCTION get_string() RETURNS TEXT AS $$
    DECLARE my_array TEXT ARRAY;
    DECLARE string_length INTEGER;
    DECLARE answer TEXT;
BEGIN
    my_array = ARRAY ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'a', 'y', 'z', 'q', 'q', 'q', 'q', 'q'];
    string_length = get_random() * 2;
    answer = '';
    FOR I IN 1..string_length LOOP
        answer = answer || my_array[get_random()];
    END LOOP;
    RETURN answer;
END;

$$ LANGUAGE plpgsql;

Напишем функцию, которая добавляет определённое количество записей в таблицу.

CREATE OR REPLACE FUNCTION add_random_records (records_number INTEGER) RETURNS INTEGER AS $$
    DECLARE iter INTEGER;
    DECLARE s2 TEXT;
    DECLARE s3 TEXT;
BEGIN
    FOR iter IN 1..records_number LOOP
        s2 = get_string();
        s3 = get_string();
        INSERT INTO t (t2, t3) VALUES (s2, s3);
    END LOOP;
    RETURN records_number;
END;

$$ LANGUAGE plpgsql;

По умолчанию создаётся один индекс для ключевого поля счётчика.

Поиск строк без использования индексов

Для заполнения таблицы данными вызываем процедуру.

SELECT add_random_records (200000);

Проанализируем запрос на выборку записи, где поле t2 имеет определённое значение.

EXPLAIN ANALYZE SELECT * FROM t WHERE t2 = 'ddfgfqfbgdyefzgcef';

Проанализируем запрос на выборку, где поля t2 и t3 имеют определённые значения.

EXPLAIN ANALYZE SELECT * FROM t WHERE t2 = 'ddfgfqfbgdyefzgcef' AND t3 = 'eyeaezdgya';

Использование btree индексов для поиска и сортировки строк

Удаляем таблицу.

DROP TABLE t;

Создаём таблицу заново.

CREATE TABLE t (t1 BIGSERIAL PRIMARY KEY, t2 TEXT, t3 TEXT);

Создаём два btree индекса.

CREATE INDEX my_t2_index ON t USING btree (t2);
CREATE INDEX my_t3_index ON t USING btree (t3);

Заполняем пустую таблицу данными.

SELECT add_random_records (200000);

Проанализируем запрос на выборку, где поле t2 имеет определённое значение.

EXPLAIN ANALYZE SELECT * FROM t WHERE t2 = 'dcyyqqyzgbbadbzgag';

Проанализируем запрос на выборку, где поля t2 и t3 имеют определённые значения.

EXPLAIN ANALYZE SELECT * FROM t WHERE t2 = 'dcyyqqyzgbbadbzgag' AND t3 = 'cazaecyf';

Проанализируем запрос на вывод первых десяти записей после сортировки по полю t2.

EXPLAIN ANALYZE SELECT * FROM t ORDER BY t2 ASC LIMIT 10;

В результате анализа выясняется, что поиск и сортировка данных с использованием индексов работает гораздо быстрее.

Удаление индексов

Удаляем созданные индексы

DROP INDEX IF EXISTS my_t2_index;
DROP INDEX IF EXISTS my_t3_index;

Функциональный индекс

Создадим таблицу для хранения строк.

CREATE TABLE s (s1 SERIAL PRIMARY KEY, s2 TEXT);

Создадим функциональный индекс для поля s2.

Индекс использует функцию перевода строки в нижний регистр.

CREATE INDEX my_s2_func_index ON s ( LOWER(s2) );

Заполним таблицу строками, содержащими большие и маленькие буквы.

Выполним запрос, который ищет запись с определённым значением поля s2 без использования функции LOWER.

Данный запрос будет работать медленно, так как он будет перебирать все строки таблицы.

EXPLAIN ANALYZE SELECT * FROM s WHERE s2 = 'FZgyqdYfEDcFfzEAGC';

Теперь выполним запрос, который ищет запись по полю s2 c использованием функции LOWER.

Данный запрос будет работать быстро, так как он использует функциональный индекс.

EXPLAIN ANALYZE SELECT * FROM s WHERE LOWER(s2) = LOWER('FZgyqdYfEDcFfzEAGC');

Использование функционального индекса с собственными функциями

Напишем собственную функцию, возвращающую длину строки.

Слово IMMUTABLE было добавлено, чтобы было можно использовать данную функцию для индекса.

CREATE OR REPLACE FUNCTION get_number (q TEXT) RETURNS INTEGER AS $$
    DECLARE n INTEGER;
BEGIN
    n = LENGTH(q);
    RETURN n;
END;

$$ LANGUAGE plpgsql IMMUTABLE;

Создадим таблицу для хранения строк.

CREATE TABLE x (x1 SERIAL PRIMARY KEY, x2 TEXT);

Создадим функциональный индекс для поля x2.

Индекс использует функцию get_number.

CREATE INDEX my_func_ind_x2_x2 ON x ( get_number(x2) );

Заполним таблицу записями.

Напишем запрос на получение записей, у которых длина строки в поле x2 равняется восьми.

Данный запрос будет работать быстро, так как используется функциональный индекс.

EXPLAIN ANALYZE SELECT * FROM x WHERE get_number(x2) = 8;