Если совсем предельно экономить, можно выжать еще пару процентов:
WITH map AS (
SELECT
row_number() OVER(ORDER BY n) pos
, CASE
WHEN n % 2 = 1 THEN n / 2
END fid
FROM
regexp_split_to_table(
'2333133121414131402'
, ''
)
WITH ORDINALITY T(sz, n)
, generate_series(1, sz::integer)
)
, remap AS (
SELECT
array_agg(fid ORDER BY pos DESC) FILTER(WHERE fid IS NOT NULL) rpl
, count(*) FILTER(WHERE fid IS NOT NULL) lim
FROM
map
)
SELECT
sum((pos - 1) * coalesce(fid, rpl[rplpos]))
FROM
(
SELECT
map.*
, count(*) FILTER(WHERE fid IS NULL) OVER(ORDER BY pos) rplpos
FROM
map
WHERE
pos <= (SELECT lim FROM remap)
) T
, remap;
А если вот так поменять кусочек, то еще 10мс долой:
, remap AS (
SELECT
array_agg(fid ORDER BY pos DESC) rpl
, count(*) lim
FROM
map
WHERE
fid IS NOT NULL
)
Если развить предложенный подход и избавиться от лишних операций со строками, то можно сделать вот так:
WITH map AS (
SELECT
row_number() OVER() pos
, CASE
WHEN n % 2 = 1 THEN n / 2
END fid
FROM
regexp_split_to_table(
'2333133121414131402'
, ''
)
WITH ORDINALITY T(sz, n)
, generate_series(1, sz::integer)
)
, remap AS (
SELECT
u.*
, lim
FROM
(
SELECT
array_agg(pos ORDER BY pos) FILTER(WHERE fid IS NULL) free
, array_agg(fid ORDER BY pos DESC) FILTER(WHERE fid IS NOT NULL) file
FROM
map
) T
, array_length(file, 1) lim
, unnest(free, file) u(pos, fid)
)
SELECT
sum((pos - 1) * coalesce(map.fid, remap.fid))
FROM
map
LEFT JOIN
remap
USING(pos)
WHERE
map.pos <= (SELECT lim FROM remap LIMIT 1);
Получается вот такой план - 182ms вместо 326ms, то есть примерно в 2 раза быстрее чем в статье:
Я исключительно "за" множество разнообразных подходов к решениям! Правда, такого уровня запросы уже стоит как-то "расшифровывать" - с массивами это как-то попроще.
"Уж сколько раз твердили миру..." смотреть на buffers при анализе быстродействия:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customerid, categoryid, weekday, total FROM shopping
ORDER BY CustomerId, CategoryId, WeekDay;
Index Scan using idx1 on shopping (actual time=0.065..318.714 rows=1000000 loops=1)
Buffers: local hit=935926 read=12272
Planning Time: 0.096 ms
Execution Time: 356.156 ms
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customerid, categoryid, weekday, total FROM shopping
ORDER BY WeekDay, CategoryId, CustomerId;
Index Scan using idx2 on shopping (actual time=0.069..9072.660 rows=1000000 loops=1)
Buffers: local hit=50 read=1004865
Planning Time: 0.102 ms
Execution Time: 9170.632 ms
Если "пересортировать" под второй индекс, становится быстрее уже он:
CREATE TEMP TABLE shopping2 AS
TABLE shopping ORDER BY WeekDay, CategoryId, CustomerId;
CREATE INDEX idx21 ON shopping2(CustomerId, CategoryId, WeekDay);
CREATE INDEX idx22 ON shopping2(WeekDay, CategoryId, CustomerId);
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customerid, categoryid, weekday, total FROM shopping2
ORDER BY WeekDay, CategoryId, CustomerId;
Index Scan using idx22 on shopping2 (actual time=0.073..202.190 rows=1000000 loops=1)
Buffers: local read=12262
Planning Time: 0.094 ms
Execution Time: 239.664 ms
Кто понимает, зачем использует ORM, его сильные и слабые стороны, - молодец.
Увы, приходилось видеть генерированные запросы с тысячами ID-параметров, гуляющих в рамках одной базы между сервером и клиентом. Впрочем, и для разных баз можно обойтись без проброса через клиента - с помощью FDW, например.
Сейчас фича проходит испытания. Учитывая, что зависимости от версии ядра минимальны - структура query tree достаточно стабильна, и нет причин модифицировать код, то она может использоваться в Postgres версии 10, а может быть даже и ранее.
Очень похоже, что конкретно эта тема не пройдет, хотя сама идея автоисправления подозрительных запросов любопытна.
Но, насколько я могу судить, общая тенденция все-таки в сторону оптимизаций планировщика под запрос, а не наоборот - хотя и там не все идет гладко.
Слишком часто. Выставлять чаще 1h обычно незачем - полезнее тогда уж целевые таблицы внешними средствами обслуживать.
То есть если вы решили сделать очередь-на-таблице, например, то гораздо эффективнее делать на ней TRUNCATE при опустошении на прикладном уровне, чем заставлять дергаться AV.
Такой вариант очень активно будет просаживать вашу базу, если активных таблиц/секций в ней достаточно много - например, реализован шардинг пользовательских данных. Сама процедура перебора таблиц и оценка необходимости обработки каждой из них - недешева.
А зачем мне это значение вычислять? Достаточно глаз и первой картинки, чтобы увидеть, что связь между количеством прочитанных данных и индексно прочитанных записей есть.
То есть я могу, конечно, заранее прописать в явном виде (как это сделано для метрики read.ratio на нижнем графике первой картинки) для каждой пары величин - но зачем?
Зачем? График - это инструмент для пост-анализа причин и обнаружения корреляций, которые не прописаны заранее, незачем в них "сидеть".
Объяснять им это бесполезно. ... Реальному DBA , в реальной работе это не поможет
Откуда такая категоричность? Если конкретно вам не повезло с разработчиками, или не хватает возможностей отвечать "за костюмчик" и хочется только "за пуговицы" - это не означает, что так живут все.
Высокая утилизация CPU - показывает эффективное использование предоставленных ресурсов.
Или она может запросто показывать крайне печальное состояние внутри самой СУБД (пример), или на том же хосте может существовать некий параллельный (иногда даже системный) процесс, "отъедающий CPU". Поэтому без дополнительных метрик утверждение бессмысленно.
Если совсем предельно экономить, можно выжать еще пару процентов:
А если вот так поменять кусочек, то еще 10мс долой:
Если развить предложенный подход и избавиться от лишних операций со строками, то можно сделать вот так:
Получается вот такой план - 182ms вместо 326ms, то есть примерно в 2 раза быстрее чем в статье:
В первом приближении, оно считает неправильно - выдает 2003 на тестовой последовательности вместо 1928 согласно постановке.
Вы когда картинки сериями из презентации дергаете, хотя бы ссылки на первоисточник оставляйте (pdf, транскрипт и видео).
Я исключительно "за" множество разнообразных подходов к решениям! Правда, такого уровня запросы уже стоит как-то "расшифровывать" - с массивами это как-то попроще.
Безусловно. Но это несколько усложнит код примера, а так уж на существенно на скорость не повлияет с точки зрения абсолютных величин.
depesz слегка закрылся от нас, так что используйте explain.tensor.ru
"Уж сколько раз твердили миру..." смотреть на buffers при анализе быстродействия:
Если "пересортировать" под второй индекс, становится быстрее уже он:
Спасибо! Добавил к статье с замечанием о линейной упорядочиваемости типа значения.
Кто понимает, зачем использует ORM, его сильные и слабые стороны, - молодец.
Увы, приходилось видеть генерированные запросы с тысячами ID-параметров, гуляющих в рамках одной базы между сервером и клиентом. Впрочем, и для разных баз можно обойтись без проброса через клиента - с помощью FDW, например.
Мы разработали для себя и предлагаем другим вот такую штуку - можно потрогать демку.
Очень похоже, что конкретно эта тема не пройдет, хотя сама идея автоисправления подозрительных запросов любопытна.
Но, насколько я могу судить, общая тенденция все-таки в сторону оптимизаций планировщика под запрос, а не наоборот - хотя и там не все идет гладко.
Немного дожег - смотрите UPD. ))
Слишком часто. Выставлять чаще 1h обычно незачем - полезнее тогда уж целевые таблицы внешними средствами обслуживать.
То есть если вы решили сделать очередь-на-таблице, например, то гораздо эффективнее делать на ней TRUNCATE при опустошении на прикладном уровне, чем заставлять дергаться AV.
ALTER SYSTEM SET autovacuum_naptime = '10min';Такой вариант очень активно будет просаживать вашу базу, если активных таблиц/секций в ней достаточно много - например, реализован шардинг пользовательских данных. Сама процедура перебора таблиц и оценка необходимости обработки каждой из них - недешева.
Альтернативный вариант на SQL получился вроде как попроще.
А зачем мне это значение вычислять? Достаточно глаз и первой картинки, чтобы увидеть, что связь между количеством прочитанных данных и индексно прочитанных записей есть.
То есть я могу, конечно, заранее прописать в явном виде (как это сделано для метрики read.ratio на нижнем графике первой картинки) для каждой пары величин - но зачем?
Большей частью "глазами", когда это необходимо.
Вот конкретный пример - около 07:14 был аномально резкий резкий рост
block.hit- а почему?.. а потому что из БДIndex Scan'ами выдернули кучу записей:Дальше мы выявляем, в которой табличке/индексе произошла неприятность:
Зная имя индекса, находим подходящие по таймлайну шаблоны планов:
Видим, что в моменте пришло аномально большое количество "похожих" запросов:
Берем любой из этих планов, и понимаем, что у нас тут индекса не хватает подходящего:
Который нам сразу предлагают создать:
Зачем? График - это инструмент для пост-анализа причин и обнаружения корреляций, которые не прописаны заранее, незачем в них "сидеть".
Откуда такая категоричность? Если конкретно вам не повезло с разработчиками, или не хватает возможностей отвечать "за костюмчик" и хочется только "за пуговицы" - это не означает, что так живут все.
Или она может запросто показывать крайне печальное состояние внутри самой СУБД (пример), или на том же хосте может существовать некий параллельный (иногда даже системный) процесс, "отъедающий CPU".
Поэтому без дополнительных метрик утверждение бессмысленно.