explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r8QC

Settings
# exclusive inclusive rows x rows loops node
1. 2.907 549.998 ↑ 1.0 10 1

Limit (cost=79,653.84..79,653.87 rows=10 width=6,369) (actual time=547.094..549.998 rows=10 loops=1)

2. 0.097 547.091 ↑ 999.4 10 1

Sort (cost=79,653.84..79,678.83 rows=9,994 width=6,369) (actual time=547.091..547.091 rows=10 loops=1)

  • Sort Key: (to_char(o.date_time_create, 'DD.MM.YYYY'::text)) DESC
  • Sort Method: top-N heapsort Memory: 31kB
3. 0.299 546.994 ↑ 434.5 23 1

HashAggregate (cost=79,238.00..79,337.94 rows=9,994 width=6,369) (actual time=546.911..546.994 rows=23 loops=1)

  • Group Key: (to_char(o.date_time_create, 'DD.MM.YYYY'::text)), (''::text), (''::text), rc.name, sc.name, (((o.online_shop)::json ->> 'numberDeparture'::text)), o.number, (((o.packages)::json #>> '{0,description}'::text[])), o.payer_contragent_code, o.sender_contragent_code, o.receiver_contragent_code, ro.number, o.order_status_code, order_status.name, o.order_additional_status_code, delivery_detail_additional_status.name, order_status."groupCode", order_status_group.name, so.number, tariff_type.name, o.order_type_code, tariff_type.urgency
4. 0.000 546.695 ↑ 238.0 42 1

Append (cost=1,546.25..78,688.33 rows=9,994 width=6,369) (actual time=375.336..546.695 rows=42 loops=1)

5. 15.310 385.598 ↑ 276.5 18 1

Gather (cost=1,546.25..38,935.73 rows=4,977 width=1,941) (actual time=375.334..385.598 rows=18 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
6. 1.633 370.288 ↑ 401.2 4 4

Nested Loop Left Join (cost=546.25..37,438.03 rows=1,605 width=1,941) (actual time=259.362..370.288 rows=4 loops=4)

7. 2.471 368.645 ↑ 400.0 4 4

Nested Loop Left Join (cost=545.82..34,377.85 rows=1,600 width=2,426) (actual time=258.970..368.645 rows=4 loops=4)

8. 0.034 366.160 ↑ 398.5 4 4

Hash Left Join (cost=545.39..31,357.14 rows=1,594 width=2,577) (actual time=257.866..366.160 rows=4 loops=4)

  • Hash Cond: (o.order_additional_status_code = delivery_detail_additional_status.code)
9. 0.034 365.646 ↑ 398.5 4 4

Hash Left Join (cost=543.01..31,350.57 rows=1,594 width=2,061) (actual time=257.360..365.646 rows=4 loops=4)

  • Hash Cond: (o.tariff_type_code = tariff_type.code)
10. 0.019 364.935 ↑ 398.5 4 4

Nested Loop Left Join (cost=516.30..31,319.61 rows=1,594 width=2,028) (actual time=256.658..364.935 rows=4 loops=4)

11. 0.029 364.914 ↑ 398.5 4 4

Nested Loop Left Join (cost=515.74..26,918.44 rows=1,594 width=2,034) (actual time=256.647..364.914 rows=4 loops=4)

12. 0.174 364.883 ↑ 398.5 4 4

Hash Left Join (cost=515.18..22,517.28 rows=1,594 width=2,040) (actual time=256.635..364.883 rows=4 loops=4)

  • Hash Cond: (o.order_status_code = order_status.code)
13. 281.432 363.392 ↑ 398.5 4 4

Parallel Bitmap Heap Scan on "order" o (cost=511.68..22,507.61 rows=1,594 width=1,004) (actual time=255.159..363.392 rows=4 loops=4)

  • Recheck Cond: ((sender_contragent_code)::text = '56090b33d83467dd84bd15ce'::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: 5530
  • Heap Blocks: exact=4687
14. 81.960 81.960 ↓ 1.1 22,143 1

Bitmap Index Scan on idx_order_sender_code (cost=0.00..510.44 rows=20,024 width=0) (actual time=81.960..81.960 rows=22,143 loops=1)

  • Index Cond: ((sender_contragent_code)::text = '56090b33d83467dd84bd15ce'::text)
15. 0.031 1.317 ↓ 26.0 26 4

Hash (cost=3.49..3.49 rows=1 width=1,040) (actual time=1.317..1.317 rows=26 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.453 1.286 ↓ 26.0 26 4

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

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

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

  • Index Cond: ((lang)::text = 'rus'::text)
18. 0.016 0.016 ↓ 2.0 2 104

Seq Scan on order_status_group (cost=0.00..1.11 rows=1 width=520) (actual time=0.015..0.016 rows=2 loops=104)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
19. 0.002 0.002 ↓ 0.0 0 18

Index Scan using "idx-order_id_ek5" on "order" ro (cost=0.56..2.76 rows=1 width=26) (actual time=0.002..0.002 rows=0 loops=18)

  • Index Cond: (o.reverse_ek5_id = id_ek5)
20. 0.002 0.002 ↓ 0.0 0 18

Index Scan using "idx-order_id_ek5" on "order" so (cost=0.56..2.76 rows=1 width=26) (actual time=0.002..0.002 rows=0 loops=18)

  • Index Cond: (o.straight_ek5_id = id_ek5)
21. 0.103 0.677 ↑ 1.0 282 4

Hash (cost=23.19..23.19 rows=282 width=41) (actual time=0.677..0.677 rows=282 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
22. 0.515 0.574 ↑ 1.0 282 4

Bitmap Heap Scan on tariff_type (cost=4.66..23.19 rows=282 width=41) (actual time=0.069..0.574 rows=282 loops=4)

  • Recheck Cond: ((lang)::text = 'rus'::text)
  • Heap Blocks: exact=15
23. 0.059 0.059 ↑ 1.0 282 4

Bitmap Index Scan on "idx-tariff_type-lang" (cost=0.00..4.59 rows=282 width=0) (actual time=0.058..0.059 rows=282 loops=4)

  • Index Cond: ((lang)::text = 'rus'::text)
24. 0.018 0.480 ↓ 27.0 27 4

Hash (cost=2.36..2.36 rows=1 width=520) (actual time=0.480..0.480 rows=27 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
25. 0.462 0.462 ↓ 27.0 27 4

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.448..0.462 rows=27 loops=4)

  • Index Cond: ((lang)::text = 'rus'::text)
26. 0.014 0.014 ↑ 1.0 1 18

Index Scan using "idx-city-code-lang" on city rc (cost=0.43..1.90 rows=1 width=29) (actual time=0.014..0.014 rows=1 loops=18)

  • Index Cond: ((code = (((o.receiver)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
27. 0.010 0.010 ↑ 1.0 1 18

Index Scan using "idx-city-code-lang" on city sc (cost=0.43..1.90 rows=1 width=29) (actual time=0.010..0.010 rows=1 loops=18)

  • Index Cond: ((code = (((o.sender)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
28. 0.081 5.324 ↑ 40.0 1 1

Nested Loop Left Join (cost=29.55..652.67 rows=40 width=1,941) (actual time=4.893..5.324 rows=1 loops=1)

29. 0.120 5.233 ↑ 40.0 1 1

Nested Loop Left Join (cost=29.12..545.86 rows=40 width=2,426) (actual time=4.803..5.233 rows=1 loops=1)

30. 0.006 5.055 ↑ 40.0 1 1

Nested Loop Left Join (cost=28.69..439.76 rows=40 width=2,577) (actual time=4.625..5.055 rows=1 loops=1)

  • Join Filter: (o_1.order_additional_status_code = delivery_detail_additional_status_1.code)
  • Rows Removed by Join Filter: 27
31. 0.029 5.009 ↑ 40.0 1 1

Hash Left Join (cost=28.54..436.80 rows=40 width=2,061) (actual time=4.579..5.009 rows=1 loops=1)

  • Hash Cond: (o_1.tariff_type_code = tariff_type_1.code)
32. 0.005 4.693 ↑ 40.0 1 1

Nested Loop Left Join (cost=1.83..409.98 rows=40 width=2,028) (actual time=4.264..4.693 rows=1 loops=1)

33. 0.004 4.683 ↑ 40.0 1 1

Nested Loop Left Join (cost=1.27..298.78 rows=40 width=2,034) (actual time=4.255..4.683 rows=1 loops=1)

34. 0.010 4.673 ↑ 40.0 1 1

Nested Loop Left Join (cost=0.71..187.58 rows=40 width=2,040) (actual time=4.245..4.673 rows=1 loops=1)

  • Join Filter: (o_1.order_status_code = order_status_1.code)
  • Rows Removed by Join Filter: 25
35. 4.475 4.475 ↑ 40.0 1 1

Index Scan using idx_order_receiver_code on "order" o_1 (cost=0.56..183.50 rows=40 width=1,004) (actual time=4.183..4.475 rows=1 loops=1)

  • Index Cond: ((receiver_contragent_code)::text = '56090b33d83467dd84bd15ce'::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: 11
36. 0.022 0.188 ↓ 26.0 26 1

Materialize (cost=0.14..3.49 rows=1 width=1,040) (actual time=0.055..0.188 rows=26 loops=1)

37. 0.047 0.166 ↓ 26.0 26 1

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

  • Join Filter: (order_status_1."groupCode" = order_status_group_1.code)
  • Rows Removed by Join Filter: 29
38. 0.041 0.041 ↓ 26.0 26 1

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

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

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

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
40. 0.006 0.006 ↓ 0.0 0 1

Index Scan using "idx-order_id_ek5" on "order" ro_1 (cost=0.56..2.78 rows=1 width=26) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (o_1.reverse_ek5_id = id_ek5)
41. 0.005 0.005 ↓ 0.0 0 1

Index Scan using "idx-order_id_ek5" on "order" so_1 (cost=0.56..2.78 rows=1 width=26) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (o_1.straight_ek5_id = id_ek5)
42. 0.083 0.287 ↑ 1.0 282 1

Hash (cost=23.19..23.19 rows=282 width=41) (actual time=0.286..0.287 rows=282 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
43. 0.168 0.204 ↑ 1.0 282 1

Bitmap Heap Scan on tariff_type tariff_type_1 (cost=4.66..23.19 rows=282 width=41) (actual time=0.049..0.204 rows=282 loops=1)

  • Recheck Cond: ((lang)::text = 'rus'::text)
  • Heap Blocks: exact=15
44. 0.036 0.036 ↑ 1.0 282 1

Bitmap Index Scan on "idx-tariff_type-lang" (cost=0.00..4.59 rows=282 width=0) (actual time=0.036..0.036 rows=282 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
45. 0.014 0.040 ↓ 27.0 27 1

Materialize (cost=0.14..2.37 rows=1 width=520) (actual time=0.019..0.040 rows=27 loops=1)

46. 0.026 0.026 ↓ 27.0 27 1

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

  • Index Cond: ((lang)::text = 'rus'::text)
47. 0.058 0.058 ↑ 1.0 1 1

Index Scan using "idx-city-code-lang" on city rc_1 (cost=0.43..2.65 rows=1 width=29) (actual time=0.058..0.058 rows=1 loops=1)

  • Index Cond: ((code = (((o_1.receiver)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
48. 0.010 0.010 ↑ 1.0 1 1

Index Scan using "idx-city-code-lang" on city sc_1 (cost=0.43..2.65 rows=1 width=29) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((code = (((o_1.sender)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
49. 12.484 158.616 ↑ 216.4 23 1

Gather (cost=1,560.55..38,950.03 rows=4,977 width=1,941) (actual time=152.826..158.616 rows=23 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
50. 0.949 146.132 ↑ 267.5 6 4

Nested Loop Left Join (cost=560.55..37,452.33 rows=1,605 width=1,941) (actual time=129.475..146.132 rows=6 loops=4)

51. 0.690 145.176 ↑ 266.7 6 4

Nested Loop Left Join (cost=560.12..34,392.15 rows=1,600 width=2,426) (actual time=129.011..145.176 rows=6 loops=4)

52. 0.033 144.475 ↑ 265.7 6 4

Hash Left Join (cost=559.69..31,371.44 rows=1,594 width=2,577) (actual time=128.747..144.475 rows=6 loops=4)

  • Hash Cond: (o_2.order_additional_status_code = delivery_detail_additional_status_2.code)
53. 0.038 144.395 ↑ 265.7 6 4

Hash Left Join (cost=557.31..31,364.87 rows=1,594 width=2,061) (actual time=128.674..144.395 rows=6 loops=4)

  • Hash Cond: (o_2.tariff_type_code = tariff_type_2.code)
54. 0.023 144.074 ↑ 265.7 6 4

Nested Loop Left Join (cost=530.60..31,333.91 rows=1,594 width=2,028) (actual time=128.362..144.074 rows=6 loops=4)

55. 0.032 144.049 ↑ 265.7 6 4

Nested Loop Left Join (cost=530.04..26,932.74 rows=1,594 width=2,034) (actual time=128.350..144.049 rows=6 loops=4)

56. 0.163 144.014 ↑ 265.7 6 4

Hash Left Join (cost=529.48..22,531.58 rows=1,594 width=2,040) (actual time=128.337..144.014 rows=6 loops=4)

  • Hash Cond: (o_2.order_status_code = order_status_2.code)
57. 45.223 143.655 ↑ 265.7 6 4

Parallel Bitmap Heap Scan on "order" o_2 (cost=525.98..22,521.91 rows=1,594 width=1,004) (actual time=128.001..143.655 rows=6 loops=4)

  • Recheck Cond: ((payer_contragent_code)::text = '56090b33d83467dd84bd15ce'::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: 5559
  • Heap Blocks: exact=4581
58. 98.432 98.432 ↓ 1.1 22,262 1

Bitmap Index Scan on idx_order_payer_code (cost=0.00..524.74 rows=20,024 width=0) (actual time=98.432..98.432 rows=22,262 loops=1)

  • Index Cond: ((payer_contragent_code)::text = '56090b33d83467dd84bd15ce'::text)
59. 0.028 0.196 ↓ 26.0 26 4

Hash (cost=3.49..3.49 rows=1 width=1,040) (actual time=0.196..0.196 rows=26 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
60. 0.111 0.168 ↓ 26.0 26 4

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

  • Join Filter: (order_status_2."groupCode" = order_status_group_2.code)
  • Rows Removed by Join Filter: 29
61. 0.055 0.055 ↓ 26.0 26 4

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

  • Index Cond: ((lang)::text = 'rus'::text)
62. 0.002 0.002 ↓ 2.0 2 104

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

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
63. 0.003 0.003 ↓ 0.0 0 23

Index Scan using "idx-order_id_ek5" on "order" ro_2 (cost=0.56..2.76 rows=1 width=26) (actual time=0.003..0.003 rows=0 loops=23)

  • Index Cond: (o_2.reverse_ek5_id = id_ek5)
64. 0.002 0.002 ↓ 0.0 0 23

Index Scan using "idx-order_id_ek5" on "order" so_2 (cost=0.56..2.76 rows=1 width=26) (actual time=0.002..0.002 rows=0 loops=23)

  • Index Cond: (o_2.straight_ek5_id = id_ek5)
65. 0.107 0.283 ↑ 1.0 282 4

Hash (cost=23.19..23.19 rows=282 width=41) (actual time=0.282..0.283 rows=282 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
66. 0.130 0.176 ↑ 1.0 282 4

Bitmap Heap Scan on tariff_type tariff_type_2 (cost=4.66..23.19 rows=282 width=41) (actual time=0.057..0.176 rows=282 loops=4)

  • Recheck Cond: ((lang)::text = 'rus'::text)
  • Heap Blocks: exact=15
67. 0.046 0.046 ↑ 1.0 282 4

Bitmap Index Scan on "idx-tariff_type-lang" (cost=0.00..4.59 rows=282 width=0) (actual time=0.046..0.046 rows=282 loops=4)

  • Index Cond: ((lang)::text = 'rus'::text)
68. 0.016 0.047 ↓ 27.0 27 4

Hash (cost=2.36..2.36 rows=1 width=520) (actual time=0.047..0.047 rows=27 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
69. 0.031 0.031 ↓ 27.0 27 4

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

  • Index Cond: ((lang)::text = 'rus'::text)
70. 0.011 0.011 ↑ 1.0 1 23

Index Scan using "idx-city-code-lang" on city rc_2 (cost=0.43..1.90 rows=1 width=29) (actual time=0.011..0.011 rows=1 loops=23)

  • Index Cond: ((code = (((o_2.receiver)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
71. 0.007 0.007 ↑ 1.0 1 23

Index Scan using "idx-city-code-lang" on city sc_2 (cost=0.43..1.90 rows=1 width=29) (actual time=0.007..0.007 rows=1 loops=23)

  • Index Cond: ((code = (((o_2.sender)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
Planning time : 53.350 ms
Execution time : 551.262 ms