ПОЛНОЕ СКАНИРОВАНИЕ И ХЭШ-СОЕДИНЕНИЯ ВМЕСТО ИНДЕКСНОГО ДОСТУПА И ВЛОЖЕННЫХ ЦИКЛОВ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Рассмотрим запрос, возвращающий информацию по заказам 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=1.584..3.784 rows=26 loops=1) -> Seq Scan on clients c (cost=0.00..377.00 rows=1 width=5) (actual time=1.523..3.365 rows=1 loops=1) Filter: vip Rows Removed by Filter: 19999 -> Bitmap Heap Scan on orders o (cost=4.50..42.68 rows=10 width=17) (actual time=0.056..0.397 rows=26 loops=1) Recheck Cond: (client_id = c.id) Heap Blocks: exact=26 -> Bitmap Index Scan on orders_client_id_idx (cost=0.00..4.50 rows=10 width=0) (actual time=0.034..0.034 rows=26 loops=1) Index Cond: (client_id = c.id) Planning time: 1.841 ms Execution time: 3.856 ms (11 rows) ....................................................................... У нас очень немного клиентов с флагом vip: => select count(*) from clients where vip; count ------- 1 (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.054..0.093 rows=26 loops=1) -> Index Scan using clients_vip_idx on clients c (cost=0.29..4.30 rows=1 width=5) (actual time=0.034..0.035 rows=1 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.017..0.041 rows=26 loops=1) Recheck Cond: (client_id = c.id) Heap Blocks: exact=26 -> Bitmap Index Scan on orders_client_id_idx (cost=0.00..4.50 rows=10 width=0) (actual time=0.010..0.010 rows=26 loops=1) Index Cond: (client_id = c.id) Planning time: 0.367 ms Execution time: 0.126 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.052..414.886 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.013..11.098 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.005..0.015 rows=10 loops=20000) Recheck Cond: (client_id = c.id) Heap Blocks: exact=199538 -> 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.262 ms Execution time: 450.022 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=13.195..198.954 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.004..40.963 rows=200000 loops=1) -> Hash (cost=453.00..453.00 rows=20000 width=5) (actual time=13.162..13.162 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..7.424 rows=20000 loops=1) Filter: vip Planning time: 0.263 ms Execution time: 233.511 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=27.56..2213.46 rows=1437 width=17) (actual time=0.653..4.261 rows=1511 loops=1) Recheck Cond: (date_ordered < (now() - '9 years 11 mons'::interval)) Heap Blocks: exact=1129 -> Bitmap Index Scan on orders_date_ordered_idx (cost=0.00..27.20 rows=1437 width=0) (actual time=0.482..0.482 rows=1511 loops=1) Index Cond: (date_ordered < (now() - '9 years 11 mons'::interval)) Planning time: 0.252 ms Execution time: 4.704 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.161..103.120 rows=1511 loops=1) Filter: ((date_ordered + '9 years 11 mons'::interval) < now()) Rows Removed by Filter: 198489 Planning time: 0.053 ms Execution time: 104.110 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.639..2.001 rows=1511 loops=1) Recheck Cond: ((date_ordered + '9 years 11 mons'::interval) < now()) Heap Blocks: exact=1129 -> Bitmap Index Scan on orders_expr_idx (cost=0.00..1236.43 rows=66667 width=0) (actual time=0.470..0.470 rows=1511 loops=1) Index Cond: ((date_ordered + '9 years 11 mons'::interval) < now()) Planning time: 0.150 ms Execution time: 2.257 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