explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lj6L

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 2,461.132 ↑ 1.0 10 1

Limit (cost=207,739.87..207,739.90 rows=10 width=1,507) (actual time=2,461.125..2,461.132 rows=10 loops=1)

2.          

CTE filtered_orders

3. 0.010 2,459.697 ↑ 1.0 10 1

Limit (cost=207,691.85..207,691.88 rows=10 width=589) (actual time=2,459.686..2,459.697 rows=10 loops=1)

4. 34.045 2,459.687 ↑ 13,816.2 10 1

Sort (cost=207,691.85..208,037.26 rows=138,162 width=589) (actual time=2,459.685..2,459.687 rows=10 loops=1)

  • Sort Key: o_1.date_time_create DESC
  • Sort Method: top-N heapsort Memory: 30kB
5. 339.967 2,425.642 ↑ 3.2 43,319 1

HashAggregate (cost=201,942.98..203,324.60 rows=138,162 width=589) (actual time=2,377.934..2,425.642 rows=43,319 loops=1)

  • Group Key: o_1.id, o_1.deleted, o_1.id_ek5, o_1.number, o_1.online_shop, o_1.date_time_create
6. 6.845 2,085.675 ↑ 1.6 86,628 1

Append (cost=180,467.02..199,870.55 rows=138,162 width=589) (actual time=1,407.798..2,085.675 rows=86,628 loops=1)

7. 71.445 1,415.899 ↑ 3.0 42,350 1

Sort (cost=180,467.02..180,782.52 rows=126,199 width=248) (actual time=1,407.796..1,415.899 rows=42,350 loops=1)

  • Sort Key: o_1.date_time_create DESC
  • Sort Method: quicksort Memory: 23373kB
8. 42.296 1,344.454 ↑ 3.0 42,350 1

Hash Join (cost=4.01..169,774.60 rows=126,199 width=248) (actual time=39.017..1,344.454 rows=42,350 loops=1)

  • Hash Cond: (o_1.order_status_code = order_status_1.code)
9. 1,302.107 1,302.107 ↓ 1.6 243,162 1

Index Scan Backward using idx_order_sender_code_number_department on "order" o_1 (cost=0.56..169,315.83 rows=149,144 width=252) (actual time=0.072..1,302.107 rows=243,162 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-09-04 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-10-04 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
10. 0.013 0.051 ↑ 1.0 22 1

Hash (cost=3.17..3.17 rows=22 width=4) (actual time=0.051..0.051 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.038 0.038 ↑ 1.0 22 1

Seq Scan on order_status order_status_1 (cost=0.00..3.17 rows=22 width=4) (actual time=0.016..0.038 rows=22 loops=1)

  • Filter: (((lang)::text = 'rus'::text) AND ("groupCode" = 2))
  • Rows Removed by Filter: 56
12. 1.819 219.195 ↑ 4.0 967 1

Sort (cost=5,473.28..5,482.95 rows=3,870 width=248) (actual time=219.017..219.195 rows=967 loops=1)

  • Sort Key: o_2.date_time_create DESC
  • Sort Method: quicksort Memory: 160kB
13. 0.886 217.376 ↑ 4.0 967 1

Nested Loop (cost=0.56..5,242.66 rows=3,870 width=248) (actual time=0.848..217.376 rows=967 loops=1)

14. 0.230 0.230 ↑ 1.0 22 1

Seq Scan on order_status order_status_2 (cost=0.00..3.17 rows=22 width=4) (actual time=0.041..0.230 rows=22 loops=1)

  • Filter: (((lang)::text = 'rus'::text) AND ("groupCode" = 2))
  • Rows Removed by Filter: 56
15. 216.260 216.260 ↑ 5.8 44 22

Index Scan using idx_order_receiver_code_order_status_code on "order" o_2 (cost=0.56..235.62 rows=254 width=252) (actual time=0.507..9.830 rows=44 loops=22)

  • Index Cond: ((order_status_code = order_status_2.code) AND ((receiver_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-09-04 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-10-04 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
16. 90.994 443.736 ↓ 5.4 43,311 1

Sort (cost=11,512.42..11,532.65 rows=8,093 width=248) (actual time=410.283..443.736 rows=43,311 loops=1)

  • Sort Key: o_3.date_time_create DESC
  • Sort Method: quicksort Memory: 23505kB
17. 20.794 352.742 ↓ 5.4 43,311 1

Hash Join (cost=4.01..10,987.08 rows=8,093 width=248) (actual time=0.158..352.742 rows=43,311 loops=1)

  • Hash Cond: (o_3.order_status_code = order_status_3.code)
18. 331.854 331.854 ↓ 7.0 66,605 1

Index Scan Backward using idx_order_payer_code_number_department on "order" o_3 (cost=0.56..10,954.44 rows=9,564 width=252) (actual time=0.052..331.854 rows=66,605 loops=1)

  • Index Cond: (((payer_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-09-04 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-10-04 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
19. 0.006 0.094 ↑ 1.0 22 1

Hash (cost=3.17..3.17 rows=22 width=4) (actual time=0.094..0.094 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.088 0.088 ↑ 1.0 22 1

Seq Scan on order_status order_status_3 (cost=0.00..3.17 rows=22 width=4) (actual time=0.012..0.088 rows=22 loops=1)

  • Filter: (((lang)::text = 'rus'::text) AND ("groupCode" = 2))
  • Rows Removed by Filter: 56
21. 0.038 2,461.124 ↑ 1.0 10 1

Sort (cost=48.00..48.02 rows=10 width=1,507) (actual time=2,461.123..2,461.124 rows=10 loops=1)

  • Sort Key: o.date_time_create DESC
  • Sort Method: quicksort Memory: 30kB
22. 0.412 2,461.086 ↑ 1.0 10 1

Nested Loop Left Join (cost=2.62..47.83 rows=10 width=1,507) (actual time=2,460.156..2,461.086 rows=10 loops=1)

23. 0.016 2,460.614 ↑ 1.0 10 1

Nested Loop Left Join (cost=2.20..42.07 rows=10 width=1,640) (actual time=2,459.856..2,460.614 rows=10 loops=1)

24. 0.012 2,460.558 ↑ 1.0 10 1

Hash Left Join (cost=1.78..36.48 rows=10 width=1,619) (actual time=2,459.842..2,460.558 rows=10 loops=1)

  • Hash Cond: (o.straight_ek5_id = so.id_ek5)
25. 0.021 2,460.538 ↑ 1.0 10 1

Hash Left Join (cost=1.45..36.10 rows=10 width=1,119) (actual time=2,459.827..2,460.538 rows=10 loops=1)

  • Hash Cond: (o.reverse_ek5_id = ro.id_ek5)
26. 0.017 2,460.495 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.13..35.73 rows=10 width=619) (actual time=2,459.791..2,460.495 rows=10 loops=1)

27. 0.011 2,460.478 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.98..34.10 rows=10 width=566) (actual time=2,459.784..2,460.478 rows=10 loops=1)

  • Join Filter: (order_status."groupCode" = order_status_group.code)
  • Rows Removed by Join Filter: 10
28. 0.015 2,460.447 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.98..32.60 rows=10 width=554) (actual time=2,459.763..2,460.447 rows=10 loops=1)

29. 0.012 2,460.392 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.71..29.63 rows=10 width=521) (actual time=2,459.749..2,460.392 rows=10 loops=1)

30. 0.027 2,460.340 ↑ 1.0 10 1

Nested Loop (cost=0.56..28.00 rows=10 width=484) (actual time=2,459.733..2,460.340 rows=10 loops=1)

31. 2,459.703 2,459.703 ↑ 1.0 10 1

CTE Scan on filtered_orders t (cost=0.00..0.20 rows=10 width=16) (actual time=2,459.700..2,459.703 rows=10 loops=1)

32. 0.610 0.610 ↑ 1.0 1 10

Index Scan using "idx-order_id" on "order" o (cost=0.56..2.78 rows=1 width=500) (actual time=0.061..0.061 rows=1 loops=10)

  • Index Cond: (id = t.id)
33. 0.040 0.040 ↑ 1.0 1 10

Index Scan using "idx-order_status-code-lang" on order_status (cost=0.14..0.16 rows=1 width=41) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: ((o.order_status_code = code) AND ((lang)::text = 'rus'::text))
34. 0.040 0.040 ↑ 1.0 1 10

Index Scan using "idx-tariff_type-code-lang" on tariff_type (cost=0.28..0.30 rows=1 width=41) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: ((o.tariff_type_code = code) AND ((lang)::text = 'rus'::text))
35. 0.008 0.020 ↑ 1.5 2 10

Materialize (cost=0.00..1.13 rows=3 width=16) (actual time=0.002..0.002 rows=2 loops=10)

36. 0.012 0.012 ↑ 1.5 2 1

Seq Scan on order_status_group (cost=0.00..1.11 rows=3 width=16) (actual time=0.011..0.012 rows=2 loops=1)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
37. 0.000 0.000 ↓ 0.0 0 10

Index Scan using "idx-delivery_detail_additional_status-code-lang" on delivery_detail_additional_status (cost=0.14..0.16 rows=1 width=57) (actual time=0.000..0.000 rows=0 loops=10)

  • Index Cond: ((o.order_additional_status_code = code) AND ((lang)::text = 'rus'::text))
38. 0.005 0.022 ↑ 1.0 10 1

Hash (cost=0.20..0.20 rows=10 width=532) (actual time=0.022..0.022 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.017 0.017 ↑ 1.0 10 1

CTE Scan on filtered_orders ro (cost=0.00..0.20 rows=10 width=532) (actual time=0.001..0.017 rows=10 loops=1)

40. 0.003 0.008 ↑ 1.0 10 1

Hash (cost=0.20..0.20 rows=10 width=532) (actual time=0.008..0.008 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.005 0.005 ↑ 1.0 10 1

CTE Scan on filtered_orders so (cost=0.00..0.20 rows=10 width=532) (actual time=0.002..0.005 rows=10 loops=1)

42. 0.040 0.040 ↑ 1.0 1 10

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

  • Index Cond: ((code = o.receiver_city_code) AND ((lang)::text = 'rus'::text))
43. 0.060 0.060 ↑ 1.0 1 10

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

  • Index Cond: ((code = o.sender_city_code) AND ((lang)::text = 'rus'::text))
Planning time : 21.159 ms