explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.391 296.761 ↑ 1.5 100 1

Sort (cost=858.63..859.00 rows=151 width=2,961) (actual time=296.747..296.761 rows=100 loops=1)

  • Sort Key: o.date_time_create DESC
  • Sort Method: quicksort Memory: 76kB
2.          

CTE filtered_orders

3. 0.638 242.281 ↑ 1.5 100 1

HashAggregate (cost=210.87..212.37 rows=150 width=549) (actual time=242.261..242.281 rows=100 loops=1)

  • Group Key: o1.id, o1.deleted, o1.id_ek5, o1.number
4. 0.083 241.643 ↑ 1.0 150 1

Append (cost=0.56..209.37 rows=150 width=549) (actual time=1.697..241.643 rows=150 loops=1)

5. 0.033 19.203 ↑ 1.0 50 1

Limit (cost=0.56..56.08 rows=50 width=43) (actual time=1.692..19.203 rows=50 loops=1)

6. 19.170 19.170 ↑ 12,180.7 50 1

Index Scan using idx_order_sender_code_date_deleted_ex on "order" o1 (cost=0.56..676,254.50 rows=609,035 width=43) (actual time=1.687..19.170 rows=50 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-05-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. 0.040 222.220 ↑ 1.0 50 1

Limit (cost=0.56..94.96 rows=50 width=43) (actual time=22.446..222.220 rows=50 loops=1)

8. 222.180 222.180 ↑ 571.0 50 1

Index Scan using idx_order_receiver_code on "order" o1_1 (cost=0.56..53,901.08 rows=28,549 width=43) (actual time=22.444..222.180 rows=50 loops=1)

  • Index Cond: ((receiver_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text)
  • Filter: ((NOT deleted) AND (date_time_create >= '2019-05-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: 2,241
9. 0.004 0.137 ↑ 1.0 50 1

Limit (cost=0.56..56.08 rows=50 width=43) (actual time=0.033..0.137 rows=50 loops=1)

10. 0.133 0.133 ↑ 12,180.7 50 1

Index Scan using idx_order_sender_code_date_deleted_ex on "order" o1_2 (cost=0.56..676,254.50 rows=609,035 width=43) (actual time=0.031..0.133 rows=50 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-05-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)
11. 24.965 296.370 ↑ 1.5 100 1

Nested Loop Left Join (cost=38.35..640.79 rows=151 width=2,961) (actual time=245.940..296.370 rows=100 loops=1)

12. 0.451 269.605 ↑ 1.5 100 1

Nested Loop Left Join (cost=37.93..554.29 rows=150 width=3,092) (actual time=244.466..269.605 rows=100 loops=1)

  • Join Filter: (o.receiver_name ~~ '%а%'::text)
  • Rows Removed by Join Filter: 25
13. 0.428 268.054 ↑ 1.5 100 1

Nested Loop Left Join (cost=37.51..468.56 rows=150 width=3,109) (actual time=244.449..268.054 rows=100 loops=1)

  • Join Filter: (o.order_additional_status_code = delivery_detail_additional_status.code)
  • Rows Removed by Join Filter: 2,540
14. 0.187 267.226 ↑ 1.5 100 1

Hash Left Join (cost=37.36..463.95 rows=150 width=2,593) (actual time=244.386..267.226 rows=100 loops=1)

  • Hash Cond: (o.tariff_type_code = tariff_type.code)
15. 0.108 265.329 ↑ 1.5 100 1

Hash Left Join (cost=10.46..436.63 rows=150 width=2,560) (actual time=242.667..265.329 rows=100 loops=1)

  • Hash Cond: (o.straight_ek5_id = so.id_ek5)
16. 0.156 265.198 ↑ 1.5 100 1

Hash Left Join (cost=5.58..431.19 rows=150 width=2,060) (actual time=242.638..265.198 rows=100 loops=1)

  • Hash Cond: (o.reverse_ek5_id = ro.id_ek5)
17. 0.611 264.975 ↑ 1.5 100 1

Nested Loop Left Join (cost=0.71..425.74 rows=150 width=1,560) (actual time=242.558..264.975 rows=100 loops=1)

  • Join Filter: (o.order_status_code = order_status.code)
  • Rows Removed by Join Filter: 2,500
18. 0.716 263.864 ↑ 1.5 100 1

Nested Loop (cost=0.56..420.00 rows=150 width=524) (actual time=242.517..263.864 rows=100 loops=1)

19. 242.348 242.348 ↑ 1.5 100 1

CTE Scan on filtered_orders t (cost=0.00..3.00 rows=150 width=16) (actual time=242.263..242.348 rows=100 loops=1)

20. 20.800 20.800 ↑ 1.0 1 100

Index Scan using "idx-order_id" on "order" o (cost=0.56..2.78 rows=1 width=540) (actual time=0.208..0.208 rows=1 loops=100)

  • Index Cond: (id = t.id)
21. 0.301 0.500 ↓ 26.0 26 100

Materialize (cost=0.14..3.49 rows=1 width=1,040) (actual time=0.000..0.005 rows=26 loops=100)

22. 0.104 0.199 ↓ 26.0 26 1

Nested Loop Left Join (cost=0.14..3.49 rows=1 width=1,040) (actual time=0.022..0.199 rows=26 loops=1)

  • Join Filter: (order_status."groupCode" = order_status_group.code)
  • Rows Removed by Join Filter: 29
23. 0.043 0.043 ↓ 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.009..0.043 rows=26 loops=1)

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

Seq Scan on order_status_group (cost=0.00..1.11 rows=1 width=520) (actual time=0.001..0.002 rows=2 loops=26)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
25. 0.014 0.067 ↑ 1.5 100 1

Hash (cost=3.00..3.00 rows=150 width=532) (actual time=0.066..0.067 rows=100 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
26. 0.053 0.053 ↑ 1.5 100 1

CTE Scan on filtered_orders ro (cost=0.00..3.00 rows=150 width=532) (actual time=0.001..0.053 rows=100 loops=1)

27. 0.011 0.023 ↑ 1.5 100 1

Hash (cost=3.00..3.00 rows=150 width=532) (actual time=0.023..0.023 rows=100 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
28. 0.012 0.012 ↑ 1.5 100 1

CTE Scan on filtered_orders so (cost=0.00..3.00 rows=150 width=532) (actual time=0.001..0.012 rows=100 loops=1)

29. 0.066 1.710 ↑ 1.0 282 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
30. 1.644 1.644 ↑ 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.706..1.644 rows=282 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
31. 0.352 0.400 ↓ 26.0 26 100

Materialize (cost=0.14..2.37 rows=1 width=520) (actual time=0.000..0.004 rows=26 loops=100)

32. 0.048 0.048 ↓ 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.011..0.048 rows=27 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
33. 1.100 1.100 ↑ 1.0 1 100

Index Scan using "idx-city-code-lang" on city sc (cost=0.42..0.56 rows=1 width=29) (actual time=0.010..0.011 rows=1 loops=100)

  • Index Cond: ((code = o.sender_city_code) AND ((lang)::text = 'rus'::text))
34. 1.800 1.800 ↑ 1.0 1 100

Index Scan using "idx-city-code-lang" on city rc (cost=0.42..0.56 rows=1 width=29) (actual time=0.018..0.018 rows=1 loops=100)

  • Index Cond: ((code = o.receiver_city_code) AND ((lang)::text = 'rus'::text))
Planning time : 7.867 ms
Execution time : 297.185 ms