Задание 1

Создать таблицы в созданной Базе Данных PostgreSQL:

1. customer

                                        
   Столбец    |          Тип           |
--------------+------------------------+
 c_custkey    | integer                |
 c_name       | character varying(25)  |
 c_address    | character varying(40)  |
 c_nationkey  | integer                |
 c_phone      | character(15)          |
 c_acctbal    | numeric(15,2)          |
 c_mktsegment | character(10)          |
 c_comment    | character varying(117) |
 n_emp        | character(2)           |


2. nation

  Столбец   |          Тип           |
------------+------------------------+
n_nationkey | integer                |
n_name      | character(25)          |
n_regionkey | integer                |
n_comment   | character varying(152) |
n_emp       | character(2)           |


3. region

  Столбец   |          Тип           |
------------+------------------------+
r_regionkey | integer                |
r_name      | character(25)          |
r_comment   | character varying(152) |
n_emp       | character(2)           |


4. part

   Столбец    |          Тип          |
--------------+-----------------------+
p_partkey     | integer               |
p_name        | character varying(55) |
p_mfgr        | character(25)         |
p_brand       | character(10)         |
p_type        | character varying(25) |
p_size        | integer               |
p_container   | character(10)         |
p_retailprice | numeric(15,2)         |
p_comment     | character varying(23) |
n_emp         | character(2)          |


5. partsupp

   Столбец    |          Тип           |
--------------+------------------------+
ps_partkey    | integer                |
ps_suppkey    | integer                |
ps_availqty   | integer                |
ps_supplycost | numeric(15,2)          |
ps_comment    | character varying(199) |
n_emp         | character(2)           |


6. supplier

  Столбец   |          Тип           |
------------+------------------------+
s_suppkey   | integer                |
s_name      | character(25)          |
s_address   | character varying(40)  |
s_nationkey | integer                |
s_phone     | character(15)          |
s_acctbal   | numeric(15,2)          |
s_comment   | character varying(101) |
n_emp       | character(2)           |

Задание 2

Создать вью v1 в которой будут отображаться схемы и имена всех вью в текущей базе данных. Для получения этой информации нужно использовать pg_class и pg_namespace.

Задание 3

Постройте план запроса выполняющего объединение таблиц заказов и покупателей в схеме tpch1 по ключу покупателя. Какова оценка количества строк которое вернет этот запрос?

Задание 4

Сколько строк оптимизатор планирует получить при сканировании таблицы customer?

Задание 5

Подсчитайте количество строк в таблице customer. Почему количество строк в листовом узле плана задания 3 не совпадает с фактическим количеством строк в таблице?

• У оптимизатора нет актуальной статистики по таблице
• Оптимизатор пишет в план количество строк на одном сегменте
• Оптимизатор использует масштабирующие коэффициенты для оценки количества строк

Задание 6

Выполните explain analyze для запроса из задания 3.

Почему значение rows для фактического выполнения (в скобках с actual time) может отличаться от значений в плане (в скобках с cost)?

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

Задание 7

Поскольку количество строк для фактического выполнения (в скобках с actual time) отличается от значений в плане (в скобках с cost) не очень сильно (1-2 % или меньше), какое предположение мы можем сделать о статистике?

• Статистика устарела, ее нужно обновить
• Необходимо настроить регулярный сбор статистики один раз в день
• Статистика достаточно актуальна, ей можно доверять

Задание 8

Выполните оценку фактических затрат на выполнение запроса аналогичного запросу в задании 3 используя вместо таблицы customer таблицу customer_1. Сравните фактические планы для запросов с таблицей customer и customer_1. Посчитайте количество строк в таблицах. Какое предположение можно сделать относительно таблицы customer_1?

• Таблица customer_1 перекошена, поэтому на одном из сегментов находится большее количество строк, а таблица customer распределена равномерно
• Таблицы customer и customer_1 распределены равномерно но по разному ключу
• Таблицы customer и customer_1 одинаковые

Задание 9

Используя функцию gp_dist_random('имя таблицы') исследуйте таблицы tpch1.customer и tpch1.customer_1

На каком сегменте минимальное количество строк в таблице customer?

Задание 10

Постройте предполагаемый и фактический планы выполнения соединения таблиц tpch1.nation_ext и tpch1.region_ext по полю regionkey. Это внешние таблицы источники для таблиц nation и region. Почему предполагаемый и фактический планы так сильно отличаются?

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

Задание 11

Получите предполагаемый и фактический планы выполнения для объединения таблиц customer_2 и orders_2 из схемы tpch1. Сравните с планом для объединения таблиц customer и orders. В чем отличие планов?

• План для объединения таблиц customer_2 и orders_2 короче и дешевле, а также не содержит строк Redistribute motion
• Планы одинаковые
• План для объединения таблиц customer и orders значительно дешевле и содержит строки Redistribute motion

Задание 12

Почему в плане для объединения таблиц customer_2 и orders_2 нет строк Redistribute motion?

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

Задание 13

В таблице public.buowl_habitat находятся данные по зонам обитания диких животных.

При помощи функции st_area (geometry) которая вычисляет площадь геометрической фигуры упорядочите первые 5 записей (по habitat_id) по возрастанию площади. В ответе нужен получившийся порядок habitat_id.

Задание 14

В таблице public.linear_projects находятся данные о коммуникациях.

Используя функцию st_intersects(geometry,geometry) , определите сколько пересечений есть между объектами этой таблицы.

Задание 15

В таблице public.articles содержатся несколько статей.

При помощи операторов to_tsquery и to_tsvector определите во скольких текстах статей (body) упоминается авиакомпания Ryanair.

Задание 16

При помощи функции ts_rank(tsvector,tsquery) отранжируйте статьи по телу статьи для слова airline. Какая статья оказалась первой (у какой ранг больше всех)?

Задание 17

При помощи функции ts_headline (text,tsquery) подсветите в статьях упоминания авиакомпаний Ryanair и/или Wizz.

В какой статье встретилось упоминание обеих авиакомпаний?