Индексы в PostgreSQL — 1 / Блог компании Postgres Professional / Хабр

Индексы по выражениям

Мы говорили о том, что условие поиска должно иметь вид «

индексированное-поле оператор выражение

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

postgres=# explain (costs off) select * from t where lower(b) = ‘a’;QUERY PLAN                ——————————————Seq Scan on tFilter: (lower((b)::text) = ‘a’::text)(2 rows)Этот конкретный запрос не составляет труда переписать так, чтобы слева от оператора стояло только имя поля. Но если это невозможно, на помощь приходят индексы по выражениям (функциональные индексы):

postgres=# create index on t(lower(b));CREATE INDEXpostgres=# analyze t;ANALYZEpostgres=# explain (costs off) select * from t where lower(b) = ‘a’;QUERY PLAN                    —————————————————-Bitmap Heap Scan on tRecheck Cond: (lower((b)::text) = ‘a’::text)->

Bitmap Index Scan on t_lower_idxIndex Cond: (lower((b)::text) = ‘a’::text)(4 rows)Функциональный индекс создается не по полю таблицы, а по произвольному выражению; оптимизатор будет принимать во внимание такой индекс для условий вида «индексированное-выражение оператор выражение».

Стоит также иметь в виду, что по индексированному выражению собирается отдельная статистика. Ее можно увидеть в представлении pg_stats по имени индекса:

postgres=# d tTable “public.t”Column |  Type   | Modifiers——– ——— ———–a      | integer |b      | text    |c      | boolean |Indexes:”t_a_b_idx” btree (a, b)”t_a_idx” btree (a)”t_b_idx” btree (b)”t_lower_idx” btree (lower(b))

postgres=# select * from pg_stats where tablename = ‘t_lower_idx’;…Если это необходимо, можно управлять числом корзин гистограмм так же, как и для обычных полей таблицы (учитывая при этом, что имя столбца может быть разным в зависимости от индексированного выражения):

postgres=# d t_lower_idxIndex “public.t_lower_idx”Column | Type | Definition——– —— ————lower  | text | lower(b)btree, for table “public.t”

postgres=# alter index t_lower_idx alter column “lower” set statistics 69;ALTER INDEX

Какие навыки нужны рынку?

Кроме уверенного понимания Postgres, понадобятся навыки работы с самым популярным инструментом контейнеризации — Kubernetes, умение настраивать все это в различных облаках AWS, GCP, Azure, работать в Docker и комфортно чувствовать себя с Linux.

Все это мы учли в программе курса «PostgreSQL». Она состоит из 7 модулей и рассчитана на 4 месяца обучения.

В первом вводном модуле вы научитесь работать в облаке Google и установите Postgre в различных вариантах. На занятиях обсудим различные логические и физические уровни, разберем, как все работает «из коробки», чтобы понять, из чего вообще состоит PostgreSQL.

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

В каждую тему будем погружаться глубоко, чтобы вы были готовы к любым неожиданностям в работе. Казалось бы блокировки — что там сложного? Но по факту там глубокий пласт проблем, который вы, возможно, не знаете.

И это единственный практико-ориентированный хардовый курс по этой СУБД в России. По сути программы других курсов дают то, что мы проходим в первых двух модулях.

Евгений Аристов

Один из самых важных модулей посвящен оптимизации и секционированию. В этой области люди, как правило, сталкиваются с большим количеством проблем, о которых даже не подозревали. После обучения вы сможете правильно джойнить, правильно брать индексы и знать, в чем их разница. Узнаете про OLAP нагрузку и чем она отличается от OLTP.

Еще два модуля посвящены кластерам: узнаете, что такое Kubernetes и с чем его «едят», а дальше в Kubernetes в облаках будете разворачивать высоконагруженные и параллельные кластера.

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

После обучения вам не составит труда одной командой поднять виртуальную машину, развернуть базу данных Postgres, поместить это все в контейнеры докера с Java-приложением и в итоге развернуть это все двумя кнопками через Docker-Compose.

Евгений Аристов

Курс для тех, кто хочет погрузиться проектирование с использованием Postgres, прокачать свои навыки DBA, научиться работать в Kubernetes и облаках. Если вы используете сейчас платные БД, например Oracle, работаете с большими базами данными, планируете перейти на бесплатные БД.

Если это про вас, по ссылке вас ждет вступительный тест и возможность попробовать курс еще до старта на демо-занятии «Секционирование таблиц PostgreSQL», который пройдет 26 октября в 20:00. Вы получите ценные знания, познакомитесь с одним из преподавателей курса Владимиром Крокодиловым и сможете задать ему свои вопросы по обучению. До встречи в OTUS!

Оптимизация плана «из коробки»

Для того чтобы у планировщика была возможность более точно предсказывать размер промежуточных результатов, в PostgreSQL используется сбор статистики по таблицам, которая копится в pg_statistic, или в ее более удобочитаемом варианте – в pg_stats. Обновляется она автоматически при запуске vacuum, либо явно при команде ANALYZE.

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

Возьмем, например, запрос:

SELECT t1.important_value FROM t1 WHERE t1.a > 100

Предположим, что гистограмма значений в колонке «t1.a» выявила, что значения, большие 100 встречаются в примерно 1% строк таблицы. Тогда можно предсказать, что такая выборка вернет около сотой доли всех строк из таблицы «t1».БД дает возможность посмотреть на прогнозируемую затратность плана через команду EXPLAIN, а фактическое время его работы — с помощью EXPLAIN ANALYZE.

Вроде бы с автоматической статистикой теперь все должно быть хорошо, но и тут могут быть сложности. Об этом есть хорошая статья от компании Citus Data, с примером неэффективности автоматической статистики и о сборе дополнительной статистики с помощью CREATE STATISTICS (доступна с PG 10.0).

Итак, для планировщика существуют два источника ошибок в вычислении затратности:

  1. Относительная затратность примитивных операций (seq_page_cost, cpu_operator_cost, и так далее) по умолчанию могут сильно отличаться от реальности (cpu cost 0.01, srq page load cost – 1 или 4 для random page load). Далеко не факт, что 100 сравнений будут равны 1 загрузке страницы.
  2. Ошибка с прогнозом числа строк в промежуточных операциях. Фактическая затратность операции в таком случае может сильно отличаться от прогноза.

В сложных запросах составление и прогнозирование всех возможных планов может само по себе занять массу времени. Что толку вернуть данные за 1 секунду, если БД только планировала запрос минуту? В PostgreSQL есть для такой ситуации Geqo optimizer, это планировщик, который строит не все возможные варианты планов, а начинает с нескольких случайных и достраивает наилучшие, прогнозируя пути снижения затратности. Все это тоже не улучшает точность прогноза, хотя и ускоряет нахождение хоть какого-нибудь более-менее оптимального плана.

Дополнительный анализ:  Прогнозы на волейбол от профессионалов бесплатно: лучшие ставки

Освежим воспоминания

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

ОперацияЧто выполняетсяЗатратность
Операции выборки данных SELECT … WHERE …
Seq ScanЗагружаем каждую строку из таблицы и проверяем условие.O(N)
Index Scan
(b-tree index)
Данные есть прямо в индексе, поэтому ищем по условию нужные элементы индекса и берем данные оттуда.O(log(N)), поиск элемента в отсортированном дереве.
Index Scan
(hash index)
Данные есть прямо в индексе, поэтому ищем по условию нужные элементы индекса и берем данные оттуда.O(1), поиск элемента в хэш-таблице, без учета затратности создания хэшей
Bitmap Heap ScanВыбираем номера нужных строк по индексу, затем загружаем только нужные строки и проводим с ними дополнительные проверки.Index Scan Seq Scan (M),
Где M – число найденных строк после Index Scan. Предполагается что M << N, т.е. индекс полезнее чем Seq Scan.
Операции соединения (JOIN, SELECT из нескольких таблиц)
Nested LoopДля каждой строки из левой таблицы ищем подходящую строку в правой таблице.O(N2).
Но если одна из таблиц значительно меньше другой (словарь) и практически не растет со временем, то фактическая затратность может снизиться до O(N).
Hash JoinДля каждой строки из левой и правой таблицы считаем хэш, за счет чего уменьшается число переборов возможных вариантов соединения.O(N), но в случае очень неэффективной функции хэша или большого количества одинаковых полей для соединения может быть и O(N2)
Merge JoinСортируем по условию левую и правую таблицы, после чего объединяем два отсортированных спискаO(N*log(N))
Затраты на сортировку проход по списку.
Операции агрегации (GROUP BY, DISTINCT)
Group AggregateСортируем таблицу по условию агрегации и потом в отсортированном списке группируем соседние строки.O(N*log(N))
Hash AggregateСчитаем хэш для условия агрегации, для каждой строки. Для строк с одинаковым hash проводим агрегацию.O(N)

Как можно понять, затратность запроса очень сильно зависит от того, как расположены данные в таблицах и как этот порядок соответствует используемым операциям хэширования. Nested Loop, несмотря на его затратность в O(N

2

) может быть выгоднее Hash Join или Merge Join когда одна из соединяемых таблиц вырождается до одной-нескольких строк.

Кроме ресурсов CPU, затратность включает в себя и использование памяти. И то, и другое – ограниченный ресурс, поэтому планировщику запросов приходится искать компромисс. Если две таблицы математически выгоднее соединить через Hash Join, но в памяти просто нет места под такую большую хэш-таблицу, БД может быть вынуждена использовать Merge Join, например. А «медленный» Nested Loop вообще не требует дополнительной памяти и готов выдавать результаты прямо сразу после запуска.

Относительную затратность этих операций нагляднее показать на графике. Здесь не абсолютные цифры, просто примерное отношение разных операций.

График Nested Loop «стартует» ниже, т.к. для него не требуется ни дополнительных вычислений, ни выделения памяти или копирования промежуточных данных, но у него затратность O(N2). У Merge Join и Hash Join начальная затратность выше, однако после некоторых величин N они начинают выигрывать во времени у Nested Loop.

Планировщик старается выбрать план с наименьшими затратами и на графике выше придерживается разных операций при разном N (зеленая пунктирная стрелка). При числе строк до N1 выгоднее использовать Nested Loop, от N1 до N2 выгоднее Merge Join, далее после N2 выгоднее становится Hash Join, однако Hash Join требует памяти для создания хэш-таблиц. И при достижении N3 этой памяти становится недостаточно, что приводит к вынужденному использованию Merge Join.

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

Константа относительной затратностиЗначение по умолчанию
seq_page_cost1.0
random_page_cost4.0
cpu_tuple_cost0.01
cpu_index_tuple_cost0.005
cpu_operator_cost0.0025
parallel_tuple_cost0.1
parallel_setup_cost1000.0

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

Но если знать хоть немного о природе данных в таблицах, это число строк можно предсказать более точно. Например, для двух таблиц по 100 строк из примера выше, если вы заранее знаете, что объединение даст не 10 тысяч строк, а те же 100, предполагаемая затратность следующей операции сильно сокращается. В этом случае данный план мог бы стать эффективнее других.

Предисловие


В этой серии статей речь пойдет об индексах в PostgreSQL.

Любой вопрос можно рассматривать с разных точек зрения. Мы будем говорить о том, что должно интересовать прикладного разработчика, использующего СУБД: какие индексы существуют, почему в PostgreSQL их так много разных, и как их использовать для ускорения запросов.

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

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

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

После такого длинного, но необходимого введения мы подробно рассмотрим устройство и применение различных типов индексов: Hash, B-tree, GiST, SP-GiST, GIN и RUM, BRIN и Bloom.

Читайте и другие серии.

Изоляция и многоверсионность:

  1. Изоляция, как ее понимают стандарт и PostgreSQL;
  2. Слои, файлы, страницы — что творится на физическом уровне;
  3. Версии строк, виртуальные и вложенные транзакции;
  4. Снимки данных и видимость версий строк, горизонт событий;
  5. Внутристраничная очистка и HOT-обновления;
  6. Обычная очистка (vacuum);
  7. Автоматическая очистка (autovacuum);
  8. Переполнение счетчика транзакций и заморозка.
Дополнительный анализ:  Главная

Журналирование:

  1. Буферный кеш;
  2. Журнал предзаписи — как устроен и как используется при восстановлении;
  3. Контрольная точка и фоновая запись — зачем нужны и как настраиваются;
  4. Настройка журнала — уровни и решаемые задачи, надежность и производительность.

Блокировки:

  1. Блокировки отношений;
  2. Блокировки строк;
  3. Блокировки других объектов и предикатные блокировки;
  4. Блокировки в оперативной памяти.

Хинты планера в postgresql

Известно, что SQL — декларативный язык, который указывает, «что» мы хотим выбрать из базы, а «как» это сделать — СУБД решает сама. Задачу выбора для SQL-запроса конкретного способа его выполнения(плана) решает планировщик запросов, который есть практически в любой СУБД. Но иногда он выбирает не самый лучший план. Многие коммерческие СУБД предоставляют на этот случай «хинты», которые позволяют в ручном режиме подсказывать базе, как лучше выполнить запрос. В Open Source СУБД PostgreSQL такого механизма не было.

И вот, наконец, случилось то, о чем многие мечтали и чего уже устали ждать, а другие боялись. Японские разработчики из NTT реализовали хинты планера PostgreSQL. Причем, им удалось это сделать, не меняя ядро, в виде отдельного модуля pg_hint_plan, поддерживающего версии PostgreSQL 9.1 и 9.2. Модуль реализует хинты, позволяющие устанавливать методы сканирования и соединения таблиц, установку значений GUC. За деталями установки и использования добро пожаловать под кат.

С сайта можно скачать архивы исходников отдельно под версии 9.1 и 9.2, которые, правда, не отличаются абсолютно ничем и одинаково собираются под обе версии. Ну да ладно. Сборка и установка модуля проблем не вызывает: make && make install. Для сборки потребуется dev-пакет PostgreSQL от вашего любимого дистрибутива. Для того, чтобы PostgreSQL подхватил модуль, никакого SQL выполнять не нужно, достаточно добавить pg_hint_plan в переменную shared_preload_libraries в файле postgresql.conf (вместо этого можно подгружать модуль в каждую сессию, где это необходимо, с помощью команды LOAD). После перезапуска сервера станут доступны три новые GUC переменные: pg_hint_plan.enable_hint, pg_hint_plan.debug_print, pg_hint_plan.parse_messages. Первая из них отвечает за доступность хинтов (по умолчанию включены), оставшиеся две — за логирование.

Хинты указываются в комментариях к запросу, оформленных с помощью /* и */. Чтобы комментарий интерпретировался как хинт, у него в начале должен стоять знак , например /* SeqScan(t1) */. Хинты бывают следующих видов.

Хинты, отвечающие за метод сканирования таблицы

  • SeqScan (имя таблицы)
  • TidScan (имя таблицы)
  • IndexScan (имя таблицы [имя индекса])
  • IndexOnlyScan (имя таблицы [имя индекса])
  • BitmapScan (имя таблицы [имя индекса])
  • NoSeqScan (имя таблицы)
  • NoTidScan (имя таблицы)
  • NoIndexScan (имя таблицы)
  • NoIndexOnlyScan (имя таблицы)
  • NoBitmapScan (имя таблицы)

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

Хинты, отвечающие за метод соединение таблиц

  • NestLoop (список имен таблиц)
  • HashJoin (список имен таблиц)
  • MergeJoin (список имен таблиц)
  • NoNestLoop (список имен таблиц)
  • NoHashJoin (список имен таблиц)
  • NoMergeJoin (список имен таблиц)

Список имен таблиц указывается через пробел. Он чувствителен к порядку, то есть соединение осуществляется именно в том порядке, в котором таблицы указаны.

Также отдельно выделены два хинта:

  • Leading (список имен таблиц) — устанавливает порядок соединения таблиц без указания конкретного способа соединения
  • Set(GUC значение) — устанавливает значение GUC переменной на время выполнения запроса. Вобщем-то никакой новой функциональности не несет, GUC и так можно было установить, просто хинт позволяет сделать это более лаконично (и быстро?).

Настало время попробовать всё это в деле. Создадим тестовые таблицы, индексы, соберем статистику.

CREATE TABLE test1 AS (SELECT id, (random()*1000)::int AS id_2, random() AS value1, random() AS value2 FROM generate_series(1,1000000) id);
CREATE TABLE test2 AS (SELECT id, random() AS value FROM generate_series(1,1000) id);
CREATE INDEX test1_id_idx ON test1 (id);
CREATE INDEX test1_id_2_idx ON test1 (id_2);
CREATE INDEX test1_value1_idx ON test1 (value1);
CREATE INDEX test1_value2_idx ON test1 (value2);
CREATE INDEX test2_id_idx ON test2 (id);
CREATE INDEX test2_value_idx ON test2 (value);
VACUUM ANALYZE;

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

SELECT * FROM test1 WHERE value1 BETWEEN 0.5 and 0.505 AND value2 BETWEEN 0.6 and 0.61;

Планер решает объединить результаты сканирования индексов по каждому из полей с помощью Bitmap Scan.

                                                                                QUERY PLAN                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test1  (cost=319.82..514.76 rows=52 width=24) (actual time=9.575..9.736 rows=59 loops=1)
   Recheck Cond: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision) AND (value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision))
   ->  BitmapAnd  (cost=319.82..319.82 rows=52 width=0) (actual time=9.529..9.529 rows=0 loops=1)
         ->  Bitmap Index Scan on test1_value1_idx  (cost=0.00..113.54 rows=5318 width=0) (actual time=2.839..2.839 rows=5072 loops=1)
               Index Cond: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision))
         ->  Bitmap Index Scan on test1_value2_idx  (cost=0.00..206.00 rows=9764 width=0) (actual time=5.385..5.385 rows=10070 loops=1)
               Index Cond: ((value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision))
 Total runtime: 9.805 ms

Однако мы можем заставить его использовать обычный Index Scan.

/*  IndexScan(test1) */ SELECT * FROM test1 WHERE value1 BETWEEN 0.5 and 0.505 AND value2 BETWEEN 0.6 and 0.61;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test1_value1_idx on test1  (cost=0.00..15198.71 rows=52 width=24) (actual time=0.124..10.704 rows=59 loops=1)
   Index Cond: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision))
   Filter: ((value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision))
 Total runtime: 10.776 ms

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

/*  IndexScan(test1 test1_value2_idx) */ SELECT * FROM test1 WHERE value1 BETWEEN 0.5 and 0.505 AND value2 BETWEEN 0.6 and 0.61;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test1_value2_idx on test1  (cost=0.00..22463.60 rows=52 width=24) (actual time=0.787..15.757 rows=59 loops=1)
   Index Cond: ((value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision))
   Filter: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision))
 Total runtime: 15.816 ms
(4 rows)

Пример посложнее. Соединение двух таблиц с фильтрацией по полю одной таблицы, сортировкой по полю другой и LIMIT.

SELECT * FROM test1 t1 JOIN test2 t2 ON t1.id_2 = t2.id WHERE t2.value BETWEEN 0.5 AND 0.51 ORDER BY t1.value1 LIMIT 100;

Планер выбирает план c Index Scan по test1_value1_idx и Nested Loop.

                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4.33..2149.77 rows=100 width=36) (actual time=0.274..34.784 rows=100 loops=1)
   ->  Nested Loop  (cost=4.33..171467.82 rows=7992 width=36) (actual time=0.271..34.753 rows=100 loops=1)
         Join Filter: (t1.id_2 = t2.id)
         ->  Index Scan using test1_value1_idx on test1 t1  (cost=0.00..51457.05 rows=1000000 width=24) (actual time=0.022..10.338 rows=11873 loops=1)
         ->  Materialize  (cost=4.33..10.80 rows=8 width=12) (actual time=0.000..0.001 rows=8 loops=11873)
               ->  Bitmap Heap Scan on test2 t2  (cost=4.33..10.76 rows=8 width=12) (actual time=0.035..0.046 rows=8 loops=1)
                     Recheck Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
                     ->  Bitmap Index Scan on test2_value_idx  (cost=0.00..4.33 rows=8 width=0) (actual time=0.026..0.026 rows=8 loops=1)
                           Index Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
 Total runtime: 34.870 ms

Предположим, мы хотим использовать другой тип соединения таблиц: HashJoin.

/*  HashJoin(t1 t2) */ EXPLAIN ANALYZE SELECT * FROM test1 t1 JOIN test2 t2 ON t1.id_2 = t2.id WHERE t2.value BETWEEN 0.5 AND 0.51 ORDER BY t1.value1 LIMIT 100;

Планер подчиниться, добавив внутрь Bitmap Index Scan по test2, а снаружи — сортировку с Limit.

                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=20516.23..20516.48 rows=100 width=36) (actual time=156.219..156.230 rows=100 loops=1)
   ->  Sort  (cost=20516.23..20536.21 rows=7992 width=36) (actual time=156.217..156.225 rows=100 loops=1)
         Sort Key: t1.value1
         Sort Method: top-N heapsort  Memory: 32kB
         ->  Hash Join  (cost=10.86..20210.78 rows=7992 width=36) (actual time=0.248..154.286 rows=7889 loops=1)
               Hash Cond: (t1.id_2 = t2.id)
               ->  Seq Scan on test1 t1  (cost=0.00..16370.00 rows=1000000 width=24) (actual time=0.013..63.210 rows=1000000 loops=1)
               ->  Hash  (cost=10.76..10.76 rows=8 width=12) (actual time=0.066..0.066 rows=8 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                     ->  Bitmap Heap Scan on test2 t2  (cost=4.33..10.76 rows=8 width=12) (actual time=0.044..0.057 rows=8 loops=1)
                           Recheck Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
                           ->  Bitmap Index Scan on test2_value_idx  (cost=0.00..4.33 rows=8 width=0) (actual time=0.034..0.034 rows=8 loops=1)
                                 Index Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
 Total runtime: 156.335 ms

Если, к примеру, задать тип соединения MergeJoin и IndexScan по индексу test2_value_idx, то планер, опять таки добавит необходимые сортировки и Limit.

/*  MergeJoin(t1 t2) IndexScan (t2 test2_value_idx) */ EXPLAIN ANALYZE SELECT * FROM test1 t1 JOIN test2 t2 ON t1.id_2 = t2.id WHERE t2.value BETWEEN 0.5 AND 0.51 ORDER BY t1.value1 LIMIT 100;
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=54410.09..54410.34 rows=100 width=36) (actual time=446.031..446.041 rows=100 loops=1)
   ->  Sort  (cost=54410.09..54430.07 rows=7992 width=36) (actual time=446.029..446.032 rows=100 loops=1)
         Sort Key: t1.value1
         Sort Method: top-N heapsort  Memory: 32kB
         ->  Merge Join  (cost=71.79..54104.65 rows=7992 width=36) (actual time=12.501..444.501 rows=7889 loops=1)
               Merge Cond: (t1.id_2 = t2.id)
               ->  Index Scan using test1_id_2_idx on test1 t1  (cost=0.00..51460.24 rows=1000000 width=24) (actual time=0.033..377.392 rows=900401 loops=1)
               ->  Sort  (cost=24.52..24.54 rows=8 width=12) (actual time=0.074..0.545 rows=6927 loops=1)
                     Sort Key: t2.id
                     Sort Method: quicksort  Memory: 25kB
                     ->  Index Scan using test2_value_idx on test2 t2  (cost=0.00..24.40 rows=8 width=12) (actual time=0.026..0.047 rows=8 loops=1)
                           Index Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
 Total runtime: 446.182 ms

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

  • Настраивали ли Вы параметры планера *_cost, effective_cache_size, geqo* и т.д. в соответствии с имеющимися ресурсами сервера?
  • На каких данных у Вас получился план, который выполняется быстрее? На продакшене такое же распределение данных? Вы готовы переписывать хинты, когда распределение данных изменится?
  • План выполнился быстрее, когда всё, что надо, оказалось в кэше? А на момент выполнения данного запроса на продакшене всё тоже будет в кэше?
Дополнительный анализ:  Вагоны уезжают от ремонтов – Газета Коммерсантъ № 34 (6996) от 01.03.2021

И всё-таки хинты очень полезны как-минимум в двух ситуациях:

  • Хочется глубже понять работу планера/executer’а, получить ответы на вопросы «Что было бы если?».
  • Иногда планер всё таки сильно ошибается. Например, когда есть сильная корреляция между полями таблицы, которую он не умеет учитывать. Из-за этого получается неверная оценка селективности условия, и может быть плохой план.

P.S. За наводку на модуль спасибо Олегу Бартунову (aka

zen

)!

Часть i. r извлекает и рисует

Конечно, PostgreSQL с самого начала создавалась как универсальная СУБД, а не как специализированная OLAP-система. Но один из больших плюсов Постгреса — в поддержке языков программирования, с помощью которых из него можно сделать что угодно. По изобилию встроенных процедурных языков ему просто нет равных. PL/R — серверная реализация

R

— любимого языка аналитиков — один из них. Но об этом позже.

R – удивительный язык со своеобразными типами данных — list, например, может включать в себя не только данные разных типов, но и функции (вообще, язык эклектичный, и говорить о принадлежности его к определенному семейству не будем, чтобы не порождать отвлекающие дискуссии).

В нем есть симпатичный тип данных data.frame, который подражает таблице РСУБД — это матрица, у которой столбцы содержат разные типы данных, общие на уровне столбца. Поэтому (и по другим причинам) работать в R с базами данных довольно удобно.

Мы будем работать в командной строке в среде RStudio и соединяться с PostgreSQL через драйвер ODBC RpostgreSQL. Их несложно установить.

Поскольку R создавался как этакий вариант языка S для тех, кто занимается статистикой, то и мы приведем примеры из простенькой статистики с простенькой графикой. У нас нет цели знакомить с языком, но есть цель показать взаимодействие R и PostgreSQL.

Обрабатывать данные, хранящиеся в PostgreSQL, можно тремя путями.

Во-первых, можно выкачать данные из базы любыми удобными средствами, упаковать их, скажем, в JSON – их понимает R – и обрабатывать дальше в R. Это обычно не самый эффективный способ и точно не самый интересный, мы его рассматривать здесь не будем.

Во-вторых, можно связываться с базой – читать из нее и сбрасывать данные в нее – из среды R как из клиента, используя драйвер ODBC/DBI, обрабатывая данные в R. Мы покажем, как это делается.

И, наконец, можно делать обработку средствами R уже на сервере базы, используя PL/R как встроенный процедурный язык. Это имеет смысл в ряде случаев, так как в R есть, например, удобные средства агрегирования данных, которых нет в pl/pgsql. Мы покажем и это.

Распространенный подход это использование 2-го и 3-го варианта в разных фазах проекта: сначала отладка кода как внешней программы, а затем перенос ее внутрь базы.

Начнём. R интерпретируемый язык. Поэтому можно действовать по шагам, а можно сбросить код в скрипт. Дело вкуса: примеры в этой статье коротенькие.

Сначала нужно, конечно, подключить соответствующий драйвер:

# install.packages("RPostgreSQL")
require("RPostgreSQL")
drv <- dbDriver("PostgreSQL")

Операция присвоения выглядит в R, как можно было заметить, своеобразно. Вообще в R a < — b значит то же, что и b -> a, но более распространен первый способ записи.

Базу данных возьмем готовую: демобазу авиаперевозок, которую используют учебных материалахPostgres Professional На этой странице можно выбрать вариант базы по вкусу (то есть по размеру) и почитать ее описание. Схему данных воспроизводим для удобства:

Предположим, что база установлена на сервере 192.168.1.100 и называется demo. Соединяемся:

Оцените статью
Аналитик-эксперт
Добавить комментарий

Adblock
detector