
ПОЛНОЕ СКАНИРОВАНИЕ И ХЭШ-СОЕДИНЕНИЯ
ВМЕСТО ИНДЕКСНОГО ДОСТУПА И ВЛОЖЕННЫХ ЦИКЛОВ
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Рассмотрим запрос, возвращающий информацию по заказам VIP-клиентов.

        psql 

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

        => explain analyze select *
        => from clients c join orders o on (o.client_id = c.id)
        => where c.vip;
                                                                     QUERY PLAN                                                              
        -------------------------------------------------------------------------------------------------------------------------------------
         Nested Loop  (cost=4.50..419.78 rows=10 width=22) (actual time=0.414..3.022 rows=52 loops=1)
           ->  Seq Scan on clients c  (cost=0.00..377.00 rows=1 width=5) (actual time=0.376..2.556 rows=2 loops=1)
                 Filter: vip
                 Rows Removed by Filter: 19998
           ->  Bitmap Heap Scan on orders o  (cost=4.50..42.68 rows=10 width=17) (actual time=0.029..0.220 rows=26 loops=2)
                 Recheck Cond: (client_id = c.id)
                 Heap Blocks: exact=52
                 ->  Bitmap Index Scan on orders_client_id_idx  (cost=0.00..4.50 rows=10 width=0) (actual time=0.015..0.015 rows=26 loops=2)
                       Index Cond: (client_id = c.id)
         Planning time: 0.641 ms
         Execution time: 3.075 ms
        (11 rows)
        

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

У нас очень немного клиентов с флагом vip:

        => select count(*) from clients where vip;
         count 
        -------
             2
        (1 row)
        

Но чтобы это понять, пришлось просканировать всю таблицу (Rows Removed
by Filter). Поможет индекс:

        => create index on clients(vip);
        CREATE INDEX

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

        => explain analyze select *
        => from clients c join orders o on (o.client_id = c.id)
        => where c.vip;
                                                                     QUERY PLAN                                                              
        -------------------------------------------------------------------------------------------------------------------------------------
         Nested Loop  (cost=4.79..47.08 rows=10 width=22) (actual time=0.050..0.134 rows=52 loops=1)
           ->  Index Scan using clients_vip_idx on clients c  (cost=0.29..4.30 rows=1 width=5) (actual time=0.032..0.035 rows=2 loops=1)
                 Index Cond: (vip = true)
                 Filter: vip
           ->  Bitmap Heap Scan on orders o  (cost=4.50..42.68 rows=10 width=17) (actual time=0.013..0.038 rows=26 loops=2)
                 Recheck Cond: (client_id = c.id)
                 Heap Blocks: exact=52
                 ->  Bitmap Index Scan on orders_client_id_idx  (cost=0.00..4.50 rows=10 width=0) (actual time=0.007..0.007 rows=26 loops=2)
                       Index Cond: (client_id = c.id)
         Planning time: 0.334 ms
         Execution time: 0.172 ms
        (11 rows)
        

Вот теперь запрос выполняется эффективно.

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

ИНДЕКСНЫЙ ДОСТУП И ВЛОЖЕННЫЕ ЦИКЛЫ
ВМЕСТО ПОЛНОГО СКАНИРОВАНИЯ И ХЭШ-СОЕДИНЕНИЙ
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Посмотрим, что может произойти при неправильной статистике.
Пусть теперь все клиенты станут VIP-ами. Но мы изменим таблицу клиентов
так, чтобы для нее не срабатывал автоанализ (иначе будет сложно
"обмануть" планировщик).

        => alter table clients set (autovacuum_analyze_threshold = 100000000);
        ALTER TABLE

        => update clients set vip = true;
        UPDATE 20000

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

        => explain analyze select *
        => from clients c join orders o on (o.client_id = c.id)
        => where c.vip;
                                                                       QUERY PLAN                                                                
        -----------------------------------------------------------------------------------------------------------------------------------------
         Nested Loop  (cost=4.79..47.08 rows=7 width=22) (actual time=0.051..454.566 rows=200000 loops=1)
           ->  Index Scan using clients_vip_idx on clients c  (cost=0.29..4.30 rows=1 width=5) (actual time=0.016..12.952 rows=20000 loops=1)
                 Index Cond: (vip = true)
                 Filter: vip
           ->  Bitmap Heap Scan on orders o  (cost=4.50..42.68 rows=10 width=17) (actual time=0.006..0.017 rows=10 loops=20000)
                 Recheck Cond: (client_id = c.id)
                 Heap Blocks: exact=199601
                 ->  Bitmap Index Scan on orders_client_id_idx  (cost=0.00..4.50 rows=10 width=0) (actual time=0.003..0.003 rows=10 loops=20000)
                       Index Cond: (client_id = c.id)
         Planning time: 0.294 ms
         Execution time: 491.288 ms
        (11 rows)
        

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

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

        => analyze clients;
        ANALYZE

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

        => explain analyze select *
        => from clients c join orders o on (o.client_id = c.id)
        => where c.vip;
                                                               QUERY PLAN                                                        
        -------------------------------------------------------------------------------------------------------------------------
         Hash Join  (cost=703.00..7834.00 rows=200000 width=22) (actual time=12.529..180.658 rows=200000 loops=1)
           Hash Cond: (o.client_id = c.id)
           ->  Seq Scan on orders o  (cost=0.00..4381.00 rows=200000 width=17) (actual time=0.003..40.789 rows=200000 loops=1)
           ->  Hash  (cost=453.00..453.00 rows=20000 width=5) (actual time=12.489..12.489 rows=20000 loops=1)
                 Buckets: 32768  Batches: 1  Memory Usage: 617kB
                 ->  Seq Scan on clients c  (cost=0.00..453.00 rows=20000 width=5) (actual time=0.003..5.207 rows=20000 loops=1)
                       Filter: vip
         Planning time: 0.248 ms
         Execution time: 213.425 ms
        (9 rows)
        

Теперь планировщик выбрал полное сканирование, что эффективнее.

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

НЕВОЗМОЖНОСТЬ ИСПОЛЬЗОВАТЬ ИНДЕКС
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Индекс может быть использован для ускорения доступа, если условие
сформулировано как "индексированное-поле оператор выражение".

Например, создадим индекс по датам заказов:

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

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

Индекс будет использован:

        => explain analyze
        => select * from orders where date_ordered < now() - interval '9 year 11 month';
                                                                      QUERY PLAN                                                               
        ---------------------------------------------------------------------------------------------------------------------------------------
         Bitmap Heap Scan on orders  (cost=32.52..2284.38 rows=1560 width=17) (actual time=0.728..2.124 rows=1614 loops=1)
           Recheck Cond: (date_ordered < (now() - '9 years 11 mons'::interval))
           Heap Blocks: exact=1152
           ->  Bitmap Index Scan on orders_date_ordered_idx  (cost=0.00..32.12 rows=1560 width=0) (actual time=0.556..0.556 rows=1614 loops=1)
                 Index Cond: (date_ordered < (now() - '9 years 11 mons'::interval))
         Planning time: 0.224 ms
         Execution time: 2.431 ms
        (7 rows)
        

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

А вот так - нет:

        => explain analyze
        => select * from orders where date_ordered + interval '9 year 11 month' < now();
                                                         QUERY PLAN                                                  
        -------------------------------------------------------------------------------------------------------------
         Seq Scan on orders  (cost=0.00..5881.00 rows=66667 width=17) (actual time=0.025..102.374 rows=1614 loops=1)
           Filter: ((date_ordered + '9 years 11 mons'::interval) < now())
           Rows Removed by Filter: 198386
         Planning time: 0.050 ms
         Execution time: 105.531 ms
        (5 rows)
        

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

Если запрос нельзя переписать, поможет функциональный индекс:

        => create index on orders((date_ordered + interval '9 year 11 month'));
        CREATE INDEX

        => explain analyze
        => select * from orders where date_ordered + interval '9 year 11 month' < now();
                                                                    QUERY PLAN                                                            
        ----------------------------------------------------------------------------------------------------------------------------------
         Bitmap Heap Scan on orders  (cost=1253.09..4800.76 rows=66667 width=17) (actual time=0.730..2.158 rows=1614 loops=1)
           Recheck Cond: ((date_ordered + '9 years 11 mons'::interval) < now())
           Heap Blocks: exact=1152
           ->  Bitmap Index Scan on orders_expr_idx  (cost=0.00..1236.43 rows=66667 width=0) (actual time=0.560..0.560 rows=1614 loops=1)
                 Index Cond: ((date_ordered + '9 years 11 mons'::interval) < now())
         Planning time: 0.171 ms
         Execution time: 2.459 ms
        (7 rows)
        

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

УПРАВЛЕНИЕ ПОРЯДКОМ ВЫПОЛНЕНИЯ
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Если планировщик выбирает неправильный порядок соединения таблиц,
повлиять на это можно, например, с помощью CTE - Common Table Expression, -
используя тот факт, что подзапросы CTE всегда материализуются, а не
подставляются в основной запрос.

Например:

        => explain (costs off)
        => with t as (
        =>   select c.id client_id, o.id order_id
        =>   from clients c join orders o on (o.client_id = c.id)
        => )
        => select t.client_id, t.order_id, i.id item_id
        => from items i join t on (i.order_id = t.order_id);
                        QUERY PLAN                 
        -------------------------------------------
         Hash Join
           Hash Cond: (t.order_id = i.order_id)
           CTE t
             ->  Hash Join
                   Hash Cond: (o.client_id = c.id)
                   ->  Seq Scan on orders o
                   ->  Hash
                         ->  Seq Scan on clients c
           ->  CTE Scan on t
           ->  Hash
                 ->  Seq Scan on items i
        (11 rows)
        

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

Или так:

        => explain (costs off)
        => with t as (
        =>   select o.id order_id, o.client_id, i.id item_id
        =>   from orders o join items i on (i.order_id = o.id)
        => )
        => select c.id client_id, t.order_id, t.item_id
        => from clients c join t on (c.id = t.client_id);
                        QUERY PLAN                
        ------------------------------------------
         Hash Join
           Hash Cond: (t.client_id = c.id)
           CTE t
             ->  Hash Join
                   Hash Cond: (i.order_id = o.id)
                   ->  Seq Scan on items i
                   ->  Hash
                         ->  Seq Scan on orders o
           ->  CTE Scan on t
           ->  Hash
                 ->  Seq Scan on clients c
        (11 rows)
        

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

КОРРЕЛИРОВАННЫЕ ПОДЗАПРОСЫ
~~~~~~~~~~~~~~~~~~~~~~~~~~

Коррелированные подзапросы могут ограничивать оптимизатор.
Рассмотрим пример:

        => explain (costs off)
        => select c.id, (select count(*) from orders o where o.client_id = c.id) cnt
        => from clients c;
                                      QUERY PLAN                              
        ----------------------------------------------------------------------
         Seq Scan on clients c
           SubPlan 1
             ->  Aggregate
                   ->  Index Only Scan using orders_client_id_idx on orders o
                         Index Cond: (client_id = c.id)
        (5 rows)
        

Здесь оптимизатор всегда выполняет подзапрос в цикле.

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

Если же раскрыть подзапрос с помощью соединения, свобода
оптимизатора увеличивается:

        => explain (costs off)
        => select c.id, count(*) cnt
        => from clients c left join orders o on (o.client_id = c.id)
        => group by c.id;
                       QUERY PLAN                
        -----------------------------------------
         HashAggregate
           Group Key: c.id
           ->  Hash Right Join
                 Hash Cond: (o.client_id = c.id)
                 ->  Seq Scan on orders o
                 ->  Hash
                       ->  Seq Scan on clients c
        (7 rows)
        

Здесь было выбрано соединение хэшированием.

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

А если ограничить число строк, будет выбрано соединение вложенными циклами:

        => explain (costs off)
        => select c.id, count(*) cnt
        => from clients c left join orders o on (o.client_id = c.id)
        => where c.id = 1
        => group by c.id;
                                     QUERY PLAN                             
        --------------------------------------------------------------------
         GroupAggregate
           Group Key: c.id
           ->  Nested Loop Left Join
                 Join Filter: (o.client_id = c.id)
                 ->  Index Only Scan using clients_pkey on clients c
                       Index Cond: (id = 1)
                 ->  Index Only Scan using orders_client_id_idx on orders o
                       Index Cond: (client_id = 1)
        (8 rows)
        

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

Тем не менее, коррелированные подзапросы из фразы WHERE обычно могут быть
автоматически преобразованы в соединения. Например:

        => explain (costs off)
        => select o.*
        => from orders o
        => where exists (
        =>   select 1 from items i
        =>   where i.order_id = o.id and i.amount = 100
        => );
                              QUERY PLAN                       
        -------------------------------------------------------
         Hash Join
           Hash Cond: (o.id = i.order_id)
           ->  Seq Scan on orders o
           ->  Hash
                 ->  HashAggregate
                       Group Key: i.order_id
                       ->  Seq Scan on items i
                             Filter: (amount = '100'::numeric)
        (8 rows)
        

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

ОПЕРАЦИИ СО МНОЖЕСТВАМИ
~~~~~~~~~~~~~~~~~~~~~~~

Операции со множествами не преобразуются в эквивалентные соединения.
Например, запрос, выбирающий заказы без позиций, можно сформулировать так:

        => explain (costs off)
        => select o.id from orders o
        => except
        => select i.order_id from items i;
                        QUERY PLAN                 
        -------------------------------------------
         HashSetOp Except
           ->  Append
                 ->  Subquery Scan on "*SELECT* 1"
                       ->  Seq Scan on orders o
                 ->  Subquery Scan on "*SELECT* 2"
                       ->  Seq Scan on items i
        (6 rows)
        

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

А можно иначе:

        => explain (costs off)
        => select o.id from orders o
        => where not exists (select null from items i where i.order_id = o.id);
                                QUERY PLAN                         
        -----------------------------------------------------------
         Merge Anti Join
           Merge Cond: (o.id = i.order_id)
           ->  Index Only Scan using orders_pkey on orders o
           ->  Index Only Scan using items_order_id_idx on items i
        (4 rows)
        

Оптимизатор не будет сравнивать эти формы, так что выбор ложится на плечи
автора запроса.

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

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

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

        => alter system reset all;
        ALTER SYSTEM

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