itmo_conspects

Лекция 10. Архитектура PostgreSQL, часть 2

Модель доступа

В PostgreSQL изначально была реализована дискреционная модель: владельцы объектов (таблиц, схем, баз данных и т.д.) самостоятельно определяют, кто и какие действия может выполнять с их объектами

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

Ролевая модель доступа (появилась в PostgreSQL 10): роль может быть назначена отдельному пользователю или группе. Роли удобны для централизованного управления правами

-- Создание роли с набором атрибутов
CREATE ROLE имя_роли WITH
    LOGIN          -- возможность входа
    CREATEDB       -- право создавать базы
    CREATEROLE     -- право изменять роли
    SUPERUSER      -- суперпользовательские права
    INHERIT;       -- унаследование прав от других ролей

Роли могут наследоваться (INHERIT) и передавать свои привилегии

Ролям можно присваивать следующие права для доступа к операциям над таблицами:

TRUNCATE быстрее DELETE, потому что:

Привилегии на базы данных:

По умолчанию PostgreSQL создаёт новую базу как копию template0 или template1

Схемы — логические контейнеры объектов внутри базы

Обработка SQL-запроса

  1. Лексический анализ

    На этом этапе сервер принимает SQL-текст запроса и анализирует его на наличие синтакcических ошибок. Результатом является дерево разбора (parse tree), представляющее структуру запроса

  2. Семантический разбор

    Здесь заменяются представления на их определения, обрабатываются правила, определенные для таблиц, а так же проверяются права у пользователя к этим объектам

  3. Планирование

    PostgreSQL может выполнять один и тот же запрос разными способами. Планировщик генерирует альтернативные планы (деревья с физическими операциями)

    Планировщик принимает переписанное дерево запроса и генерирует один или несколько вариантов выполнения запроса. Количество возможных планов экспоненциально зависит от количества соединяемых таблиц. Для сокращения пространства перебора традиционно используется алгоритм динамического программирования с эвристиками

    С помощью ключевого слова EXPLAIN можно узнать структуру плана запроса, а с помощью EXPLAIN ANALYZE можно, выполнив его, узнать реальные затраты

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

    Планы делятся на общие и частные. При выполнении подготовленных запросов план выполнения может быть сохранен и повторно использован

    Общий план, который строится один раз без учета конкретных значений параметров, предоставленных во время исполнения

    Частный план, который создается при каждом выполнении подготовленного запроса и учитывает конкретные значения параметров

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

  4. Выполнение

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

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

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

Статистика

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

PostgreSQL хранит статистику в системной таблице pg_statistic. Обычно используют представление pg_stats

Базовая статистика отношений хранится в таблице pg_class

pg_stats хранит:

Статистика собирается при анализе (ручном или автоматическом). Однако ввиду особой важности базовая статистика рассчитывается также при выполнении некоторых операций и уточняется при очистке

Для анализа случайно выбираются 300*default_statistics_target строк. Поскольку размер выборки, достаточной для построения статистики заданной точности, слабо зависит от объема анализируемых данных, размер таблицы не учитывается

Для уточнения оценки при неравномерном распределении собирается статистика по наиболее часто встречающимся значениям и частоте их появления. Представление pg_stats показывает два этих массива в столбцах most_common_vals и most_common_freq

Список частых значений используется и для оценки селективности условий с неравенствами. Например, для условия вида “столбец < значение” надо найти в most_common_vals все значения, меньшие искомого, и просуммировать частоты из most_common_freqs

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

Поле correlation показывается корреляцию между физическим расположением данных и логических порядком в смысле операций сравнения. Если значения хранятся строго по возрастанию, корреляция будет близка к 1; если по убыванию, то к -1. Если данные расположены хаотично, то корреляция близка к 0. Корреляция используется для оценки стоимости индексного сканирования

Методы доступа к таблице

Другое

Соединение можно делать:

PostgreSQL использует быструю сортировку (quicksort), чтобы выполнить операцию соединения. Размер выделенной памяти для сортировки контролируется параметром work_mem. Если объем данных превышает work_mem, PostgreSQL может производить сортировку с временным сохранением промежуточных результатов на диске

Агрегацию (вычисление значений, таких как COUNT, AVG и так далее) можно делать:

Shared buffers - область оперативной памяти, где PostgreSQL хранит копии страниц таблиц и индексов. Повторный доступ к данным из буфера ускоряет работу

Управляется параметром shared_buffers (рекомендуется ставить значение, равное 25–40% от объема оперативной памяти)

This site is open source. Improve this page.