
База и таблицы
~~~~~~~~~~~~~~

=> create database db16;
CREATE DATABASE

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

=> create table orders (
=>   id serial,
=>   placed date
=> );
CREATE TABLE

=> create table items (
=>   id serial,
=>   order_id integer,
=>   amount money
=> );
CREATE TABLE

=> insert into orders(placed)
=> select current_date
=> from generate_series(1,1000000);
INSERT 0 1000000

=> insert into items(order_id,amount)
=> select mod(s.id,1000000)+1, random()*100::money
=> from generate_series(1,10000000) as s(id);
INSERT 0 10000000

=> alter table orders add constraint orders_pkey primary key(id);
ALTER TABLE

=> alter table items add constraint items_pkey primary key(id);
ALTER TABLE

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

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

=> analyze orders;
ANALYZE

=> analyze items;
ANALYZE


План, выбираемый оптимизатором, и время выполнения
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Соединение вложенными циклами для небольшой выборки:

=> explain analyze
=> select i.*
=> from orders o join items i on (o.id = i.order_id)
=> where o.id between 1 and 100;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.86..4532.11 rows=1021 width=16) (actual time=0.020..2.421 rows=1000 loops=1)
   ->  Index Only Scan using orders_pkey on orders o  (cost=0.42..10.69 rows=113 width=4) (actual time=0.011..0.039 rows=100 loops=1)
         Index Cond: ((id >= 1) AND (id <= 100))
         Heap Fetches: 100
   ->  Index Scan using items_order_id_idx on items i  (cost=0.43..39.92 rows=9 width=16) (actual time=0.002..0.020 rows=10 loops=100)
         Index Cond: (order_id = o.id)
 Planning time: 2.937 ms
 Execution time: 3.037 ms
(8 rows)


Соединение слиянием для большой выборки:

=> explain analyze
=> select o.id, sum(i.amount)
=> from orders o join items i on (o.id = i.order_id)
=> group by o.id;
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=2.58..653492.70 rows=1000000 width=12) (actual time=0.050..14463.784 rows=1000000 loops=1)
   Group Key: o.id
   ->  Merge Join  (cost=2.58..598293.68 rows=9039804 width=12) (actual time=0.026..10862.823 rows=10000000 loops=1)
         Merge Cond: (o.id = i.order_id)
         ->  Index Only Scan using orders_pkey on orders o  (cost=0.42..28220.42 rows=1000000 width=4) (actual time=0.015..424.372 rows=1000000 loops=1)
               Heap Fetches: 1000000
         ->  Index Scan using items_order_id_idx on items i  (cost=0.43..452205.19 rows=9999922 width=12) (actual time=0.007..5723.775 rows=10000000 loops=1)
 Planning time: 0.897 ms
 Execution time: 14653.730 ms
(9 rows)



Другой план и время выполнения
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


=> set enable_nestloop=off;
SET

=> explain analyze
=> select i.* from orders o join items i on (o.id = i.order_id)
=> where o.id between 1 and 100;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.10..191576.23 rows=1021 width=16) (actual time=0.086..4659.744 rows=1000 loops=1)
   Hash Cond: (i.order_id = o.id)
   ->  Seq Scan on items i  (cost=0.00..154054.22 rows=9999922 width=16) (actual time=0.011..2297.875 rows=10000000 loops=1)
   ->  Hash  (cost=10.69..10.69 rows=113 width=4) (actual time=0.067..0.067 rows=100 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 7kB
         ->  Index Only Scan using orders_pkey on orders o  (cost=0.42..10.69 rows=113 width=4) (actual time=0.009..0.041 rows=100 loops=1)
               Index Cond: ((id >= 1) AND (id <= 100))
               Heap Fetches: 100
 Planning time: 0.268 ms
 Execution time: 4659.941 ms
(10 rows)


Теперь используется соединение хэшированием и время выполнения увеличилось.

=> set enable_nestloop=on;
SET

=> set enable_mergejoin=off;
SET

=> set enable_hashjoin=off;
SET

=> explain analyze
=> select o.id, sum(i.amount)
=> from orders o join items i on (o.id = i.order_id)
=> group by o.id;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.86..1069923.45 rows=1000000 width=12) (actual time=0.110..15456.521 rows=1000000 loops=1)
   Group Key: o.id
   ->  Nested Loop  (cost=0.86..1014724.43 rows=9039804 width=12) (actual time=0.048..11856.334 rows=10000000 loops=1)
         ->  Index Only Scan using orders_pkey on orders o  (cost=0.42..28220.42 rows=1000000 width=4) (actual time=0.028..377.003 rows=1000000 loops=1)
               Heap Fetches: 1000000
         ->  Index Scan using items_order_id_idx on items i  (cost=0.43..0.90 rows=9 width=12) (actual time=0.002..0.007 rows=10 loops=1000000)
               Index Cond: (order_id = o.id)
 Planning time: 0.174 ms
 Execution time: 15650.822 ms
(9 rows)


Соединение вложенными циклами - время увеличилось.


Group by и Sort by
~~~~~~~~~~~~~~~~~~

Пример с группировкой, которая выполняется с помощью сортировки:

=> explain select order_id
=> from items
=> group by order_id;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Group  (cost=0.43..477204.99 rows=1106210 width=4)
   Group Key: order_id
   ->  Index Only Scan using items_order_id_idx on items  (cost=0.43..452205.19 rows=9999922 width=4)
(3 rows)


При добавлении сортировки план не меняется:

=> explain select order_id
=> from items
=> group by order_id
=> order by order_id;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Group  (cost=0.43..477204.99 rows=1106210 width=4)
   Group Key: order_id
   ->  Index Only Scan using items_order_id_idx on items  (cost=0.43..452205.19 rows=9999922 width=4)
(3 rows)


При обратном порядке тот же индекс используется для поиска в обратном
направлении:

=> explain select order_id
=> from items
=> group by order_id
=> order by order_id desc;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Group  (cost=0.43..477204.99 rows=1106210 width=4)
   Group Key: order_id
   ->  Index Only Scan Backward using items_order_id_idx on items  (cost=0.43..452205.19 rows=9999922 width=4)
(3 rows)


=> \q
