Работа с временными таблицами

Оптимизация запросов в PostgreSQL

Amy McCarty

Instructor

О временных таблицах

Что?

Таблица с ограниченным сроком жизни

Зачем?

Временное хранилище
Сеанс базы данных
Несколько запросов
Данные конкретного пользователя
Медленные таблицы

Как?

CREATE TEMP TABLE name AS

Структура временной таблицы

holiday	holiday_type	country_code
Epiphany	religious	CZE
Epiphany	religious	FRA
Epiphany	religious	USA
Thanksgiving	secular	USA

CREATE TEMP TABLE usa_holidays AS
  SELECT holiday, holiday_type
  FROM world_holidays
  WHERE country_code = 'USA';

Праздники США

holiday	holiday_type
Epiphany	religious
Thanksgiving	secular

Большие медленные таблицы

Медленно из-за большого числа записей

Статистика таблицы	World Holidays	USA Holidays
Тип	table	temp table
# Строк	591 444	25

Сложные медленные представления

Медленно из-за логики представления

Диаграмма: таблицы отдельных стран передают данные в представление World Holidays

Статистика таблицы	World Holidays	USA Holidays
Тип	view	temp_table
# Строк	591 444	25
Источники	195	1

Таблицы содержат данные
Представления содержат инструкции по получению данных

Объединение нескольких таблиц с одной

CREATE TEMP TABLE usa_holidays AS
  SELECT holiday, holiday_type
  FROM world_holidays
  WHERE country_code = 'USA';

WITH religious AS 
(   SELECT usa.holiday, r.initial_yr
     , r.celebration_dt
    FROM religious r
    INNER JOIN usa_holidays usa 
      USING (holiday)  )
, secular AS 
(   SELECT usa.holiday, s.initial_yr
     , s.celebration_dt
    FROM secular s
    INNER JOIN usa_holidays usa
      USING (holiday)  )
, ...

ANALYZE

1 CREATE TEMP TABLE usa_holidays AS
2 SELECT holiday, holiday_type
3 FROM world_holidays
4 WHERE country_code = 'USA';
5 
6 ANALYZE usa_holidays;
7 
8 SELECT * FROM usa_holidays

Планировщик запросов (шаги выполнения)

Несколько поваров вокруг большого котла

Статистика из pg_statistics
Оценки времени выполнения

Давайте потренируемся!

Оптимизация запросов в PostgreSQL