explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DYQ3

Settings
# exclusive inclusive rows x rows loops node
1. 8.465 5,089.184 ↑ 1.0 30 1

Limit (cost=1,176,898.19..1,176,899.84 rows=30 width=6,365) (actual time=5,080.640..5,089.184 rows=30 loops=1)

2. 0.060 5,080.719 ↑ 6,592.1 30 1

Unique (cost=1,176,898.19..1,187,775.21 rows=197,764 width=6,365) (actual time=5,080.638..5,080.719 rows=30 loops=1)

3. 1,952.682 5,080.659 ↑ 3,351.9 59 1

Sort (cost=1,176,898.19..1,177,392.60 rows=197,764 width=6,365) (actual time=5,080.636..5,080.659 rows=59 loops=1)

  • Sort 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, tariff_type.urgency
  • Sort Method: quicksort Memory: 125044kB
4. 7.940 3,127.977 ↓ 1.2 230,593 1

Append (cost=15,760.17..843,165.97 rows=197,764 width=6,365) (actual time=112.454..3,127.977 rows=230,593 loops=1)

5. 0.000 1,401.897 ↓ 1.2 132,318 1

Gather (cost=15,760.17..262,083.64 rows=114,094 width=1,937) (actual time=112.453..1,401.897 rows=132,318 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
6. 797.795 1,435.738 ↑ 1.0 22,053 6

Parallel Hash Left Join (cost=14,760.17..249,674.24 rows=22,819 width=1,937) (actual time=99.114..1,435.738 rows=22,053 loops=6)

  • Hash Cond: ((((o.sender)::json #>> '{address,cityCode}'::text[]))::integer = sc.code)
7. 411.126 622.935 ↑ 1.0 22,053 6

Parallel Hash Left Join (cost=8,775.80..243,216.64 rows=22,738 width=2,425) (actual time=83.216..622.935 rows=22,053 loops=6)

  • Hash Cond: ((((o.receiver)::json #>> '{address,cityCode}'::text[]))::integer = rc.code)
8. 5.702 197.628 ↑ 1.0 22,053 6

Hash Left Join (cost=2,791.43..237,158.63 rows=22,658 width=2,577) (actual time=68.565..197.628 rows=22,053 loops=6)

  • Hash Cond: (o.order_additional_status_code = delivery_detail_additional_status.code)
9. 6.776 191.837 ↑ 1.0 22,053 6

Hash Left Join (cost=2,788.08..237,095.72 rows=22,658 width=2,061) (actual time=68.431..191.837 rows=22,053 loops=6)

  • Hash Cond: (o.tariff_type_code = tariff_type.code)
10. 8.767 184.380 ↑ 1.0 22,053 6

Nested Loop Left Join (cost=2,756.01..237,003.26 rows=22,658 width=2,028) (actual time=67.706..184.380 rows=22,053 loops=6)

11. 11.468 175.613 ↑ 1.0 22,053 6

Nested Loop Left Join (cost=2,755.44..181,098.84 rows=22,658 width=2,034) (actual time=67.690..175.613 rows=22,053 loops=6)

12. 32.299 164.145 ↑ 1.0 22,053 6

Hash Left Join (cost=2,754.88..125,194.42 rows=22,658 width=2,040) (actual time=67.667..164.145 rows=22,053 loops=6)

  • Hash Cond: (o.order_status_code = order_status.code)
13. 90.840 131.570 ↑ 1.0 22,053 6

Parallel Bitmap Heap Scan on "order" o (cost=2,750.77..125,102.44 rows=22,658 width=1,004) (actual time=67.325..131.570 rows=22,053 loops=6)

  • Recheck Cond: ((sender_contragent_code)::text = '4469aa32-bdf8-443f-b4c0-4fe5184c4c3d'::text)
  • Heap Blocks: exact=16523
14. 40.730 40.730 ↓ 1.2 132,365 1

Bitmap Index Scan on idx_order_sender_code (cost=0.00..2,722.45 rows=113,291 width=0) (actual time=40.730..40.730 rows=132,365 loops=1)

  • Index Cond: ((sender_contragent_code)::text = '4469aa32-bdf8-443f-b4c0-4fe5184c4c3d'::text)
15. 0.029 0.276 ↓ 26.0 26 6

Hash (cost=4.10..4.10 rows=1 width=1,040) (actual time=0.276..0.276 rows=26 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.179 0.247 ↓ 26.0 26 6

Nested Loop Left Join (cost=0.00..4.10 rows=1 width=1,040) (actual time=0.072..0.247 rows=26 loops=6)

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

Seq Scan on order_status (cost=0.00..2.98 rows=1 width=524) (actual time=0.036..0.065 rows=26 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 52
18. 0.003 0.003 ↓ 2.0 2 156

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

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
19. 0.000 0.000 ↓ 0.0 0 132,318

Index Scan using "idx-order_id_ek5" on "order" ro (cost=0.56..2.47 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=132,318)

  • Index Cond: (o.reverse_ek5_id = id_ek5)
20. 0.000 0.000 ↓ 0.0 0 132,318

Index Scan using "idx-order_id_ek5" on "order" so (cost=0.56..2.47 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=132,318)

  • Index Cond: (o.straight_ek5_id = id_ek5)
21. 0.192 0.681 ↑ 1.0 278 6

Hash (cost=28.60..28.60 rows=278 width=41) (actual time=0.681..0.681 rows=278 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
22. 0.489 0.489 ↑ 1.0 278 6

Seq Scan on tariff_type (cost=0.00..28.60 rows=278 width=41) (actual time=0.018..0.489 rows=278 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 810
23. 0.029 0.089 ↓ 27.0 27 6

Hash (cost=3.34..3.34 rows=1 width=520) (actual time=0.089..0.089 rows=27 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
24. 0.060 0.060 ↓ 27.0 27 6

Seq Scan on delivery_detail_additional_status (cost=0.00..3.34 rows=1 width=520) (actual time=0.021..0.060 rows=27 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 80
25. 5.676 14.181 ↑ 2.5 10,468 6

Parallel Hash (cost=5,657.77..5,657.77 rows=26,128 width=29) (actual time=14.181..14.181 rows=10,468 loops=6)

  • Buckets: 65536 Batches: 1 Memory Usage: 5184kB
26. 8.505 8.505 ↑ 2.5 10,468 6

Parallel Seq Scan on city rc (cost=0.00..5,657.77 rows=26,128 width=29) (actual time=0.011..8.505 rows=10,468 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 27004
27. 6.812 15.008 ↑ 2.5 10,468 6

Parallel Hash (cost=5,657.77..5,657.77 rows=26,128 width=29) (actual time=15.008..15.008 rows=10,468 loops=6)

  • Buckets: 65536 Batches: 1 Memory Usage: 5184kB
28. 8.196 8.196 ↑ 2.5 10,468 6

Parallel Seq Scan on city sc (cost=0.00..5,657.77 rows=26,128 width=29) (actual time=0.023..8.196 rows=10,468 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 27004
29. 0.467 613.325 ↑ 6.9 23 1

Nested Loop Left Join (cost=2.55..362,910.34 rows=159 width=1,937) (actual time=457.735..613.325 rows=23 loops=1)

30. 0.712 612.766 ↑ 6.9 23 1

Nested Loop Left Join (cost=2.12..362,501.75 rows=158 width=2,425) (actual time=457.691..612.766 rows=23 loops=1)

31. 0.013 611.939 ↑ 6.8 23 1

Nested Loop Left Join (cost=1.69..362,098.51 rows=157 width=2,577) (actual time=457.603..611.939 rows=23 loops=1)

  • Join Filter: (o_1.order_additional_status_code = delivery_detail_additional_status_1.code)
  • Rows Removed by Join Filter: 23
32. 0.194 611.903 ↑ 6.8 23 1

Nested Loop Left Join (cost=1.69..362,092.81 rows=157 width=2,061) (actual time=457.592..611.903 rows=23 loops=1)

  • Join Filter: (o_1.tariff_type_code = tariff_type_1.code)
  • Rows Removed by Join Filter: 2691
33. 0.034 611.502 ↑ 6.8 23 1

Nested Loop Left Join (cost=1.69..361,412.97 rows=157 width=2,028) (actual time=457.490..611.502 rows=23 loops=1)

34. 0.031 611.468 ↑ 6.8 23 1

Nested Loop Left Join (cost=1.12..360,976.51 rows=157 width=2,034) (actual time=457.485..611.468 rows=23 loops=1)

35. 0.123 611.414 ↑ 6.8 23 1

Nested Loop Left Join (cost=0.56..360,540.05 rows=157 width=2,040) (actual time=457.475..611.414 rows=23 loops=1)

  • Join Filter: (o_1.order_status_code = order_status_1.code)
  • Rows Removed by Join Filter: 575
36. 611.153 611.153 ↑ 6.8 23 1

Index Scan using idx_order_receiver_code_date on "order" o_1 (cost=0.56..360,533.59 rows=157 width=1,004) (actual time=457.357..611.153 rows=23 loops=1)

  • Index Cond: ((receiver_contragent_code)::text = '4469aa32-bdf8-443f-b4c0-4fe5184c4c3d'::text)
37. 0.046 0.138 ↓ 26.0 26 23

Materialize (cost=0.00..4.11 rows=1 width=1,040) (actual time=0.002..0.006 rows=26 loops=23)

38. 0.033 0.092 ↓ 26.0 26 1

Nested Loop Left Join (cost=0.00..4.10 rows=1 width=1,040) (actual time=0.030..0.092 rows=26 loops=1)

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

Seq Scan on order_status order_status_1 (cost=0.00..2.98 rows=1 width=524) (actual time=0.018..0.033 rows=26 loops=1)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 52
40. 0.026 0.026 ↓ 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.001..0.001 rows=2 loops=26)

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

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

  • Index Cond: (o_1.reverse_ek5_id = id_ek5)
42. 0.000 0.000 ↓ 0.0 0 23

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

  • Index Cond: (o_1.straight_ek5_id = id_ek5)
43. 0.133 0.207 ↑ 2.4 118 23

Materialize (cost=0.00..29.99 rows=278 width=41) (actual time=0.000..0.009 rows=118 loops=23)

44. 0.074 0.074 ↑ 2.4 118 1

Seq Scan on tariff_type tariff_type_1 (cost=0.00..28.60 rows=278 width=41) (actual time=0.006..0.074 rows=118 loops=1)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 352
45. 0.017 0.023 ↓ 2.0 2 23

Materialize (cost=0.00..3.34 rows=1 width=520) (actual time=0.000..0.001 rows=2 loops=23)

46. 0.006 0.006 ↓ 2.0 2 1

Seq Scan on delivery_detail_additional_status delivery_detail_additional_status_1 (cost=0.00..3.34 rows=1 width=520) (actual time=0.005..0.006 rows=2 loops=1)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
47. 0.115 0.115 ↑ 1.0 1 23

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

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

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

  • Index Cond: ((code = (((o_1.sender)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
49. 0.000 1,104.815 ↓ 1.2 98,252 1

Gather (cost=15,481.63..215,205.52 rows=83,511 width=1,937) (actual time=87.401..1,104.815 rows=98,252 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
50. 613.989 1,129.643 ↑ 1.0 16,375 6

Parallel Hash Left Join (cost=14,481.63..205,854.42 rows=16,702 width=1,937) (actual time=85.570..1,129.643 rows=16,375 loops=6)

  • Hash Cond: ((((o_2.sender)::json #>> '{address,cityCode}'::text[]))::integer = sc_2.code)
51. 318.295 502.166 ↑ 1.0 16,375 6

Parallel Hash Left Join (cost=8,497.26..199,523.68 rows=16,643 width=2,425) (actual time=71.166..502.166 rows=16,375 loops=6)

  • Hash Cond: ((((o_2.receiver)::json #>> '{address,cityCode}'::text[]))::integer = rc_2.code)
52. 4.448 171.836 ↑ 1.0 16,375 6

Hash Left Join (cost=2,512.89..193,485.40 rows=16,585 width=2,577) (actual time=58.763..171.836 rows=16,375 loops=6)

  • Hash Cond: (o_2.order_additional_status_code = delivery_detail_additional_status_2.code)
53. 5.124 167.307 ↑ 1.0 16,375 6

Hash Left Join (cost=2,509.54..193,438.45 rows=16,585 width=2,061) (actual time=58.635..167.307 rows=16,375 loops=6)

  • Hash Cond: (o_2.tariff_type_code = tariff_type_2.code)
54. 6.663 161.677 ↑ 1.0 16,375 6

Nested Loop Left Join (cost=2,477.47..193,362.18 rows=16,585 width=2,028) (actual time=58.087..161.677 rows=16,375 loops=6)

55. 9.111 155.014 ↑ 1.0 16,375 6

Nested Loop Left Join (cost=2,476.90..151,569.26 rows=16,585 width=2,034) (actual time=58.073..155.014 rows=16,375 loops=6)

56. 7.100 145.903 ↑ 1.0 16,375 6

Hash Left Join (cost=2,476.34..109,776.33 rows=16,585 width=2,040) (actual time=58.047..145.903 rows=16,375 loops=6)

  • Hash Cond: (o_2.order_status_code = order_status_2.code)
57. 113.714 138.553 ↑ 1.0 16,375 6

Parallel Bitmap Heap Scan on "order" o_2 (cost=2,472.23..109,707.89 rows=16,585 width=1,004) (actual time=57.731..138.553 rows=16,375 loops=6)

  • Recheck Cond: ((payer_contragent_code)::text = '4469aa32-bdf8-443f-b4c0-4fe5184c4c3d'::text)
  • Filter: ((NOT deleted) AND (date_time_create >= '2019-03-05 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: 1852
  • Heap Blocks: exact=12728
58. 24.839 24.839 ↓ 1.1 109,610 1

Bitmap Index Scan on idx_order_payer_code (cost=0.00..2,451.50 rows=99,018 width=0) (actual time=24.839..24.839 rows=109,610 loops=1)

  • Index Cond: ((payer_contragent_code)::text = '4469aa32-bdf8-443f-b4c0-4fe5184c4c3d'::text)
59. 0.028 0.250 ↓ 26.0 26 6

Hash (cost=4.10..4.10 rows=1 width=1,040) (actual time=0.249..0.250 rows=26 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
60. 0.144 0.222 ↓ 26.0 26 6

Nested Loop Left Join (cost=0.00..4.10 rows=1 width=1,040) (actual time=0.080..0.222 rows=26 loops=6)

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

Seq Scan on order_status order_status_2 (cost=0.00..2.98 rows=1 width=524) (actual time=0.054..0.076 rows=26 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 52
62. 0.002 0.002 ↓ 2.0 2 156

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=156)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
63. 0.000 0.000 ↓ 0.0 0 98,252

Index Scan using "idx-order_id_ek5" on "order" ro_2 (cost=0.56..2.52 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=98,252)

  • Index Cond: (o_2.reverse_ek5_id = id_ek5)
64. 0.000 0.000 ↓ 0.0 0 98,252

Index Scan using "idx-order_id_ek5" on "order" so_2 (cost=0.56..2.52 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=98,252)

  • Index Cond: (o_2.straight_ek5_id = id_ek5)
65. 0.146 0.506 ↑ 1.0 278 6

Hash (cost=28.60..28.60 rows=278 width=41) (actual time=0.506..0.506 rows=278 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
66. 0.360 0.360 ↑ 1.0 278 6

Seq Scan on tariff_type tariff_type_2 (cost=0.00..28.60 rows=278 width=41) (actual time=0.022..0.360 rows=278 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 810
67. 0.025 0.081 ↓ 27.0 27 6

Hash (cost=3.34..3.34 rows=1 width=520) (actual time=0.081..0.081 rows=27 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
68. 0.056 0.056 ↓ 27.0 27 6

Seq Scan on delivery_detail_additional_status delivery_detail_additional_status_2 (cost=0.00..3.34 rows=1 width=520) (actual time=0.017..0.056 rows=27 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 80
69. 4.911 12.035 ↑ 2.5 10,468 6

Parallel Hash (cost=5,657.77..5,657.77 rows=26,128 width=29) (actual time=12.035..12.035 rows=10,468 loops=6)

  • Buckets: 65536 Batches: 1 Memory Usage: 5216kB
70. 7.124 7.124 ↑ 2.5 10,468 6

Parallel Seq Scan on city rc_2 (cost=0.00..5,657.77 rows=26,128 width=29) (actual time=0.007..7.124 rows=10,468 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 27004
71. 5.927 13.488 ↑ 2.5 10,468 6

Parallel Hash (cost=5,657.77..5,657.77 rows=26,128 width=29) (actual time=13.488..13.488 rows=10,468 loops=6)

  • Buckets: 65536 Batches: 1 Memory Usage: 5216kB
72. 7.561 7.561 ↑ 2.5 10,468 6

Parallel Seq Scan on city sc_2 (cost=0.00..5,657.77 rows=26,128 width=29) (actual time=0.023..7.561 rows=10,468 loops=6)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 27004
Planning time : 21.867 ms
Execution time : 5,099.307 ms