explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x1gn : Optimization for: Optimization for: Optimization for: plan #x38Q; plan #BUZl; plan #BPsk

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.054 19,364.804 ↑ 1.0 10 1

Limit (cost=2,342,439.74..2,342,439.76 rows=10 width=2,961) (actual time=19,364.752..19,364.804 rows=10 loops=1)

2.          

CTE filtered_orders

3. 203.102 2,708.389 ↑ 2.7 201,122 1

Unique (cost=782,263.69..788,955.30 rows=535,329 width=549) (actual time=2,177.321..2,708.389 rows=201,122 loops=1)

4. 766.814 2,505.287 ↑ 1.4 394,497 1

Sort (cost=782,263.69..783,602.01 rows=535,329 width=549) (actual time=2,177.318..2,505.287 rows=394,497 loops=1)

  • Sort Key: o1.id, o1.deleted, o1.id_ek5, o1.number
  • Sort Method: quicksort Memory: 43,109kB
5. 43.660 1,738.473 ↑ 1.4 394,497 1

Append (cost=0.56..654,162.91 rows=535,329 width=549) (actual time=0.069..1,738.473 rows=394,497 loops=1)

6. 724.492 724.492 ↑ 1.4 193,375 1

Index Scan using idx_order_sender_code_date_deleted_ex on "order" o1 (cost=0.56..296,115.94 rows=261,535 width=43) (actual time=0.068..724.492 rows=193,375 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-08-07 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-07 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
7. 117.441 117.441 ↑ 1.6 7,747 1

Index Scan using idx_order_receiver_code on "order" o1_1 (cost=0.56..53,901.08 rows=12,259 width=43) (actual time=5.789..117.441 rows=7,747 loops=1)

  • Index Cond: ((receiver_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text)
  • Filter: ((NOT deleted) AND (date_time_create >= '2019-08-07 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-07 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 28,591
8. 852.880 852.880 ↑ 1.4 193,375 1

Index Scan using idx_order_sender_code_date_deleted_ex on "order" o1_2 (cost=0.56..296,115.94 rows=261,535 width=43) (actual time=0.104..852.880 rows=193,375 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-08-07 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-07 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
9. 180.682 19,364.750 ↑ 54,280.7 10 1

Sort (cost=1,553,484.44..1,554,841.45 rows=542,807 width=2,961) (actual time=19,364.746..19,364.750 rows=10 loops=1)

  • Sort Key: o.date_time_create
  • Sort Method: top-N heapsort Memory: 34kB
10. 11,376.406 19,184.068 ↑ 2.7 201,122 1

Hash Left Join (cost=1,342,142.47..1,541,754.57 rows=542,807 width=2,961) (actual time=5,828.374..19,184.068 rows=201,122 loops=1)

  • Hash Cond: (o.sender_city_code = sc.code)
11. 246.301 7,724.758 ↑ 2.7 201,122 1

Hash Left Join (cost=1,333,148.40..1,521,846.25 rows=539,055 width=3,092) (actual time=5,744.478..7,724.758 rows=201,122 loops=1)

  • Hash Cond: (o.receiver_city_code = rc.code)
12. 454.477 7,371.720 ↑ 2.7 201,122 1

Nested Loop Left Join (cost=1,324,154.32..1,511,446.85 rows=535,329 width=3,071) (actual time=5,637.415..7,371.720 rows=201,122 loops=1)

  • Join Filter: (o.order_additional_status_code = delivery_detail_additional_status.code)
  • Rows Removed by Join Filter: 5,206,209
13. 86.639 6,716.121 ↑ 2.7 201,122 1

Hash Left Join (cost=1,324,154.18..1,503,414.55 rows=535,329 width=2,555) (actual time=5,637.388..6,716.121 rows=201,122 loops=1)

  • Hash Cond: (o.tariff_type_code = tariff_type.code)
14. 548.358 6,629.302 ↑ 2.7 201,122 1

Nested Loop Left Join (cost=1,324,127.27..1,501,961.41 rows=535,329 width=2,522) (actual time=5,637.195..6,629.302 rows=201,122 loops=1)

  • Join Filter: (o.order_status_code = order_status.code)
  • Rows Removed by Join Filter: 5,028,050
15. 310.083 5,879.822 ↑ 2.7 201,122 1

Hash Right Join (cost=1,324,126.99..1,493,925.59 rows=535,329 width=1,486) (actual time=5,636.885..5,879.822 rows=201,122 loops=1)

  • Hash Cond: (ro.id_ek5 = o.reverse_ek5_id)
16. 55.485 55.485 ↑ 2.7 201,122 1

CTE Scan on filtered_orders ro (cost=0.00..10,706.58 rows=535,329 width=532) (actual time=0.003..55.485 rows=201,122 loops=1)

17. 139.451 5,514.254 ↑ 2.7 201,122 1

Hash (cost=1,251,041.38..1,251,041.38 rows=535,329 width=986) (actual time=5,514.254..5,514.254 rows=201,122 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 75,865kB
18. 159.947 5,374.803 ↑ 2.7 201,122 1

Hash Right Join (cost=1,118,861.91..1,251,041.38 rows=535,329 width=986) (actual time=5,287.579..5,374.803 rows=201,122 loops=1)

  • Hash Cond: (so.id_ek5 = o.straight_ek5_id)
19. 42.252 42.252 ↑ 2.7 201,122 1

CTE Scan on filtered_orders so (cost=0.00..10,706.58 rows=535,329 width=532) (actual time=0.005..42.252 rows=201,122 loops=1)

20. 235.735 5,172.604 ↑ 2.7 201,122 1

Hash (cost=1,078,711.30..1,078,711.30 rows=535,329 width=486) (actual time=5,172.604..5,172.604 rows=201,122 loops=1)

  • Buckets: 262,144 Batches: 4 Memory Usage: 76,889kB
21. 525.824 4,936.869 ↑ 2.7 201,122 1

Nested Loop (cost=0.56..1,078,711.30 rows=535,329 width=486) (actual time=2,177.355..4,936.869 rows=201,122 loops=1)

22. 2,802.069 2,802.069 ↑ 2.7 201,122 1

CTE Scan on filtered_orders t (cost=0.00..10,706.58 rows=535,329 width=16) (actual time=2,177.324..2,802.069 rows=201,122 loops=1)

23. 1,608.976 1,608.976 ↑ 1.0 1 201,122

Index Scan using "idx-order_id" on "order" o (cost=0.56..2.00 rows=1 width=502) (actual time=0.008..0.008 rows=1 loops=201,122)

  • Index Cond: (id = t.id)
24. 200.792 201.122 ↓ 26.0 26 201,122

Materialize (cost=0.28..5.89 rows=1 width=1,040) (actual time=0.000..0.001 rows=26 loops=201,122)

25. 0.021 0.330 ↓ 26.0 26 1

Nested Loop Left Join (cost=0.28..5.89 rows=1 width=1,040) (actual time=0.282..0.330 rows=26 loops=1)

  • Join Filter: (order_status."groupCode" = order_status_group.code)
  • Rows Removed by Join Filter: 29
26. 0.023 0.023 ↓ 26.0 26 1

Index Scan using "idx-order_status-lang" on order_status (cost=0.14..2.36 rows=1 width=524) (actual time=0.015..0.023 rows=26 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
27. 0.286 0.286 ↓ 2.0 2 26

Index Scan using "idx-order_status_group-code-lang" on order_status_group (cost=0.14..3.51 rows=1 width=520) (actual time=0.011..0.011 rows=2 loops=26)

  • Index Cond: ((lang)::text = 'rus'::text)
28. 0.063 0.180 ↑ 1.0 282 1

Hash (cost=23.38..23.38 rows=282 width=41) (actual time=0.179..0.180 rows=282 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
29. 0.117 0.117 ↑ 1.0 282 1

Index Scan using "idx-tariff_type-lang" on tariff_type (cost=0.28..23.38 rows=282 width=41) (actual time=0.012..0.117 rows=282 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
30. 201.102 201.122 ↓ 26.0 26 201,122

Materialize (cost=0.14..2.37 rows=1 width=520) (actual time=0.000..0.001 rows=26 loops=201,122)

31. 0.020 0.020 ↓ 27.0 27 1

Index Scan using "idx-delivery_detail_additional_status-lang" on delivery_detail_additional_status (cost=0.14..2.36 rows=1 width=520) (actual time=0.010..0.020 rows=27 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
32. 30.303 106.737 ↑ 1.0 62,960 1

Hash (cost=8,198.39..8,198.39 rows=63,655 width=29) (actual time=106.737..106.737 rows=62,960 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 5,000kB
33. 38.385 76.434 ↑ 1.0 62,960 1

Bitmap Heap Scan on city rc (cost=2,896.70..8,198.39 rows=63,655 width=29) (actual time=39.372..76.434 rows=62,960 loops=1)

  • Recheck Cond: ((lang)::text = 'rus'::text)
  • Heap Blocks: exact=4,506
34. 38.049 38.049 ↑ 1.0 62,965 1

Bitmap Index Scan on "idx-city-code-lang" (cost=0.00..2,880.79 rows=63,655 width=0) (actual time=38.049..38.049 rows=62,965 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
35. 26.452 82.904 ↑ 1.0 62,960 1

Hash (cost=8,198.39..8,198.39 rows=63,655 width=29) (actual time=82.904..82.904 rows=62,960 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 5,000kB
36. 36.031 56.452 ↑ 1.0 62,960 1

Bitmap Heap Scan on city sc (cost=2,896.70..8,198.39 rows=63,655 width=29) (actual time=20.984..56.452 rows=62,960 loops=1)

  • Recheck Cond: ((lang)::text = 'rus'::text)
  • Heap Blocks: exact=4,506
37. 20.421 20.421 ↑ 1.0 62,965 1

Bitmap Index Scan on "idx-city-code-lang" (cost=0.00..2,880.79 rows=63,655 width=0) (actual time=20.421..20.421 rows=62,965 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
Planning time : 4.062 ms
Execution time : 19,371.149 ms