24 заметки с тегом

постгрес

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

Вчера под ночь боролся с «Постгресом» (это такая СУБД), в итоге поборол, но не так, как хотелось бы. Бизнес-задачу мне не хотелось бы сейчас рассматривать, поэтому для иллюстрации проблемы создам тестовую таблицу безо всякой смысловой нагрузки:

SELECT id, CASE WHEN RANDOM()>.5 THEN value END AS value
INTO test
FROM generate_series(1, 1000000) WITH ORDINALITY AS test(id, value)

В тестовой таблице два поля — идентификатор (id) и значение (value), примерно половина значений в поле value — NULL. И теперь нам надо создать индекс для запросов, которые выглядит примерно так:

SELECT id FROM test WHERE value IS NULL ORDER BY id;
SELECT id FROM test WHERE value IS NOT NULL ORDER BY id;

План для них выглядит одинаково печально, что логично — на таблице ни одного индекса:

Sort  (cost=61739.93..62977.68 rows=495100 width=4) (actual time=47289.775..47761.235 rows=498778 loops=1)
   Sort Key: id
   Sort Method: external merge  Disk: 6848kB
   ->  Seq Scan on test  (cost=0.00..14910.00 rows=495100 width=4) (actual time=12.039..40854.731 rows=498778 loops=1)
         Filter: (value IS NULL)
         Rows Removed by Filter: 501222
 Planning Time: 0.174 ms
 Execution Time: 48134.553 ms

Чтобы сэкономить место, мне очень хотелось сделать индекс функциональным:

CREATE UNIQUE INDEX ON test((value IS NULL), id);

Таким образом вместо целого поля будет использован логический тип всего с двумя значениями.

Конечно из-за выравнивания полей в индексе полноценно воспользоваться экономией в данном случае не получится, но хотя бы статистика для оптимизатора будет точнее за счёт однообразности значений.

Первый запрос ожидаемо улучшился:

Index Scan using test_expr_id_idx on test  (cost=0.42..33882.43 rows=495100 width=4) (actual time=0.056..519.584 rows=498778 loops=1)
   Index Cond: ((value IS NULL) = true)
 Planning Time: 0.182 ms
 Execution Time: 894.551 ms

А второй — нет:

Sort  (cost=62738.26..64000.51 rows=504900 width=4) (actual time=1046.582..1471.180 rows=501222 loops=1)
   Sort Key: id
   Sort Method: external merge  Disk: 6880kB
   ->  Seq Scan on test  (cost=0.00..14910.00 rows=504900 width=4) (actual time=0.033..522.071 rows=501222 loops=1)
         Filter: (value IS NOT NULL)
         Rows Removed by Filter: 498778
 Planning Time: 0.136 ms
 Execution Time: 1841.282 ms

Несмотря на то, что в индексе содержится информация, достаточная для выполнения этого запроса, в данном случае «Постгрес» не понимает, что VALUE IS NOT NULL эквивалентно (VALUE IS NULL) = false.

Причём, если попробовать переписать запрос, чтобы условие выборки выглядело как (VALUE IS NULL) = false, то тут оптимизатор не оплошает — перепишет за нас условие в виде VALUE IS NOT NULL и опять не будет узнавать его в индексе.

Я вижу несколько путей решения этой проблемы, все с недостатками.

Во-первых, можно «замаскировать» конструкцию …IS [NOT] NULL, чтобы оптимизатор перестал его нормализировать:

CREATE UNIQUE INDEX ON test((CASE WHEN value IS NULL THEN TRUE ELSE FALSE END), id);

Недостатки очевидны: громоздкость конструкции, кроме того, легко забыть, что условия для этой таблицы надо писать именно так. Можно поместить эту конструкцию внутрь функции, но это устранит только первый недостаток.

Во-вторых, можно создать два индекса вместо одного:

CREATE UNIQUE INDEX ON test((value IS NULL), id);
CREATE UNIQUE INDEX ON test((value IS NOT NULL), id);

Недостаток: у нас два индекса — при вставке или обновлении будем писать в два места, раздуваться (bloat) у нас будут тоже два индекса вместо одного.

В-третьих, можно не выпендриваться, а создать обычный индекс:

CREATE UNIQUE INDEX ON test(value, id);

Фатальный недостаток: индекс, по сути, бесполезен — он содержит всю таблицу целиком, «Постгрес» скорее всего выберет последовательное сканирование таблицы, а индекс использовать не будет.

И, наконец, вариант, который мне нравится в этой ситуации больше всего — частичные индексы:

CREATE UNIQUE INDEX ON test(id) WHERE value IS NULL;
CREATE UNIQUE INDEX ON test(id) WHERE value IS NOT NULL;

Да, у нас опять два индекса, но они довольно компактны по размеру: в каждом примерно половина данных, кроме того, в нём вообще только одно поле — второго нет вовсе, его заменяет условие индекса.

Добавлено позднее: мне тут подсказывают, что нужно ещё учитывать селективность. Это верно, но мне совершенно не хотелось затрагивать ещё и эту тему. В реально задаче в индексе больше полей.

 1 комментарий    335   27 дн   sql   постгрес   программирование

Выполнение запроса с таймаутом

В продукте, который мы делаем в нашей компании, не используется PDO для соединения с базой данных — так исторически сложилось и вряд ли имеет смысл менять. Причина тому — оптимизации; мы широко используем эскуэль и задействуем диалектные особенности используемой СУБД (в нашем случае это «Постгрес»). То есть универсальность PDO нам ничего бы не дала.

Недавно возникла задача выполнения запроса с таймаутом — если выполнение запроса занимает больше времени, чем по нашему мнению пользователь готов ждать, то лучше прервать запрос и показать заглушку. В PDO кажется такая возможность есть (параметр можно задать, но мы не тестировали), а в постгресовском модуле для ПХП готового способа не оказалось.

Мы долгое время перебирали идеи (вплоть до внешнего процесса с таймером), но ничего не выглядело достаточно хорошо, чтобы помещать это в код. В конечном счёте мой братишка предложил очень простое решение. Ответ лежал в работе с асинхронным АПИ. Обычно это выглядит как-то так:

$con = pg_pconnect("…");
if ($con === false) {
    throw new \RuntimeException('Could not connect');
}
// асинхронно выполняем запрос, в синхронном варианте тут было бы pg_query
if (pg_send_query($conn, "select * from users")) {
    // тут можно делать что угодно, пока мы не будем готовы принять результат
    // …
    // синхронно получаем результат
    var_dump(pg_get_result($conn));
}

Поскольку pg_send_query неблокирующий, то можно было бы попытаться как-то проконтролировать не истёк ли таймаут. Но как это сделать? Вот что он придумал:

function waitForResult($connection, callable $callback, int $timeout): void
{
    $stream = pg_socket($connection);
    while (pg_connection_busy($connection)) {
        $read = [$stream];
        $write = $except = null;

        $numChangedStreams = stream_select($read, $write, $except, 0, $timeout);
        if ($numChangedStreams > 0 || $numChangedStreams === false) {
            break;
        }

        $callback();
    }
}

После отсылки запроса мы получаем сетевой сокет соединения с базой данных и ожидаем в нём появления данных для чтения. Если за таймаут этого не происходит (или возникает ошибка), то цикл ожидания прерывается, иначе вызывается функция обратного вызова для получения порции данных.

 2 комментария    427   7 мес   php   постгрес   программирование

Слоны

Есть такая система репликации на триггерах для СУБД «Постгрес» — Slony, не знаю кто как читает это слово по всему миру, я всегда произносил его как «Сло́ни» — вслед за нашими системными администраторами.

Оказывается читать это слово следует немного иначе.

Примерно в конце девяностых на логотипе «Постгреса» появился слон, к этому логотипу, да и видимо вёрстке сайта, приложили руку россияне. Так картинка со слоном называлась «slonik.gif» и прислал её, как достоверно известно, петербуржец Дмитрий Самерсов, рисунок сделала какая-то его знакомая.

Тот самый логотип со слоном

Ян Вик, активный участник сообщества, в то время искал название для своей системы репликации и поинтересовался что значит «slonik», а потом спросил как будет много слонов, так и родилось название «Slony» — «Слоны́».

Мелочь в синтаксисе SELECT

В синтаксисе запроса SELECT «Постгреса» есть приятная мелочь, которая хоть и нестандартна, но греет мне душу, — согласно описанию, выбираемые поля (часть между SELECT и FROM) можно опускать.

Вот как это работает в консоли:

SELECT FROM usr;
--
(588910 строк)

Кстати, можно ещё поставить там ключевое слово ALL, будет результат будет точно такой же.

Для чего это может пригодиться? Для подзапросов с EXISTS. Мне не нравится, как выглядит всем привычный вариант:

SELECT COUNT(*)
FROM document
WHERE EXISTS (
    SELECT NULL
    FROM document_n
    WHERE id = document.id
)

В подзапросе после SELECT по стандарту надо что-то указать, всё равно что, использоваться это не будет. Мне такая избыточность не по вкусу. А вот «Постгрес» позволяет ничего там не указывать:

SELECT COUNT(*)
FROM document
WHERE EXISTS (
    SELECT
    FROM document_n
    WHERE id = document.id
)

Смотрится как будто чуть почище. Знаю, многие ребята осуждают использование особенностей реализации эскуэля в конкретной СУБД, из-за возможных проблем с портированием на другие СУБД, но для нас это вряд ли проблема — в нашем продукте используется множество куда более сложных специфичных для «Постгреса» вещей.

Если когда-либо встанет вопрос о переезде на другую СУБД, то вышеописанное — самая малая из проблем и решить её просто — заменой через регулярное выражение.

 2 комментария    38   2018   постгрес   программирование

Примерная оценка в Постгресе

Читая документацию по «Постгресу», набрёл на интересную конструкцию — TABLESAMPLE. Она позволяет обработать запросом не всю таблицу, а только указанный процент, причём строки выбираются случайным образом.

Например, встречается такая задача — показать какую-нибудь внутреннюю рекламу, для этого надо быстро выбрать какой-нибудь случайный идентификатор из таблицы, вот так это можно сделать при помощи указанной конструкции:

SELECT id FROM (
    SELECT id FROM very_big_table TABLESAMPLE SYSTEM (.0001) LIMIT 100
) _ ORDER BY RANDOM() LIMIT 1

Тут выбирается одна десятитысячная процента таблицы (0,0001%), а потом из этой выборки берётся первая строка. На настоящей таблице с почти миллиардом строк, такой запрос у меня занял около ста миллисекунд.

SYSTEM тут — метод выборки. Их всего два, второй (BERNOULLI) даёт более случайные и аккуратные результаты, но работает как-то уж очень медленно. SYSTEM работает на уровне блоков, выдавая случайные блоки целиком, и из-за этого я дополнительно сортирую результат — чтобы перемешать строки внутри блока.

А вообще TABLESAMPLE хорошая штука, когда нужно быстро прикинуть результат. Например, у вас какой-то тяжёлый запрос, а примерное значение необходимо прямо сейчас. Ограничиваем выборку каким-то статистически значимым процентом таблицы и экстраполируем, если это требуется.

Типа, сколько в среднем примерно сотрудники некой гипотетической компании тратят на обед в зависимости от пола (оценка по одному проценту данных):

SELECT sex, AVG(spent)
FROM meal TABLESAMPLE SYSTEM (1)
WHERE mtime BETWEEN SYMMETRIC '12:00:00' AND '14:00:00'
GROUP BY 1

Или сколько примерно раз я ездил такси «Везёт» за последние семь лет (оценка по пяти процентам и экстраполяция):

SELECT COUNT(*)*20
FROM taxi_call TABLESAMPLE BERNOULLI (5)
WHERE carrier='vezet' AND cdate >= '2011-08-03'

Как сконвертировать bytea

Пока сегодня ехал домой с военного поиска (я в таком уже участвовал в прошлом году), написала в «телеграм» дипломница — она не смогла разобраться как сконвертировать в буквы формат постгреса bytea, в котором мы ей выгрузили кое-какие данные по её теме.

Ограничусь фотографией на которой почти ничего не видно, дабы не эпатировать публику

Отдельно про поездку рассказывать не буду, я вообще подумал, что пора завязывать с публикациями на такие темы, а пока ехал, накидал на коленке функцию на Пайтоне, положу сюда, чтобы не потерялась:

def decodeBytea(bytes: bytearray, charset: str) -> str:
	if bytes[:2] == b'\\x':
		return bytearray.fromhex(bytes[2:].decode()).decode(charset)
	else:
		raise ValueError('Invalid format')

Формат немудрёный — впереди слеш-экс, дальше идут шестнадцатеричные коды байт, которые надо превратить в символы.

 4 комментария    38   2018   python   постгрес   программирование

Сдутие таблиц без блокировки

Братишка прислал ссылку на интересную статью — «Reducing bloat without locking». Речь идёт о так называемом «распухании» (bloating) таблиц в «Постгресе».

Подробно суть в трёх предложениях не объяснить, если вкратце, то механизм изоляции в транзакциях у «Постгреса» устроен так, что при обновлении и удалении строк в таблицах могут образовываться «дыры», которые увеличивают общий объём таблицы. Если происходят вставки или обновления и новые данные помещаются в неиспользуемое пространство, то «дыры» могут со временем заполняться. Но иногда «дыр» остаётся много и это проблема с которой надо что-то делать.

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

test=> CREATE TABLE test AS SELECT generate_series(1, 1000) id;
SELECT 1000
test=> SELECT MAX(ctid) FROM test;
  max
--------
 (4,96)
(1 row)
test=> DELETE FROM test WHERE id & 1 = 1;
DELETE 500
test=> SELECT MAX(ctid) FROM test;
  max
--------
 (4,96)
(1 row)

ctid — это специальный внутренний столбец «Постгреса», который отображает физическое расположение строки. Для каждой строки в нём записано два числа — номер страницы, где располагается строка и порядковый номер строки в странице.

Как видим, таблица «распухла» — половина строк удалена, а количество занимаемых страниц не изменилось. Что же можно сделать? Давайте попробуем команду VACUUM:

test=> VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 500 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 500 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

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

Что же делать? Можно выполнить VACUUM FULL — эта команда полностью перельёт таблицу на новое место, избавив нас от «дыр», но во время работы таблица будет эксклюзивно заблокирована, для больших таблиц, да ещё в продуктиве это непозволительно.

Другой способ — перенести строки с последних страниц в «дыры» на первых, освободив таким образом несколько страниц в конце. Попробуем:

test=> BEGIN;
BEGIN
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 161
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 161
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 48
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 48
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 34
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 0
test=> COMMIT;
COMMIT

В одной транзакции мы обновили все строки, которые располагаются на первой странице и дальше. Попробуем теперь сделать VACUUM:

test=> VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 452 row versions in 2 pages
INFO:  "test": found 452 removable, 500 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 469 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": truncated 5 to 3 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Как видимо команда удалила две неиспользуемые страницы и уменьшила общее количество страниц с пяти до трёх.

Идея, описанная в статье, лежит в основе скрипта pgcompacttable из инструментария компании «Дейта Игрет».

 4 комментария    94   2018   постгрес   программирование

Проблема при апгрейде Постгреса

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

Я в обновлении не участвовал, но наблюдал вполглаза. Заинтересовался что дало такой результат — что-то перекрутили в Посгресе или какие-то наши поздние коммиты так ухудшили ситуацию?

Посмотрел какие запросы вылезли наверх в «Барсуке», глазам не поверил — запросы довольно простые, с чего бы им тормозить, посмотрел планы и причину нашёл довольно быстро. Но обо всём по порядку.

Миграцию с одной версии на другую делали полным дампом — идея была под шумок избавиться ещё и от распухания (bloat) таблиц. Не в первый раз такое делали, да и способ довольно обычный.

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

А когда восстанавливались из дампа, вот что пошло не так:

sed=> CREATE INDEX du_uid_did ON document_user USING btree (user_id, document_id);
ERROR: temporary file size exceeds temp_file_limit (4194304kB)

Так как проблем никто не ожидал, в лог наливки данных не заглянули, а там процесс прервался на попытке создания одного из индексов. Как результат — не создались этот индекс и все идущие за ним (ещё две штуки), сканирование по индексам в некоторых запросах сменилось на последовательный скан по таблице и всё стало плохо.

 2 комментария    21   2018   постгрес   программирование

PostgreSQL и PHP — слон слону не товарищ

Продолжаю серию удивительных открытий в мире перехода на «Постгрес». В документации к функции pg_execute есть малозаметное примечание к последнему параметру — в нём передаются значения для запроса:

Warning Elements are converted to strings by calling this function.

Думаю мало кто обращает на него внимания, собственно, я тоже не обращал. Прежде чем двинуться дальше, разберёмся — что же здесь написано?

Перевод такой: все значения, которые передаются, приводятся к строкам. Код, который это выполняется выглядит так (взял из ПХП 7.2):

if (num_params > 0) {
        int i = 0;
        params = (char **)safe_emalloc(sizeof(char *), num_params, 0);

        ZEND_HASH_FOREACH_VAL(Z_ARRVAL_P(pv_param_arr), tmp) {
                ZVAL_DEREF(tmp);
                if (Z_TYPE_P(tmp) == IS_NULL) {
                        params[i] = NULL;
                } else {
                        zval tmp_val;

                        ZVAL_COPY(&tmp_val, tmp);
                        convert_to_cstring(&tmp_val);
                        params[i] = estrndup(Z_STRVAL(tmp_val), Z_STRLEN(tmp_val));
                        zval_ptr_dtor(&tmp_val);
                }
                i++;
        } ZEND_HASH_FOREACH_END();
}

pgsql_result = PQexecParams(pgsql, query, num_params,
                                NULL, (const char * const *)params, NULL, NULL, 0);

Вышеупомянутое примечание есть только у этой функции, но на деле в любом месте, где привязываются значения, всё выглядит примерно так же (это касается и модуля ПДО).

Думаю, это связано с типизацией «Постгреса». Взять к примеру числа — два числовых типа ПХП нельзя адекватно преобразовать в россыпь типов «Постгреса», а если привести к неверному типу будут проблемы — в этой СУБД есть понятие перерузки функций, то есть функция выбирается не только по имени, но и по числу и типам параметров.

Поэтому и выбраны строки — они приведутся к нужному числовому типу сами собой, со строками это работает. К сожалению в этом преобразовании кроются и проблемы.

Ещё когда мы работали только с «Ораклом», заметили, что если вместо чисел привязывать строки, то иногда планы выполнения запросов меняются в худшую сторону. Лёгкость обращения с типами в ПХП иногда к этому приводит — переменная, используемая для хранения числа, имеет строковый тип.

К счастью, в оракловом модуле это легко решается — при привязке надо всего лишь указать, что тут мы имеем ввиду число, сами собой совершатся нужные преобразования и «Оракл» так устроен, что никаких проблем это не породит.

В «Постгресе» проблемы будут, я их описал выше, так ещё и способа хорошего нет — все функции любого модуля для работы с этой базой, доступные в ПХП любой версии, преобразуют все числа в строки. К несчастью, в «Постгресе» проблема изменения планов тоже имеет место — недавно наткнулись на запрос, который через ПХП выполняется почти полторы секунды, а через консольный клиент — меньше миллисекунды.

Мой братишка придумал оригинальное решение — определять позиции на которых мы привязываем числа и автоматически внутри нашего фреймворка в этом месте запроса указывать тип bigint явным образом. То есть добавлять после плейсхолдера параметра конструкцию «::bigint».

Пришлось изменить несколько наших хранимых процедур, но в целом всё плошло довольно гладко.

 1 комментарий    32   2017   php   постгрес   программирование

Ускорение пользовательских функций в PostgreSQL

Мы ещё в процессе перевода последних наших проектов с «Оракла» на «Постгрес», поэтому всё ещё не закончили избавляться от характерных для диалекта «Оракла» функций, тащим их с собой — пока так проще.

В попытке как-то их ускорить, наткнулся на странность. Возможно это где-то описано, я пока не нашёл это место в руководстве.

Ниже две функции на разных языках, поддерживаемых «Постгресом», по смыслу идентичные — обе представляют собой замену функции NVL2 «Оракла» над целыми числами:

CREATE FUNCTION nvl2_plpgsql(a numeric, b numeric, c numeric) RETURNS numeric AS '
BEGIN
  IF a IS NULL
    THEN RETURN c;
    ELSE RETURN b;
  END IF;
END;
' LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION nvl2_sql(a numeric, b numeric, c numeric) RETURNS numeric AS '
SELECT CASE WHEN a IS NULL THEN c ELSE b END;
' LANGUAGE SQL IMMUTABLE;

Будет ли какая-то разница в производительности? Посмотрим на тесты:

 p95=# EXPLAIN ANALYZE SELECT nvl2_plpgsql(g, 0, 0) FROM generate_series(1, 10000000) _(g);

 Planning time: 0.066 ms  Execution time: 38881.927 ms

p95=# EXPLAIN ANALYZE SELECT nvl2_sql(g, 0, 0) FROM generate_series(1, 10000000) _(g);

 Planning time: 0.253 ms  Execution time: 15435.231 ms

Как ни странно, функция на чистом ЭсКуЭле быстрее. Судя по моим экспериментам, так всегда. Иногда результаты различаются вдвое, иногда на порядок — зависит от того, что она делает, но как правило быстрее, обратных примеров я ещё не встречал.

Это неплохое подспорье, так как у нас такие функции встречаются не только в самих запросах, но и в функциональных индексах, значит ускорятся не только чтения, но и вставки.

Ранее Ctrl + ↓