explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RJdo

Settings
# exclusive inclusive rows x rows loops node
1. 2.724 710.095 ↑ 1.0 10 1

Limit (cost=237,323.36..237,323.39 rows=10 width=6,369) (actual time=707.375..710.095 rows=10 loops=1)

  • Buffers: shared hit=100676 dirtied=6
2. 0.112 707.371 ↑ 1,103.0 10 1

Sort (cost=237,323.36..237,350.94 rows=11,030 width=6,369) (actual time=707.371..707.371 rows=10 loops=1)

  • Sort Key: (to_char(o.date_time_create, 'DD.MM.YYYY'::text)) DESC
  • Sort Method: top-N heapsort Memory: 30kB
  • Buffers: shared hit=100676 dirtied=6
3. 0.404 707.259 ↑ 367.7 30 1

HashAggregate (cost=236,864.41..236,974.71 rows=11,030 width=6,369) (actual time=707.114..707.259 rows=30 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
  • Buffers: shared hit=100673 dirtied=6
4. 0.000 706.855 ↑ 204.3 54 1

Append (cost=1,564.61..236,257.76 rows=11,030 width=6,369) (actual time=17.880..706.855 rows=54 loops=1)

  • Buffers: shared hit=100673 dirtied=6
5. 22.247 60.151 ↑ 244.0 23 1

Gather (cost=1,564.61..42,649.64 rows=5,613 width=1,941) (actual time=17.878..60.151 rows=23 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=21087 dirtied=3
6. 0.639 37.904 ↑ 301.8 6 4

Nested Loop Left Join (cost=564.61..41,088.34 rows=1,811 width=1,941) (actual time=19.637..37.904 rows=6 loops=4)

  • Buffers: shared hit=21087 dirtied=3
7. 0.424 37.259 ↑ 300.7 6 4

Nested Loop Left Join (cost=564.18..37,669.13 rows=1,804 width=2,428) (actual time=19.311..37.259 rows=6 loops=4)

  • Buffers: shared hit=20870 dirtied=2
8. 0.036 36.820 ↑ 299.7 6 4

Hash Left Join (cost=563.74..34,292.87 rows=1,798 width=2,578) (actual time=19.106..36.820 rows=6 loops=4)

  • Hash Cond: (o.order_additional_status_code = delivery_detail_additional_status.code)
  • Buffers: shared hit=20597 dirtied=2
9. 0.037 36.735 ↑ 299.7 6 4

Hash Left Join (cost=560.39..34,284.79 rows=1,798 width=2,062) (actual time=19.028..36.735 rows=6 loops=4)

  • Hash Cond: (o.tariff_type_code = tariff_type.code)
  • Buffers: shared hit=20589 dirtied=2
10. 0.019 36.283 ↑ 299.7 6 4

Nested Loop Left Join (cost=528.32..34,247.93 rows=1,798 width=2,029) (actual time=18.584..36.283 rows=6 loops=4)

  • Buffers: shared hit=20529 dirtied=2
11. 0.035 36.263 ↑ 299.7 6 4

Nested Loop Left Join (cost=527.76..29,288.53 rows=1,798 width=2,035) (actual time=18.574..36.263 rows=6 loops=4)

  • Buffers: shared hit=20529 dirtied=2
12. 0.206 36.226 ↑ 299.7 6 4

Hash Left Join (cost=527.19..24,329.13 rows=1,798 width=2,041) (actual time=18.559..36.226 rows=6 loops=4)

  • Hash Cond: (o.order_status_code = order_status.code)
  • Buffers: shared hit=20529 dirtied=2
13. 29.921 35.828 ↑ 299.7 6 4

Parallel Bitmap Heap Scan on "order" o (cost=523.08..24,318.04 rows=1,798 width=1,005) (actual time=18.179..35.828 rows=6 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: 5528
  • Heap Blocks: exact=6018
  • Buffers: shared hit=20291 dirtied=2
14. 5.907 5.907 ↓ 1.0 22,134 1

Bitmap Index Scan on idx_order_sender_code (cost=0.00..521.69 rows=21,670 width=0) (actual time=5.907..5.907 rows=22,134 loops=1)

  • Index Cond: ((sender_contragent_code)::text = '56090b33d83467dd84bd15ce'::text)
  • Buffers: shared hit=229
15. 0.029 0.192 ↓ 26.0 26 4

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=112
16. 0.101 0.163 ↓ 26.0 26 4

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

  • Join Filter: (order_status."groupCode" = order_status_group.code)
  • Rows Removed by Join Filter: 29
  • Buffers: shared hit=112
17. 0.060 0.060 ↓ 26.0 26 4

Seq Scan on order_status (cost=0.00..2.98 rows=1 width=524) (actual time=0.038..0.060 rows=26 loops=4)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 52
  • Buffers: shared hit=8
18. 0.002 0.002 ↓ 2.0 2 104

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

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=104
19. 0.002 0.002 ↓ 0.0 0 23

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

  • Index Cond: (o.reverse_ek5_id = id_ek5)
20. 0.001 0.001 ↓ 0.0 0 23

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

  • Index Cond: (o.straight_ek5_id = id_ek5)
21. 0.096 0.415 ↑ 1.0 278 4

Hash (cost=28.60..28.60 rows=278 width=41) (actual time=0.415..0.415 rows=278 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
  • Buffers: shared hit=60
22. 0.319 0.319 ↑ 1.0 278 4

Seq Scan on tariff_type (cost=0.00..28.60 rows=278 width=41) (actual time=0.013..0.319 rows=278 loops=4)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 810
  • Buffers: shared hit=60
23. 0.014 0.049 ↓ 27.0 27 4

Hash (cost=3.34..3.34 rows=1 width=520) (actual time=0.049..0.049 rows=27 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=8
24. 0.035 0.035 ↓ 27.0 27 4

Seq Scan on delivery_detail_additional_status (cost=0.00..3.34 rows=1 width=520) (actual time=0.011..0.035 rows=27 loops=4)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 80
  • Buffers: shared hit=8
25. 0.015 0.015 ↑ 1.0 1 23

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

  • Index Cond: ((code = (((o.receiver)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
  • Buffers: shared hit=95
26. 0.006 0.006 ↑ 1.0 1 23

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

  • Index Cond: ((code = (((o.sender)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
  • Buffers: shared hit=92
27. 0.058 600.569 ↑ 38.0 1 1

Nested Loop Left Join (cost=2.55..152,488.87 rows=38 width=1,941) (actual time=61.791..600.569 rows=1 loops=1)

  • Buffers: shared hit=58265
28. 0.062 600.503 ↑ 38.0 1 1

Nested Loop Left Join (cost=2.12..152,387.41 rows=38 width=2,428) (actual time=61.726..600.503 rows=1 loops=1)

  • Buffers: shared hit=58256
29. 0.006 600.432 ↑ 38.0 1 1

Nested Loop Left Join (cost=1.69..152,286.61 rows=38 width=2,578) (actual time=61.655..600.432 rows=1 loops=1)

  • Join Filter: (o_1.order_additional_status_code = delivery_detail_additional_status_1.code)
  • Rows Removed by Join Filter: 27
  • Buffers: shared hit=58247
30. 0.006 600.391 ↑ 38.0 1 1

Nested Loop Left Join (cost=1.69..152,282.70 rows=38 width=2,062) (actual time=61.615..600.391 rows=1 loops=1)

  • Join Filter: (o_1.tariff_type_code = tariff_type_1.code)
  • Rows Removed by Join Filter: 11
  • Buffers: shared hit=58245
31. 0.003 600.365 ↑ 38.0 1 1

Nested Loop Left Join (cost=1.69..152,094.95 rows=38 width=2,029) (actual time=61.590..600.365 rows=1 loops=1)

  • Buffers: shared hit=58244
32. 0.004 600.347 ↑ 38.0 1 1

Nested Loop Left Join (cost=1.12..151,989.31 rows=38 width=2,035) (actual time=61.572..600.347 rows=1 loops=1)

  • Buffers: shared hit=58244
33. 0.009 600.335 ↑ 38.0 1 1

Nested Loop Left Join (cost=0.56..151,883.67 rows=38 width=2,041) (actual time=61.561..600.335 rows=1 loops=1)

  • Join Filter: (o_1.order_status_code = order_status_1.code)
  • Rows Removed by Join Filter: 25
  • Buffers: shared hit=58244
34. 600.223 600.223 ↑ 38.0 1 1

Index Scan using idx_order_receiver_code_date on "order" o_1 (cost=0.56..151,879.00 rows=38 width=1,005) (actual time=61.521..600.223 rows=1 loops=1)

  • Index Cond: ((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) AND ((receiver_contragent_code)::text = '56090b33d83467dd84bd15ce'::text))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 8
  • Buffers: shared hit=58216
35. 0.016 0.103 ↓ 26.0 26 1

Materialize (cost=0.00..4.11 rows=1 width=1,040) (actual time=0.030..0.103 rows=26 loops=1)

  • Buffers: shared hit=28
36. 0.034 0.087 ↓ 26.0 26 1

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

  • Join Filter: (order_status_1."groupCode" = order_status_group_1.code)
  • Rows Removed by Join Filter: 29
  • Buffers: shared hit=28
37. 0.027 0.027 ↓ 26.0 26 1

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

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 52
  • Buffers: shared hit=2
38. 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
  • Buffers: shared hit=26
39. 0.008 0.008 ↓ 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.008..0.008 rows=0 loops=1)

  • Index Cond: (o_1.reverse_ek5_id = id_ek5)
40. 0.015 0.015 ↓ 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.015..0.015 rows=0 loops=1)

  • Index Cond: (o_1.straight_ek5_id = id_ek5)
41. 0.007 0.020 ↑ 23.2 12 1

Materialize (cost=0.00..29.99 rows=278 width=41) (actual time=0.011..0.020 rows=12 loops=1)

  • Buffers: shared hit=1
42. 0.013 0.013 ↑ 23.2 12 1

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

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 34
  • Buffers: shared hit=1
43. 0.010 0.035 ↓ 27.0 27 1

Materialize (cost=0.00..3.34 rows=1 width=520) (actual time=0.010..0.035 rows=27 loops=1)

  • Buffers: shared hit=2
44. 0.025 0.025 ↓ 27.0 27 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.006..0.025 rows=27 loops=1)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 80
  • Buffers: shared hit=2
45. 0.009 0.009 ↑ 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.009..0.009 rows=1 loops=1)

  • Index Cond: ((code = (((o_1.receiver)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
  • Buffers: shared hit=4
46. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

  • Index Cond: ((code = (((o_1.sender)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
  • Buffers: shared hit=4
47. 13.286 48.771 ↑ 179.3 30 1

Gather (cost=1,557.78..40,953.80 rows=5,379 width=1,941) (actual time=15.756..48.771 rows=30 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=21321 dirtied=3
48. 0.852 35.485 ↑ 216.9 8 4

Nested Loop Left Join (cost=557.78..39,415.90 rows=1,735 width=1,941) (actual time=13.973..35.485 rows=8 loops=4)

  • Buffers: shared hit=21321 dirtied=3
49. 0.746 34.633 ↑ 216.1 8 4

Nested Loop Left Join (cost=557.35..36,130.80 rows=1,729 width=2,428) (actual time=13.754..34.633 rows=8 loops=4)

  • Buffers: shared hit=21041 dirtied=2
50. 0.041 33.887 ↑ 215.4 8 4

Hash Left Join (cost=556.91..32,887.36 rows=1,723 width=2,578) (actual time=13.545..33.887 rows=8 loops=4)

  • Hash Cond: (o_2.order_additional_status_code = delivery_detail_additional_status_2.code)
  • Buffers: shared hit=20715
51. 0.041 33.798 ↑ 215.4 8 4

Hash Left Join (cost=553.56..32,879.48 rows=1,723 width=2,062) (actual time=13.471..33.798 rows=8 loops=4)

  • Hash Cond: (o_2.tariff_type_code = tariff_type_2.code)
  • Buffers: shared hit=20707
52. 0.030 33.376 ↑ 215.4 8 4

Nested Loop Left Join (cost=521.49..32,842.81 rows=1,723 width=2,029) (actual time=13.065..33.376 rows=8 loops=4)

  • Buffers: shared hit=20647
53. 0.047 33.345 ↑ 215.4 8 4

Nested Loop Left Join (cost=520.93..28,088.71 rows=1,723 width=2,035) (actual time=13.056..33.345 rows=8 loops=4)

  • Buffers: shared hit=20647
54. 0.181 33.296 ↑ 215.4 8 4

Hash Left Join (cost=520.36..23,334.61 rows=1,723 width=2,041) (actual time=13.044..33.296 rows=8 loops=4)

  • Hash Cond: (o_2.order_status_code = order_status_2.code)
  • Buffers: shared hit=20647
55. 26.475 32.966 ↑ 215.4 8 4

Parallel Bitmap Heap Scan on "order" o_2 (cost=516.25..23,323.82 rows=1,723 width=1,005) (actual time=12.745..32.966 rows=8 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: 5555
  • Heap Blocks: exact=5291
  • Buffers: shared hit=20409
56. 6.491 6.491 ↓ 1.1 22,251 1

Bitmap Index Scan on idx_order_payer_code (cost=0.00..514.91 rows=20,767 width=0) (actual time=6.490..6.491 rows=22,251 loops=1)

  • Index Cond: ((payer_contragent_code)::text = '56090b33d83467dd84bd15ce'::text)
  • Buffers: shared hit=231
57. 0.024 0.149 ↓ 26.0 26 4

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=112
58. 0.076 0.125 ↓ 26.0 26 4

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

  • Join Filter: (order_status_2."groupCode" = order_status_group_2.code)
  • Rows Removed by Join Filter: 29
  • Buffers: shared hit=112
59. 0.048 0.048 ↓ 26.0 26 4

Seq Scan on order_status order_status_2 (cost=0.00..2.98 rows=1 width=524) (actual time=0.030..0.048 rows=26 loops=4)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 52
  • Buffers: shared hit=8
60. 0.001 0.001 ↓ 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.001..0.001 rows=2 loops=104)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=104
61. 0.002 0.002 ↓ 0.0 0 30

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

  • Index Cond: (o_2.reverse_ek5_id = id_ek5)
62. 0.001 0.001 ↓ 0.0 0 30

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

  • Index Cond: (o_2.straight_ek5_id = id_ek5)
63. 0.088 0.381 ↑ 1.0 278 4

Hash (cost=28.60..28.60 rows=278 width=41) (actual time=0.380..0.381 rows=278 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
  • Buffers: shared hit=60
64. 0.293 0.293 ↑ 1.0 278 4

Seq Scan on tariff_type tariff_type_2 (cost=0.00..28.60 rows=278 width=41) (actual time=0.014..0.293 rows=278 loops=4)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 810
  • Buffers: shared hit=60
65. 0.048 0.048 ↓ 27.0 27 4

Hash (cost=3.34..3.34 rows=1 width=520) (actual time=0.048..0.048 rows=27 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=8