• Индексы в PostgreSQL — 2


      Интерфейс


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

      Свойства


      Все свойства методов доступа представлены в таблице pg_am (am — access method). Из этой таблицы можно получить и сам список доступных методов:

      postgres=# select amname from pg_am;
       amname
      --------
       btree
       hash
       gist
       gin
       spgist
       brin
      (6 rows)

      Хотя к методам доступа можно с полным правом отнести и последовательное сканирование, исторически сложилось так, что оно отсутствует в этом списке.

      В версиях PostgreSQL 9.5 и более старых каждое свойство было представлено отдельным полем таблицы pg_am. Начиная с версии 9.6 свойства опрашиваются специальными функциями и разделены на несколько уровней:

      • свойства метода доступа — pg_indexam_has_property,
      • свойства конкретного индекса — pg_index_has_property,
      • свойства отдельных столбцов индекса — pg_index_column_has_property.

      Разделение на уровни метода доступа и индекса сделано с прицелом на будущее: в настоящее время все индексы, созданные на основе одного метода доступа, всегда будут иметь одинаковые свойства.

      Читать дальше →
    • Индексы в PostgreSQL — 1

        Предисловие


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

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

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

        В этой части мы поговорим про разделение сфер ответственности между общим механизмом индексирования, относящимся к ядру СУБД, и отдельными методами индексного доступа, которые в PostgreSQL можно добавлять как расширения. В следующей части мы рассмотрим интерфейс метода доступа и такие важные понятия, как классы и семейства операторов. После такого длинного, но необходимого введения мы подробно рассмотрим устройство и применение различных типов индексов: Hash, B-tree, GiST, SP-GiST, GIN и RUM, BRIN и Bloom.

        Индексы


        Индексы в PostgreSQL — специальные объекты базы данных, предназначенные в основном для ускорения доступа к данным. Это вспомогательные структуры: любой индекс можно удалить и восстановить заново по информации в таблице. Иногда приходится слышать, что СУБД может работать и без индексов, просто медленно. Однако это не так, ведь индексы служат также для поддержки некоторых ограничений целостности.
        Читать дальше →
      • И снова о рекурсивных запросах

        • Tutorial
        В этой заметке речь пойдет о том, как писать рекурсивные запросы. Тема эта поднималась не раз и не два, но обычно все ограничивается простыми «деревянными» случаями: спуститься от вершины до листьев, подняться от вершины до корня. Мы же займемся более сложным случаем произвольного графа.

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

        Для упражнения будем использовать демо-базу, подробно описанную ранее, и попробуем написать в ней запрос для поиска кратчайшего пути из одного аэропорта в другой.
        Читать дальше →
      • Демонстрационная база данных для PostgreSQL

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


          Сразу приведу ссылку на полное описание (там же написано, где взять демо-базу и как ее установить).


          image

          Читать дальше →
        • Обработка запросов в Oracle и PostgreSQL: следствия одного решения

            Обработка запросов SQL и  в Оракле, и в Постгресе имеет много общего. Так или иначе, надо выполнить синтаксический разбор, проверить семантику (для чего потребуется метаинформация, и не важно, называется ли это «словарь данных» или «системный каталог»), выполнить какие-то преобразования, построить оптимальный план выполнения (в обеих системах основанный на стоимости, а следовательно требующий заранее собранной статистики).

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

            В статье мы попытаемся проследить, как из-за разницы в одном архитектурном решении логически следует совершенно разная идеология работы в запросами в двух СУБД.

            Приведенные примеры (которые выполнялись на версиях Oracle 11.2 XE и PostgreSQL 9.4) содержат время выполнения запросов. Нас интересуют только относительные величины: во сколько раз изменилось время выполнения после внесения в запрос тех или иных изменений. При этом абсолютные цифры могут отличаться на порядки в зависимости от аппаратуры, нагрузки и настроек. Чтобы не давать повод для бессмысленных выводов на их основании, все абсолютные значения в статье отмасштабированы так, чтобы один из запросов составлял в обеих системах 10 секунд.
            Читать дальше →