psql 

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

=> set work_mem = '128MB';
SET


Первая попытка
~~~~~~~~~~~~~~


=> explain analyze
=> select gen.d::date date_ordered, sum(i.amount) amount
=> from generate_series(now(), now() - interval '9 day', interval '-1 day') gen(d)
=> left join orders o on (o.date_ordered::date = gen.d::date)
=> left join items i on (i.order_id = o.id)
=> group by gen.d::date
=> order by gen.d::date;
                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=288604.12..288604.62 rows=200 width=12) (actual time=1472.563..1472.566 rows=10 loops=1)
   Sort Key: ((gen.d)::date)
   Sort Method: quicksort  Memory: 17kB
   ->  HashAggregate  (cost=288593.48..288596.48 rows=200 width=12) (actual time=1472.532..1472.535 rows=10 loops=1)
         Group Key: (gen.d)::date
         ->  Hash Right Join  (cost=54353.48..238593.48 rows=10000000 width=12) (actual time=278.462..1468.167 rows=5670 loops=1)
               Hash Cond: (i.order_id = o.id)
               ->  Seq Scan on items i  (cost=0.00..41740.00 rows=2000000 width=8) (actual time=0.027..468.076 rows=2000000 loops=1)
               ->  Hash  (cost=41853.48..41853.48 rows=1000000 width=12) (actual time=276.054..276.054 rows=556 loops=1)
                     Buckets: 1048576  Batches: 1  Memory Usage: 4114kB
                     ->  Merge Left Join  (cost=21843.48..41853.48 rows=1000000 width=12) (actual time=274.988..275.717 rows=556 loops=1)
                           Merge Cond: (((gen.d)::date) = ((o.date_ordered)::date))
                           ->  Sort  (cost=59.84..62.34 rows=1000 width=8) (actual time=0.803..0.809 rows=10 loops=1)
                                 Sort Key: ((gen.d)::date)
                                 Sort Method: quicksort  Memory: 17kB
                                 ->  Function Scan on generate_series gen  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.774..0.778 rows=10 loops=1)
                           ->  Sort  (cost=21783.64..22283.64 rows=200000 width=12) (actual time=168.876..226.633 rows=200000 loops=1)
                                 Sort Key: ((o.date_ordered)::date)
                                 Sort Method: quicksort  Memory: 11909kB
                                 ->  Seq Scan on orders o  (cost=0.00..4174.00 rows=200000 width=12) (actual time=0.009..81.261 rows=200000 loops=1)
 Planning time: 3.356 ms
 Execution time: 1473.469 ms
(22 rows)


У запроса явная проблема с оценкой кардинальности, и источником ее является
функция generate_series. Как и для любой другой функции, возвращающей
набор строк, планировщик считает, что кардинальность равна 1000.


Вторая попытка
~~~~~~~~~~~~~~

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

=> create or replace function gen10()
=> returns setof date
=> rows 10
=> as $$
=>   select d::date from generate_series(now(), now() - interval '9 day', interval '-1 day') gen(d);
=> $$ language sql;
CREATE FUNCTION

Здесь конструкция "rows 10" определяет оценку кардинальности.
Разумеется, можно определить функцию с параметром, но оценка, увы,
должна быть константой.

=> explain analyze
=> select gen.d date_ordered, sum(i.amount) amount
=> from gen10() gen(d)
=> left join orders o on (o.date_ordered::date = gen.d::date)
=> left join items i on (i.order_id = o.id)
=> group by gen.d::date
=> order by gen.d::date;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=18301.17..18301.19 rows=10 width=8) (actual time=212.909..212.910 rows=10 loops=1)
   Sort Key: gen.d
   Sort Method: quicksort  Memory: 17kB
   ->  HashAggregate  (cost=18300.88..18301.00 rows=10 width=8) (actual time=212.885..212.893 rows=10 loops=1)
         Group Key: gen.d
         ->  Nested Loop Left Join  (cost=0.90..17800.88 rows=100000 width=8) (actual time=0.752..208.166 rows=5670 loops=1)
               ->  Hash Right Join  (cost=0.47..5274.48 rows=10000 width=8) (actual time=0.726..136.333 rows=556 loops=1)
                     Hash Cond: ((o.date_ordered)::date = gen.d)
                     ->  Seq Scan on orders o  (cost=0.00..4174.00 rows=200000 width=12) (actual time=0.003..49.344 rows=200000 loops=1)
                     ->  Hash  (cost=0.35..0.35 rows=10 width=4) (actual time=0.159..0.159 rows=10 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 5kB
                           ->  Function Scan on gen10 gen  (cost=0.25..0.35 rows=10 width=4) (actual time=0.153..0.155 rows=10 loops=1)
               ->  Index Scan using items_order_id_idx on items i  (cost=0.43..1.14 rows=11 width=8) (actual time=0.041..0.122 rows=10 loops=556)
                     Index Cond: (order_id = o.id)
 Planning time: 0.271 ms
 Execution time: 212.970 ms
(16 rows)


Оценка кардинальности исправлена, однако теперь видна вторая проблема:
из-за преобразования типа невозможно использовать индекс по дате
на таблице заказов.


Третья попытка
~~~~~~~~~~~~~~

Создадим функциональный индекс:

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

И проанализируем таблицу, чтобы собралась статистика:

=> analyze orders;
ANALYZE

=> explain analyze
=> select gen.d date_ordered, sum(i.amount) amount
=> from gen10() gen(d)
=> left join orders o on (o.date_ordered::date = gen.d::date)
=> left join items i on (i.order_id = o.id)
=> group by gen.d::date
=> order by gen.d::date;
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2516.01..2516.03 rows=10 width=8) (actual time=18.186..18.188 rows=10 loops=1)
   Sort Key: gen.d
   Sort Method: quicksort  Memory: 17kB
   ->  HashAggregate  (cost=2515.72..2515.84 rows=10 width=8) (actual time=18.167..18.169 rows=10 loops=1)
         Group Key: gen.d
         ->  Nested Loop Left Join  (cost=5.52..2488.32 rows=5480 width=8) (actual time=0.179..15.428 rows=5670 loops=1)
               ->  Nested Loop Left Join  (cost=5.10..1801.87 rows=548 width=8) (actual time=0.168..1.501 rows=556 loops=1)
                     ->  Function Scan on gen10 gen  (cost=0.25..0.35 rows=10 width=4) (actual time=0.106..0.115 rows=10 loops=1)
                     ->  Bitmap Heap Scan on orders o  (cost=4.85..179.60 rows=55 width=12) (actual time=0.025..0.110 rows=56 loops=10)
                           Recheck Cond: ((date_ordered)::date = gen.d)
                           Heap Blocks: exact=550
                           ->  Bitmap Index Scan on orders_date_ordered_idx1  (cost=0.00..4.83 rows=55 width=0) (actual time=0.016..0.016 rows=56 loops=10)
                                 Index Cond: ((date_ordered)::date = gen.d)
               ->  Index Scan using items_order_id_idx on items i  (cost=0.43..1.14 rows=11 width=8) (actual time=0.004..0.019 rows=10 loops=556)
                     Index Cond: (order_id = o.id)
 Planning time: 0.360 ms
 Execution time: 18.244 ms
(17 rows)


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


Еще вариант
~~~~~~~~~~~

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

=> create temporary table gen(d date) on commit preserve rows;
CREATE TABLE

=> insert into gen(d) select gen.d::date from generate_series(now(), now() - interval '9 day', interval '-1 day') gen(d);
INSERT 0 10

=> analyze gen;
ANALYZE

Теперь планировщик будет знать, сколько строк в таблице:

=> explain analyze
=> select gen.d::date date_ordered, sum(i.amount) amount
=> from gen
=> left join orders o on (o.date_ordered::date = gen.d::date)
=> left join items i on (i.order_id = o.id)
=> group by gen.d::date
=> order by gen.d::date;
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2516.76..2516.78 rows=10 width=8) (actual time=20.276..20.278 rows=10 loops=1)
   Sort Key: gen.d
   Sort Method: quicksort  Memory: 17kB
   ->  HashAggregate  (cost=2516.47..2516.59 rows=10 width=8) (actual time=20.260..20.263 rows=10 loops=1)
         Group Key: gen.d
         ->  Nested Loop Left Join  (cost=5.27..2489.07 rows=5480 width=8) (actual time=0.038..17.217 rows=5670 loops=1)
               ->  Nested Loop Left Join  (cost=4.85..1802.62 rows=548 width=8) (actual time=0.029..1.530 rows=556 loops=1)
                     ->  Seq Scan on gen  (cost=0.00..1.10 rows=10 width=4) (actual time=0.003..0.015 rows=10 loops=1)
                     ->  Bitmap Heap Scan on orders o  (cost=4.85..179.60 rows=55 width=12) (actual time=0.026..0.119 rows=56 loops=10)
                           Recheck Cond: ((date_ordered)::date = gen.d)
                           Heap Blocks: exact=550
                           ->  Bitmap Index Scan on orders_date_ordered_idx1  (cost=0.00..4.83 rows=55 width=0) (actual time=0.014..0.014 rows=56 loops=10)
                                 Index Cond: ((date_ordered)::date = gen.d)
               ->  Index Scan using items_order_id_idx on items i  (cost=0.43..1.14 rows=11 width=8) (actual time=0.005..0.018 rows=10 loops=556)
                     Index Cond: (order_id = o.id)
 Planning time: 0.388 ms
 Execution time: 20.331 ms
(17 rows)


=> \q
