Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
EXPLAIN ANALYZE SELECT num_tickets, count( * ) AS num_bookings FROM ( SELECT b.book_ref, count( * ) FROM bookings b, tickets t WHERE date_trunc( 'mon', b.book_date ) = '2016-09-01' AND t.book_ref = b.book_ref GROUP BY b.book_ref ) AS count_tickets( book_ref, num_tickets ) GROUP by num_tickets ORDER BY num_tickets DESC; 322 Контрольные вопросы и задания Мы экспериментировали с параметрами планировщика enable_hashjoin и enable_nestloop при наличии индекса по таблице tickets. SET enable_hashjoin = off; SET enable_nestloop = off; Однако полученные планы детально рассмотрены не были. Задание. Проанализируйте эти планы. Посмотрите, в каких случаях использу- ются и в каких не используются индексы по таблицам bookings и tickets. Вспомните о таком понятии, как селективность, т. е. доля строк, выбираемых из таблицы. 14. В столбцах таблиц могут содержаться значения NULL. При сортировке строк по значениям таких столбцов СУБД по умолчанию ведет себя так, как будто зна- чение NULL превосходит по величине любые другие значения. В результате по- лучается, что если задан возрастающий порядок сортировки, то значения NULL будут идти последними, если же порядок сортировки убывающий, тогда они бу- дут первыми. Принимая решение о создании индексов, нужно учитывать требу- емый порядок сортировки и желаемое расположение строк со значениями NULL в выборке (в ее начале или в конце). Давайте создадим таблицу, содержащую такое число строк, что использование индекса планировщиком становится очень вероятным, и выполним с этой таб- лицей ряд экспериментов. CREATE TABLE nulls AS SELECT num::integer, 'TEXT' || num::text AS txt FROM generate_series( 1, 200000 ) AS gen_ser( num ); SELECT 200000 Проиндексируем таблицу по числовому столбцу. Поскольку мы не указываем порядок сортировки явным образом, то по умолчанию он будет возрастающим, как если бы мы использовали ключевое слово ASC. CREATE INDEX nulls_ind ON nulls ( num ); CREATE INDEX Добавим в таблицу одну строку, содержащую значение NULL в индексируемом столбце: 323 Глава 10. Повышение производительности INSERT INTO nulls VALUES ( NULL, 'TEXT' ); INSERT 0 1 Теперь посмотрим, будет ли использоваться индекс в следующем запросе: EXPLAIN SELECT * FROM nulls ORDER BY num; Да, индекс используется. QUERY PLAN --------------------------------------------------------------------- Index Scan using nulls_ind on nulls (cost=0.42..5852.42 rows=200000 width=14) (1 строка) Вы можете убедиться, что строка со значением NULL окажется в выводе самой последней. Поскольку в нашей таблице очень много строк, воспользуемся пред- ложением OFFSET: SELECT * FROM nulls ORDER BY num OFFSET 199995; num | txt --------+------------ 199996 | TEXT199996 199997 | TEXT199997 199998 | TEXT199998 199999 | TEXT199999 200000 | TEXT200000 | TEXT (6 строк) Модифицируем запрос, явно указав, что значения NULL должны располагаться в самом начале выборки, и посмотрим, будет ли использоваться индекс теперь. EXPLAIN SELECT * FROM nulls ORDER BY num NULLS FIRST; 324 Контрольные вопросы и задания Теперь индекс не используется. Вместо этого выполняется последовательное сканирование таблицы и сортировка выбранных строк. QUERY PLAN -------------------------------------------------------------------- Sort (cost=24110.14..24610.14 rows=200000 width=14) Sort Key: num NULLS FIRST -> Seq Scan on nulls (cost=0.00..3081.00 rows=200000 width=14) (3 строки) Задание 1. Ниже приведена модифицированная команды выборки из таблицы nulls. Не выполняя эту команду, попытайтесь ответить, будет ли использо- ваться созданный нами индекс при выполнении такой выборки, а затем про- верьте вашу гипотезу на практике. EXPLAIN SELECT * FROM nulls ORDER BY num DESC NULLS FIRST; Обратите внимание на ключевое слово Backward в плане выполнения запроса. Что оно означает? Задание 2. Модифицируйте команду создания индекса таким образом, чтобы он использовался при выполнении следующей выборки: SELECT * FROM nulls ORDER BY num NULLS FIRST; Задание 3. Выполните аналогичные эксперименты, задавая убывающий по- рядок сортировки с помощью ключевого слова DESC и изменяя расположение значений NULL в выборке с помощью ключевых слов NULLS FIRST и NULLS LAST предложения ORDER BY. С помощью команды EXPLAIN ANALYZE посмот- рите, каким будет фактическое время выполнения команд. За дополнительной информацией обратитесь к описанию команды CREATE INDEX, приведенному в документации. 15. Обратитесь к запросам в главе 6. Выполните команду EXPLAIN для всех этих запросов и ознакомьтесь с планами, которые создаст планировщик. В планах могут встречаться наименования методов, которые не были рассмотрены в тек- сте главы, однако они должны быть вам интуитивно понятны. 325 Глава 10. Повышение производительности 16. В разделе документации 19.7 «Планирование запросов» приведены парамет- ры, с помощью которых можно влиять на решения, принимаемые планиров- щиком. В тексте главы мы уже говорили о параметрах, управляющих выбором способа соединения наборов строк, и показали простой пример. Также было сказано и о том, что при установке значений параметров enable_hashjoin, enable_mergejoin и enable_nestloop в off не накладывается полного за- прета на использование соответствующих методов. Вместо этого конкретному методу назначается очень высокая стоимость. Давайте проведем следующий эксперимент: запретим использование всех методов соединения наборов строк и выполним запрос, в котором соединяются две таблицы. SET enable_hashjoin = off; SET enable_mergejoin = off; SET enable_nestloop = off; Запрос выводит информацию о числе мест в самолетах всех моделей. EXPLAIN SELECT a.model, count( * ) FROM aircrafts a, seats s WHERE a.aircraft_code = s.aircraft_code GROUP BY a.aircraft_code; QUERY PLAN --------------------------------------------------------------------- GroupAggregate (cost=10000000000.41..10000000109.95 rows=9 width=56) Group Key: a.aircraft_code -> Nested Loop (cost=10000000000.41..10000000103.16 rows=1339 width=48) -> Index Scan using aircrafts_pkey on aircrafts a (cost=0.14..12.27 rows=9 width=48) -> Index Only Scan using seats_pkey on seats s (cost=0.28..8.61 rows=149 width=4) Index Cond: (aircraft_code = a.aircraft_code) (6 строк) Обратите внимание на оценки стоимости выполнения запроса. Резкое повы- шение оценок происходит именно в узле, отвечающем за соединение наборов строк. Эти оценки не означают, что время выполнения запроса будет стремить- ся к бесконечности. С помощью команды EXPLAIN ANALYZE выполните запрос и убедитесь в этом сами. 326 Контрольные вопросы и задания Задание. Самостоятельно ознакомьтесь с содержанием раздела документации 19.7 «Планирование запросов», а также раздела 14.3 «Управление планировщи- ком с помощью явных предложений JOIN» и проведите эксперименты с запро- сами, приведенными в главе 6 пособия, получая различные варианты планов и сравнивая их. Ваша задача — понять, как изменения значений этих параметров влияют на план выполнения запроса. Однако для того чтобы понимать, когда и почему нуж- но изменять значения конкретных параметров, правильно оценивать степень и направленность их влияния, понимать взаимосвязь параметров, требуется опыт и изучение документации. 17. Самостоятельно ознакомьтесь с разделом документации 14.2 «Статистика, ис- пользуемая планировщиком». 18. Команда EXPLAIN имеет опцию BUFFERS. Ознакомьтесь с ней самостоятельно по разделу документации 14.1 «Использование EXPLAIN». 19. При массовом вводе данных в базу данных производительность СУБД может снижаться по ряду причин, например, при наличии индексов они обновляются при вводе каждой новой строки в таблицу, а это требует дополнительных за- трат ресурсов. Для повышения производительности СУБД в подобных ситуаци- ях в документации предлагается ряд мер, например, удаление индексов перед началом массового ввода данных и пересоздание индексов после завершения такого ввода. Ознакомьтесь с этими мерами самостоятельно по разделу доку- ментации 14.4 «Наполнение базы данных». Смоделируйте ситуации, описанные в этом разделе документации, и выполните рекомендуемые действия. 327 Рекомендуемые источники 1. Гарсиа-Молина Г., Ульман Д. Д., Уидом Д. Системы баз данных. Полный курс: пер. с англ. — М.: Вильямс, 2003. — 1088 с. 2. Грофф Дж. Р., Вайнберг П. Н., Оппель Э. Дж. SQL. Полное руководство: пер. с англ. — 3-е изд. — М.: Вильямс, 2015. — 960 с. 3. Дейт К. Дж. Введение в системы баз данных: пер. с англ. — 8-е изд. — М.: Вильямс, 2005. — 1328 с. 4. Новиков Б., Домбровская Г. Настройка приложений баз данных. — СПб.: БХВ- Петербург, 2012. — 240 с. 5. Новиков Б., Горшкова Е., Графеева Н. Основы технологий баз данных. — М.: Postgres Professional, 2018. — 182 с. 6. Селко Д. Стиль программирования Джо Селко на SQL: пер. с англ. — М.: Русская редакция; СПб.: Питер, 2006. — 206 с. 7. Официальный сайт PostgreSQL http://www.postgresql.org 8. Postgres Professional http://postgrespro.ru 9. Учебные курсы Postgres Professional http://postgrespro.ru/education/courses 328 Предметный указатель A Aggregate, узел плана 301 ALL 205 ALTER MATERIALIZED VIEW 141 ALTER TABLE 117–122, 212, 220 ALTER VIEW 141 ANALYZE 312 ANY 205 ARRAY, литерал 66 array_append, функция 65 array_cat, функция 90 array_length, функция 169, 195 array_position, функция 67 array_prepend, функция 65 array_remove, функция 66 AS 152–153 AT TIME ZONE 127 avg, функция 168 B BEGIN 259 BETWEEN 148 bigint, тип 51 bigserial, тип 54 Bitmap Scan, узел плана 300 boolean, тип 63, 87–88 C CASCADE 116 CASE, условное выражение 152, 237 char, тип 54 character varying, тип 54 character, тип 54 CHECK 96 COALESCE, условное выражение 193 COMMENT 108 COMMIT 263 CONSTRAINT 101 COPY 217–218, 227 count, функция 46, 173, 195, 207 CREATE INDEX 243 CREATE MATERIALIZED VIEW 128 CREATE TABLE 27, 32, 107 CREATE TEMP TABLE 211 CREATE VIEW 123 CROSS JOIN 159, 237 current_date, функция 57 current_schema, функция 133 current_time, функция 59 current_timestamp, функция 62 D date, тип 56 datestyle, параметр 79–82 date_trunc, функция 62, 85 decimal, тип 52 DEFAULT 54, 96, 133–135 default_transaction_isolation, параметр 259 DELETE 41, 49, 224–226 USING 225 DISTINCT 150, 176, 179, 220 double precision, тип 52, 74–76 DROP INDEX 245 DROP MATERIALIZED VIEW 130 DROP TABLE 37, 115 DROP VIEW 124, 128 329 Предметный указатель E END 263 EXCEPT 167 EXISTS 179 EXPLAIN 296–297 ANALYZE 306 extract, функция 62, 86 F false, значение 63 FILTER 200 first_value, функция 174 float, тип 53 FOR SHARE 286 FOR UPDATE 278 FOREIGN KEY 43, 100–104, 115, 121 FROM 38, 176, 181 FULL OUTER JOIN 162 G GREATEST, условное выражение 193 GROUP BY 46, 168, 176 H Hash Join, узел плана 303–304 HAVING 170, 176, 183 I IF EXISTS 117 IN 177, 229 Index Only Scan, узел плана 300 Index Scan, узел плана 299–300 Infinity, значение 53, 75 INSERT 37–38, 213–217 ON CONFLICT 215–217, 227 integer, тип 51 INTERSECT 166 interval, тип 61, 84–85 intervalstyle, параметр 85 J JOIN 152 json, тип 68 jsonb, тип 68 jsonb_set, функция 72, 92 L LEAST, условное выражение 193 LEFT OUTER JOIN 160 left, функция 210 length, функция 210 LIKE 146, 192, 254 LIMIT 151, 176, 234, 246, 297 LOCK TABLE 279 lower, функция 248 M max, функция 168 Merge Join, узел плана 304–305 min, функция 168 N NaN, значение 53, 74, 76 Nested Loop, узел плана 302–303 NOT NULL 97 NULL, значение 18, 103, 136 отличие от пустой строки 138 при сортировке 247, 323 NULLIF, условное выражение 193 numeric, тип 52, 73–74 O OFFSET 151, 176, 324 ON CONFLICT 215–217, 227 ON DELETE CASCADE 102 NO ACTION 102 RESTRICT 102 SET DEFAULT 103 SET NULL 103 330 Предметный указатель ON UPDATE CASCADE 104 ORDER BY 39, 46, 48, 149, 176, 234, 246 OVER 173 P pg_dump 34 PRIMARY KEY 34–44, 99, 107–115, 242 psql 26–27, 32–37 автодополнение 107 выход 29 завершение комады SQL 35 описание индекса 242, 252 описание представления 127 описание таблицы 36, 107 расширенный формат 127 редактирование буфера 35 секундомер 243 список индексов 245 список схем 131 список таблиц 27 способ ввода команд 34 справка 27, 32 терминал в Windows 26 public, схема 132 R rank, функция 173, 225, 232 Read Committed, уровень изоляции 258, 261–265, 275, 280–283 особенности UPDATE 280–283 Read Uncommitted, уровень изоляции 258–261 real, тип 52, 74–76 REFERENCES 101 REFRESH MATERIALIZED VIEW 129 Repeatable Read, уровень изоляции 258, 265–269 RETURNING 213, 219, 224, 226 RIGHT OUTER JOIN 161 right, функция 210 ROLLBACK 261 round, функция 149 S search_path, параметр 105, 131 SELECT 38, 145–210 Seq Scan, узел плана 296–299 serial, тип 53, 76–78 Serializable, уровень изоляции 258, 269–275, 286–291 SET 80, 132, 305 SHOW 79, 132 SIMILAR TO 193 smallint, тип 51 smallserial, тип 54 Sort, узел плана 298, 309 string_agg, функция 182 strpos, функция 208 substr, функция 208 sum, функция 206 T text, тип 54 time, тип 58, 78 with time zone (timetz) 58 timestamp, тип 60 with time zone (timestamptz) 60 to_char, функция 57, 82 true, значение 63 TRUNCATE 226 U UNION 166, 188 UNIQUE 98, 110–122 unnest, функция 68, 196 331 UPDATE 40, 48, 219–223 FROM 223 особенности на уровне Read Committed 280–283 V VALUES 164, 176 varchar, тип 54 W WHERE 40, 145, 176 WINDOW 174 WITH 186, 201–203 WITH ORDINALITY 198 А Аномалия сериализации 257 Атомарность, свойство транзакции 256, 277 Атрибут 16 Б Блокировка 256, 278–279 на уровне строк 278, 285 на уровне таблицы 279 предикатная 286 Г Группировка 46, 168 «Грязное» чтение, аномалия 257, 259 Д Демо-база «Авиаперевозки» описание 19–21, 125, 129, 156, 189 схема 22 установка 27 функция bookings.now 207, 229, 276 Демо-база «Успеваемость» 14, 16, 95, 104 Денормализация 138, 320–322 Долговечность, свойство транзакции 256, 277 З Значение по умолчанию 54, 96, 103, 133–135 И Изолированность, свойство транзакции 256, 277 Индекс 36, 241–254, 286 метод доступа 293–294 описание в psql 242, 252 по выражению 248 по нескольким столбцам 246 список в psql 245 уникальный 242, 247 ускорение запроса 244, 314 частичный 248, 253 К Карта видимости 294 Класс оператора 254 Ключ альтернативный 17 внешний 17, 43–44, 100, 104, 115, 121 естественный 33, 42, 110 первичный 17, 34–44, 99, 107–115 потенциальный 17 составной 42, 113 суррогатный 33, 53, 109 уникальный 98, 110–122 Колонка (столбец) 15 Комментарии в SQL 106 Кортеж 16 332 Предметный указатель М Метод доступа 293–294 исключительно по индексу 294, 300 по битовой карте 294, 300 по индексу 293, 299–300 последовательный 293, 296–299 Многоверсионность 256 Модель данных реляционная 13 Н Неповторяющееся чтение, аномалия 257, 263–268 О Общее табличное выражение 186–192, 233, 317 рекурсивное 187–189, 201–203 с командой DELETE 224 с командой INSERT 213–215 с командой UPDATE 219 Ограничение целостности 16, 96–104, 255 CHECK 33, 96, 109–111, 113, 118, 135 NOT NULL 33, 97, 118, 135 индексная поддержка 36, 98–99, 242 каскадное обновление 104, 137 каскадное удаление 43, 102 каскадное удаление объектов 116, 141 первичный ключ 17, 99, 242 ссылочное (внешний ключ) 17, 43–44, 100–104, 115, 121 уникальный ключ 98, 136, 215, 242 Операторы SQL DDL 95–144 DML 145–239 классификация 18 Отношение 15 как результат реляционных операций 156 кардинальное число 16 степень 16 П План выполнения запроса 155, 296 оценка стоимости 297, 317, 326 оценка числа строк 297 расхождение оценок и факта 315 фактические затраты 307 Планировщик 155, 241, 296 управление 305 Подзапрос 177–192 в предложении FROM 181–183 в предложении HAVING 183–184 в предложении SELECT 180–181 в предложении WHERE 177–180, 205 вложенный 184–185, 191, 230 коррелированный 179, 185, 230, 315, 319 некоррелированный 177 общее табличное выражение 186–192, 233, 317 рекурсивный 187–189, 201–203 скалярный 177, 188 Последовательность 54, 111 Потерянное обновление, аномалия 257, 261–263, 283–284 333 Предметный указатель Представление 45, 123–131, 234 вертикальное и горизонтальное 142 материализованное 128, 142, 189, 320 обновляемое 141 описание в psql 127 применение 130 Процессор языка запросов 15 Псевдоним 153, 157 Р Регулярные выражения POSIX 147, 254 SQL 193 С Селективность выборки 246, 251, 293 Сериализация 257 ошибки 265, 268–275, 286 Система управления базами данных (СУБД) 14 установка 25 Снимок данных 256 Согласованность, свойство транзакции 256, 277 Соединение 152, 294, 315, 319 без использования JOIN 155 в команде DELETE 225 в команде UPDATE 223 внешнее 159–162, 189 левое 160 полное 162 правое 161 декартово 159, 237 на основе неравенства 158 таблицы с собой 156, 158 эквисоединение 153, 295 Сортировка 39, 46, 48, 149, 241–247, 295, 298, 323 в памяти и внешняя 309 Способ соединения 294–295 вложенный цикл 295, 302–303 слиянием 295, 304–305 хешированием 295, 303–304 Статистика 297, 312 Столбец (колонка) 15 вычисляемый 148, 320 Страница 293 Строка 15 версия 256 Схема 36, 108, 131–133 public 132 путь поиска 105, 131 список в psql 131 Т Таблица 15 виртуальная 187, 202, 236 виртуальная (VALUES) 164 временная 211, 220, 321 использование для журналирования 211 описание в psql 36, 107 связь 1:1 114, 162 список в psql 27 ссылающаяся и ссылочная 17, 43, 100, 106, 115, 121 Тестовые данные, генерация 235 Типы данных, приведение 44, 57, 83, 119, 237 Типы данных: JSON 68, 92–93, 112, 143, 210 вхождение 70 значение по ключу 71, 92 конкатенация 71, 93 проверка ключа 71 удаление ключа 93 334 Предметный указатель Типы данных: даты и времени 56, 78–87, 191 арифметические операции 84 временные отметки 60, 110, 112, 127 время суток 58 дата 56 интервалы 61 формат ввода и вывода 79–82, 85 часовой пояс 58, 61, 110, 112, 127, 150 Типы данных: логический 63, 87–88 Типы данных: массивы 44, 64, 90–92, 169, 192, 195 вхождение 67, 196 конкатенация 65, 90 многомерные 92 пересечение 67 преобразование в таблицы 68, 196 срезы 66 Типы данных: символьные 33, 42, 54, 112, 199, 237 использование кавычек 55–56 конкатенация 237 пустые строки 138 Типы данных: числовые 33, 51 с плавающей точкой 52, 74, 108 фиксированной точности 52, 73, 112–113 целочисленные 51, 114, 118 целочисленные с автоинкрементом 53, 76–78, 109, 111 Транзакция 18, 255–291 Триггер 276 Ф Фантомное чтение, аномалия 257, 265–268, 284–285 Функция агрегатная 46, 168–170, 182, 200, 301 оконная 170–175, 200, 232, 319 335 Моргунов Евгений Павлович POSTGRESQL. ОСНОВЫ ЯЗЫКА SQL Учебноое пособие При поддержке Postgres Professional http://postgrespro.ru Редакторы Е. В. Рогов, П. В. Лузанов Подписано в печать 31.10.18. Формат 70×100 1 / 16 . Печать офсетная. Усл. печ. л. 27,09. Тираж 1000 экз. Заказ №7782. «БХВ-Петербург», 191036, Санкт-Петербург, Гончарная ул., 20. Отпечатано с готового оригинал-макета ООО «Принт-М», 142300, М. О., г. Чехов, ул. Полиграфистов, д. 1 |