#	exclusive	inclusive	rows x	rows	loops	node
1.	1.517	702.569	↑ 1.9	1,299	1	→ Unique (cost=47,141.38..47,210.72 rows=2,472 width=516) (actual time=689.100..702.569 rows=1,299 loops=1)
2.						CTE missions
3.	15.789	173.962	↓ 1.2	10,261	1	→ Unique (cost=28,170.93..28,552.51 rows=8,701 width=569) (actual time=123.868..173.962 rows=10,261 loops=1)
4.	25.981	158.173	↓ 1.3	11,122	1	→ Merge Left Join (cost=28,170.93..28,530.76 rows=8,701 width=569) (actual time=123.864..158.173 rows=11,122 loops=1) Merge Cond: (m.id = fr.order_id)
5.	31.359	129.583	↓ 1.2	10,261	1	→ Sort (cost=27,950.61..27,972.36 rows=8,701 width=487) (actual time=122.114..129.583 rows=10,261 loops=1) Sort Key: m.id Sort Method: external merge Disk: 4800kB
6.	6.346	98.224	↓ 1.2	10,261	1	→ Hash Left Join (cost=21,965.92..25,477.26 rows=8,701 width=487) (actual time=63.675..98.224 rows=10,261 loops=1) Hash Cond: (m.courier_id = c.id)
7.	6.571	91.438	↓ 1.2	10,261	1	→ Hash Join (cost=21,921.54..25,378.64 rows=8,701 width=472) (actual time=63.216..91.438 rows=10,261 loops=1) Hash Cond: (s.sender_store_id = vs.id)
8.	8.969	80.572	↓ 1.2	10,261	1	→ Hash Join (cost=21,893.50..25,230.95 rows=8,701 width=488) (actual time=58.900..80.572 rows=10,261 loops=1) Hash Cond: (m.delivery_id = s.id)
9.	12.758	12.758	↓ 1.0	13,056	1	→ Seq Scan on missions m (cost=0.00..3,201.50 rows=13,052 width=472) (actual time=0.014..12.758 rows=13,056 loops=1) Filter: (courier_status <> ALL ('{POSTPONED_UNREACHABLE_CUSTOMER,CANCELLED,DROPOFF_CONFIRMED,POSTPONED_OTHER}'::type_of_courier_statuses[])) Rows Removed by Filter: 2246
10.	2.366	58.845	↑ 1.6	5,685	1	→ Hash (cost=21,780.10..21,780.10 rows=9,072 width=32) (actual time=58.845..58.845 rows=5,685 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 484kB
11.	33.652	56.479	↑ 1.6	5,685	1	→ Seq Scan on orders_stores s (cost=3,441.88..21,780.10 rows=9,072 width=32) (actual time=0.022..56.479 rows=5,685 loops=1) Filter: (((to_timestamp((dropoff_earliest_time)::double precision))::date <= ('now'::cstring)::date) OR (hashed SubPlan 1)) Rows Removed by Filter: 1467
12.						SubPlan (for Seq Scan)
13.	22.827	22.827	↓ 2.4	10,241	1	→ Seq Scan on missions mls (cost=0.00..3,431.00 rows=4,351 width=16) (actual time=0.009..22.827 rows=10,241 loops=1) Filter: ((courier_status <> ALL ('{POSTPONED_UNREACHABLE_CUSTOMER,CANCELLED,DROPOFF_CONFIRMED,POSTPONED_OTHER}'::type_of_courier_statuses[])) AND ((to_timestamp((dropoff_earliest_time)::double precision))::date <= ('now'::cstring)::date)) Rows Removed by Filter: 5061
14.	4.145	4.295	↑ 1.0	397	1	→ Hash (cost=23.02..23.02 rows=402 width=16) (actual time=4.294..4.295 rows=397 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 27kB
15.	0.150	0.150	↑ 1.0	397	1	→ Seq Scan on vendor_stores vs (cost=0.00..23.02 rows=402 width=16) (actual time=0.008..0.150 rows=397 loops=1)
16.	0.204	0.440	↑ 1.0	461	1	→ Hash (cost=38.61..38.61 rows=461 width=31) (actual time=0.440..0.440 rows=461 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 37kB
17.	0.236	0.236	↑ 1.0	461	1	→ Seq Scan on couriers c (cost=0.00..38.61 rows=461 width=31) (actual time=0.008..0.236 rows=461 loops=1)
18.	1.836	2.609	↓ 1.0	2,121	1	→ Sort (cost=220.33..225.63 rows=2,120 width=17) (actual time=1.716..2.609 rows=2,121 loops=1) Sort Key: fr.order_id Sort Method: quicksort Memory: 262kB
19.	0.773	0.773	↓ 1.0	2,121	1	→ Seq Scan on fixed_routes fr (cost=0.00..103.20 rows=2,120 width=17) (actual time=0.011..0.773 rows=2,121 loops=1)
20.						CTE json_deliv
21.	380.022	573.099	↓ 28.4	5,674	1	→ HashAggregate (cost=239.28..241.78 rows=200 width=48) (actual time=564.736..573.099 rows=5,674 loops=1) Group Key: m_1.delivery_id
22.	193.077	193.077	↓ 1.2	10,261	1	→ CTE Scan on missions m_1 (cost=0.00..174.02 rows=8,701 width=56) (actual time=123.886..193.077 rows=10,261 loops=1)
23.	3.664	701.052	↑ 1.9	1,299	1	→ Merge Left Join (cost=18,347.09..18,410.25 rows=2,472 width=516) (actual time=689.091..701.052 rows=1,299 loops=1) Merge Cond: (orders_stores.id = json_deliv.delivery_id)
24.	5.745	41.652	↑ 1.9	1,299	1	→ Sort (cost=18,335.45..18,341.63 rows=2,472 width=292) (actual time=41.028..41.652 rows=1,299 loops=1) Sort Key: orders_stores.id Sort Method: quicksort Memory: 719kB
25.	1.049	35.907	↑ 1.9	1,299	1	→ Hash Join (cost=28.05..18,196.13 rows=2,472 width=292) (actual time=0.412..35.907 rows=1,299 loops=1) Hash Cond: (orders_stores.sender_store_id = vendor_stores.id)
26.	34.463	34.463	↑ 1.9	1,299	1	→ Seq Scan on orders_stores (cost=0.00..18,134.10 rows=2,472 width=217) (actual time=0.009..34.463 rows=1,299 loops=1) Filter: (courier_status = 'WAITING'::type_of_courier_statuses) Rows Removed by Filter: 5853
27.	0.194	0.395	↑ 1.0	397	1	→ Hash (cost=23.02..23.02 rows=402 width=107) (actual time=0.395..0.395 rows=397 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 64kB
28.	0.201	0.201	↑ 1.0	397	1	→ Seq Scan on vendor_stores (cost=0.00..23.02 rows=402 width=107) (actual time=0.003..0.201 rows=397 loops=1)
29.	52.691	655.736	↓ 28.3	5,662	1	→ Sort (cost=11.64..12.14 rows=200 width=48) (actual time=648.024..655.736 rows=5,662 loops=1) Sort Key: json_deliv.delivery_id Sort Method: external sort Disk: 16704kB
30.	603.045	603.045	↓ 28.4	5,674	1	→ CTE Scan on json_deliv (cost=0.00..4.00 rows=200 width=48) (actual time=564.743..603.045 rows=5,674 loops=1)

Planning time	:	6.687 ms
Execution time	:	712.963 ms

Unique  (cost=47141.38..47210.72 rows=2472 width=516) (actual time=689.100..702.569 rows=1299 loops=1)
  CTE missions
    ->  Unique  (cost=28170.93..28552.51 rows=8701 width=569) (actual time=123.868..173.962 rows=10261 loops=1)
          ->  Merge Left Join  (cost=28170.93..28530.76 rows=8701 width=569) (actual time=123.864..158.173 rows=11122 loops=1)
                Merge Cond: (m.id = fr.order_id)
                ->  Sort  (cost=27950.61..27972.36 rows=8701 width=487) (actual time=122.114..129.583 rows=10261 loops=1)
                      Sort Key: m.id
                      Sort Method: external merge  Disk: 4800kB
                      ->  Hash Left Join  (cost=21965.92..25477.26 rows=8701 width=487) (actual time=63.675..98.224 rows=10261 loops=1)
                            Hash Cond: (m.courier_id = c.id)
                            ->  Hash Join  (cost=21921.54..25378.64 rows=8701 width=472) (actual time=63.216..91.438 rows=10261 loops=1)
                                  Hash Cond: (s.sender_store_id = vs.id)
                                  ->  Hash Join  (cost=21893.50..25230.95 rows=8701 width=488) (actual time=58.900..80.572 rows=10261 loops=1)
                                        Hash Cond: (m.delivery_id = s.id)
                                        ->  Seq Scan on missions m  (cost=0.00..3201.50 rows=13052 width=472) (actual time=0.014..12.758 rows=13056 loops=1)
                                              Filter: (courier_status <> ALL ('{POSTPONED_UNREACHABLE_CUSTOMER,CANCELLED,DROPOFF_CONFIRMED,POSTPONED_OTHER}'::type_of_courier_statuses[]))
                                              Rows Removed by Filter: 2246
                                        ->  Hash  (cost=21780.10..21780.10 rows=9072 width=32) (actual time=58.845..58.845 rows=5685 loops=1)
                                              Buckets: 16384  Batches: 1  Memory Usage: 484kB
                                              ->  Seq Scan on orders_stores s  (cost=3441.88..21780.10 rows=9072 width=32) (actual time=0.022..56.479 rows=5685 loops=1)
                                                    Filter: (((to_timestamp((dropoff_earliest_time)::double precision))::date <= ('now'::cstring)::date) OR (hashed SubPlan 1))
                                                    Rows Removed by Filter: 1467
                                                    SubPlan 1
                                                      ->  Seq Scan on missions mls  (cost=0.00..3431.00 rows=4351 width=16) (actual time=0.009..22.827 rows=10241 loops=1)
                                                            Filter: ((courier_status <> ALL ('{POSTPONED_UNREACHABLE_CUSTOMER,CANCELLED,DROPOFF_CONFIRMED,POSTPONED_OTHER}'::type_of_courier_statuses[])) AND ((to_timestamp((dropoff_earliest_time)::double precision))::date <= ('now'::cstring)::date))
                                                            Rows Removed by Filter: 5061
                                  ->  Hash  (cost=23.02..23.02 rows=402 width=16) (actual time=4.294..4.295 rows=397 loops=1)
                                        Buckets: 1024  Batches: 1  Memory Usage: 27kB
                                        ->  Seq Scan on vendor_stores vs  (cost=0.00..23.02 rows=402 width=16) (actual time=0.008..0.150 rows=397 loops=1)
                            ->  Hash  (cost=38.61..38.61 rows=461 width=31) (actual time=0.440..0.440 rows=461 loops=1)
                                  Buckets: 1024  Batches: 1  Memory Usage: 37kB
                                  ->  Seq Scan on couriers c  (cost=0.00..38.61 rows=461 width=31) (actual time=0.008..0.236 rows=461 loops=1)
                ->  Sort  (cost=220.33..225.63 rows=2120 width=17) (actual time=1.716..2.609 rows=2121 loops=1)
                      Sort Key: fr.order_id
                      Sort Method: quicksort  Memory: 262kB
                      ->  Seq Scan on fixed_routes fr  (cost=0.00..103.20 rows=2120 width=17) (actual time=0.011..0.773 rows=2121 loops=1)
  CTE json_deliv
    ->  HashAggregate  (cost=239.28..241.78 rows=200 width=48) (actual time=564.736..573.099 rows=5674 loops=1)
          Group Key: m_1.delivery_id
          ->  CTE Scan on missions m_1  (cost=0.00..174.02 rows=8701 width=56) (actual time=123.886..193.077 rows=10261 loops=1)
  ->  Merge Left Join  (cost=18347.09..18410.25 rows=2472 width=516) (actual time=689.091..701.052 rows=1299 loops=1)
        Merge Cond: (orders_stores.id = json_deliv.delivery_id)
        ->  Sort  (cost=18335.45..18341.63 rows=2472 width=292) (actual time=41.028..41.652 rows=1299 loops=1)
              Sort Key: orders_stores.id
              Sort Method: quicksort  Memory: 719kB
              ->  Hash Join  (cost=28.05..18196.13 rows=2472 width=292) (actual time=0.412..35.907 rows=1299 loops=1)
                    Hash Cond: (orders_stores.sender_store_id = vendor_stores.id)
                    ->  Seq Scan on orders_stores  (cost=0.00..18134.10 rows=2472 width=217) (actual time=0.009..34.463 rows=1299 loops=1)
                          Filter: (courier_status = 'WAITING'::type_of_courier_statuses)
                          Rows Removed by Filter: 5853
                    ->  Hash  (cost=23.02..23.02 rows=402 width=107) (actual time=0.395..0.395 rows=397 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 64kB
                          ->  Seq Scan on vendor_stores  (cost=0.00..23.02 rows=402 width=107) (actual time=0.003..0.201 rows=397 loops=1)
        ->  Sort  (cost=11.64..12.14 rows=200 width=48) (actual time=648.024..655.736 rows=5662 loops=1)
              Sort Key: json_deliv.delivery_id
              Sort Method: external sort  Disk: 16704kB
              ->  CTE Scan on json_deliv  (cost=0.00..4.00 rows=200 width=48) (actual time=564.743..603.045 rows=5674 loops=1)
Planning time: 6.687 ms
Execution time: 712.963 ms

Per node type stats

node type	count	sum of times	% of query
CTE Scan	2	796.122 ms	113.3 %
Hash	4	6.909 ms	1.0 %
Hash Join	3	16.589 ms	2.4 %
Hash Left Join	1	6.346 ms	0.9 %
HashAggregate	1	380.022 ms	54.1 %
Merge Left Join	2	29.645 ms	4.2 %
Seq Scan	8	105.060 ms	15.0 %
Sort	4	91.631 ms	13.0 %
Unique	2	17.306 ms	2.5 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
couriers	1	0.236 ms	0.0 %
Seq Scan	1	0.236 ms	100.0 %
fixed_routes	1	0.773 ms	0.1 %
Seq Scan	1	0.773 ms	100.0 %
missions	2	35.585 ms	5.1 %
Seq Scan	2	35.585 ms	100.0 %
orders_stores	2	68.115 ms	9.7 %
Seq Scan	2	68.115 ms	100.0 %
vendor_stores	2	0.351 ms	0.0 %
Seq Scan	2	0.351 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ECN : Optimization for: plan #rDXJ

Color mode:

Visible columns:

Optimization path:

Per node type stats

Per table stats