ПОДГОТОВКА
~~~~~~~~~~

Создадим БД, установим некоторые параметры и подключим расширение
pg_stat_statements, с помощью которого будем строить профиль.

        => create database db19;
        CREATE DATABASE

        => \c db19
        You are now connected to database "db19" as user "postgres".

        => alter system set work_mem = '128MB';
        ALTER SYSTEM

        => create extension pg_stat_statements;
        CREATE EXTENSION

        => alter system set shared_preload_libraries = 'pg_stat_statements';
        ALTER SYSTEM

.......................................................................

Подключение библиотеки требует перезагрузки.

        => \q
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

        psql 

        => \c db19
        You are now connected to database "db19" as user "postgres".

.......................................................................

Создадим и наполним тестовыми данными таблицы.
В нашем примере, который будет использоваться в этой и следующей
темах, мы будем иметь дело с клиентами (таблица clients):

        => create table clients(id serial primary key, vip boolean)
        => with (fillfactor = 50);
        CREATE TABLE

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

        => insert into clients(vip)
        => select false from generate_series(1,10000*2);
        INSERT 0 20000

.......................................................................

Клиенты делают заказы:

        => create table orders(id serial primary key, client_id integer, date_ordered timestamp, active boolean)
        => with (fillfactor = 50);
        CREATE TABLE

Заказы распределены случайно, в пределах 10 лет.

        => insert into orders(client_id, date_ordered, active)
        => select trunc(random()*10000*2)::integer+1,
        =>        now() - make_interval(days => trunc(random()*365*10)::integer),
        =>        false
        => from   generate_series(1,100000*2) as gen(id);
        INSERT 0 200000

        => create index on orders(client_id);
        CREATE INDEX

.......................................................................

Заказы за последний год помечены флагом.

        => update orders set active = true
        => where date_ordered > now() - interval '1 year';
        UPDATE 19878

.......................................................................

У заказа может быть несколько позиций со своей суммой:

        => create table items(id serial primary key, order_id integer, amount numeric)
        => with (fillfactor = 50);
        CREATE TABLE

        => insert into items(order_id, amount)
        => select trunc(random()*100000*2)::integer+1,
        =>        (random()*100)::integer
        => from   generate_series(1,1000000*2) as gen(id);
        INSERT 0 2000000

        => create index on items(order_id);
        CREATE INDEX

.......................................................................

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

        => alter table orders add constraint orders_clients_fk
        => foreign key (client_id) references clients(id); 
        ALTER TABLE

        => alter table items add constraint items_orders_fk
        => foreign key (order_id) references items(id); 
        ALTER TABLE

.......................................................................

И соберем статистику по всей базе.

        => vacuum analyze;
        VACUUM

.......................................................................

ПРОФИЛЬ ВЫПОЛНЕНИЯ
~~~~~~~~~~~~~~~~~~

Посмотрим, как построить профиль выполнения, чтобы из нескольких запросов
в программе найти тот, который имеет смысл оптимизировать. Мы проделаем это
с помощью расширения pg_stat_statements.
Настроим его так, чтобы собиралась информация о всех запросах, в том числе
вложенных.

        => set pg_stat_statements.track = 'all';
        SET

.......................................................................

Создадим представление, чтобы смотреть на статистику, собранную
pg_stat_statements:

        => create view statements_v as
        => select substring(regexp_replace(query,' +',' ','g') for 50) as query,
        =>        calls,
        =>        round(total_time)/1000 as time_sec,
        =>        shared_blks_hit + shared_blks_read + shared_blks_written as shared_blks
        => from pg_stat_statements
        => order by total_time desc;
        CREATE VIEW

.......................................................................

Сбросим статистику.

        => select pg_stat_statements_reset();
         pg_stat_statements_reset 
        --------------------------
         
        (1 row)
        

.......................................................................

Задача, с которой мы будем работать: сделать VIP-клиентами тех, у кого
либо максимальное количество заказов, либо максимальная сумма заказов.

Для того выполним программу на PL/pgSQL, содержащую несколько запросов:

        => do $$
        declare
          l_max_orders integer;
          l_max_amount numeric;
          l_orders integer;
          l_amount numeric;
          l_amt numeric;
          l_c record;
          l_o record;
        begin
          -- максимальное количество заказов
          select max(cnt) into l_max_orders
          from (
                 select o.client_id, count(*) cnt
                 from orders o
                 group by o.client_id
          ) t;
        
          -- максимальная сумма заказов
          select max(amt) into l_max_amount
          from (
                 select o.client_id, sum(i.amount) amt
                 from orders o join items i on (i.order_id = o.id)
                 group by o.client_id
          ) t;
        
          -- для каждого клиента...
          for l_c in (select id from clients) loop
            -- число заказов
            select count(*) into l_orders
            from orders o
            where o.client_id = l_c.id;
        
            -- сумма заказов
            l_amount := 0;
            for l_o in (select id from orders where client_id = l_c.id) loop
              select sum(i.amount) into l_amt
              from items i
              where i.order_id = l_o.id;
              l_amount := l_amount + l_amt;
            end loop;
        
            -- меняем vip-флаг
            if l_orders = l_max_orders or l_amount = l_max_amount then
              update clients set vip = true where id = l_c.id;
            else
              update clients set vip = false where id = l_c.id;
            end if;
          end loop;
        end;
        $$ language plpgsql;
        DO

.......................................................................

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

.......................................................................

Посмотрим, какую статистику мы получили:

        => \x
        Expanded display is on.

        => select * from statements_v;
        -[ RECORD 1 ]-------------------------------------------------
        query       | do $$                                           +
                    | declare                                         +
                    |  l_max_orders integer;                          +
                    |  l_max_amount
        calls       | 1
        time_sec    | 16.356
        shared_blks | 3087092
        -[ RECORD 2 ]-------------------------------------------------
        query       | select sum(i.amount) from items i               +
                    |  where i.order_i
        calls       | 200000
        time_sec    | 8.839
        shared_blks | 2617286
        -[ RECORD 3 ]-------------------------------------------------
        query       | select max(amt) from (                          +
                    |  select o.client_id, sum(i.
        calls       | 1
        time_sec    | 2.342
        shared_blks | 24153
        -[ RECORD 4 ]-------------------------------------------------
        query       | (select id from orders where client_id = l_c.id)
        calls       | 20000
        time_sec    | 0.825
        shared_blks | 260171
        -[ RECORD 5 ]-------------------------------------------------
        query       | update clients set vip = ? where id = l_c.id
        calls       | 20000
        time_sec    | 0.219
        shared_blks | 100266
        -[ RECORD 6 ]-------------------------------------------------
        query       | select count(*) from orders o                   +
                    |  where o.client_id =
        calls       | 20000
        time_sec    | 0.154
        shared_blks | 80478
        -[ RECORD 7 ]-------------------------------------------------
        query       | select max(cnt) from (                          +
                    |  select o.client_id, count(
        calls       | 1
        time_sec    | 0.152
        shared_blks | 4397
        -[ RECORD 8 ]-------------------------------------------------
        query       | (select id from clients)
        calls       | 1
        time_sec    | 0.006
        shared_blks | 179
        -[ RECORD 9 ]-------------------------------------------------
        query       | select pg_stat_statements_reset();
        calls       | 1
        time_sec    | 0
        shared_blks | 0
        

        => \x
        Expanded display is off.

Стоит отметить, что первым идет основной запрос, который детализируется
ниже. К сожалению, нет признака, вложенный это запрос или внешний.

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

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

.......................................................................

        => select pg_stat_statements_reset();
         pg_stat_statements_reset 
        --------------------------
         
        (1 row)
        

        => update clients set vip = false where vip;
        UPDATE 1

        => with a as (
          select client_id, cnt, amt, max(cnt) over () max_cnt, max(amt) over () max_amt
          from (
        	    select o.client_id, count(*) cnt, sum(i.amount) amt
        		from orders o
        		     join
        			 (select order_id, sum(amount) amount from items group by order_id) i
        			 on (i.order_id = o.id)
        		group by o.client_id
        	) b
        )
        update clients
        set vip = true
        where id in (
        	  select client_id
        	  from a
        	  where cnt = max_cnt or amt = max_amt
        );
        UPDATE 1

.......................................................................

Посмотрим статистику теперь:

        => \x
        Expanded display is on.

        => select * from statements_v;
        -[ RECORD 1 ]---------------------------------------
        query       | with a as (                           +
                    |  select client_id, cnt, amt, max(cnt) 
        calls       | 1
        time_sec    | 2.092
        shared_blks | 24126
        -[ RECORD 2 ]---------------------------------------
        query       | update clients set vip = ? where vip;
        calls       | 1
        time_sec    | 0.004
        shared_blks | 179
        -[ RECORD 3 ]---------------------------------------
        query       | select pg_stat_statements_reset();
        calls       | 1
        time_sec    | 0
        shared_blks | 0
        

        => \x
        Expanded display is off.

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

.......................................................................

Можно посчитать общее количество страниц во всех задействованных таблицах:

        => select sum(relpages) from pg_class where relname in ('clients','orders','items');
          sum  
        -------
         24298
        (1 row)
        

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

.......................................................................

Конец демонстрации.

.......................................................................

        => \q