explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8fgy

Settings
# exclusive inclusive rows x rows loops node
1. 0.599 17.870 ↑ 1.0 10 1

Nested Loop Left Join (cost=2,756.99..2,888.78 rows=10 width=3,895) (actual time=15.884..17.870 rows=10 loops=1)

  • Buffers: shared hit=1000 read=56 dirtied=1
2.          

CTE orders

3. 0.021 12.903 ↑ 1.0 10 1

Limit (cost=2,754.70..2,754.73 rows=10 width=5,755) (actual time=12.885..12.903 rows=10 loops=1)

  • Buffers: shared hit=769 read=46 dirtied=1
4. 0.171 12.882 ↑ 3.0 10 1

Sort (cost=2,754.70..2,754.78 rows=30 width=5,755) (actual time=12.878..12.882 rows=10 loops=1)

  • Sort Key: o_1.date_time_create DESC
  • Sort Method: quicksort Memory: 45kB
  • Buffers: shared hit=769 read=46 dirtied=1
5. 3.964 12.711 ↑ 3.0 10 1

HashAggregate (cost=2,753.75..2,754.05 rows=30 width=5,755) (actual time=12.701..12.711 rows=10 loops=1)

  • Group Key: o_1.id, o_1.id_ek5, o_1.number, o_1.date_time_create, o_1.user_created_code, o_1.order_type_code, o_1.note, o_1.true_delivery_mode_code, o_1.ek4_id, o_1.sender, o_1.sender_contragent_code, o_1.receiver, o_1.receiver_contragent_code, o_1.payer, o_1.payer_contragent_code, o_1.method_of_payment_code, o_1.payer_type_code, o_1.packages, o_1.volume_weight, o_1.calc_weight, o_1.online_shop, o_1.international, o_1.is_international, o_1.tariff_type_code, o_1.additional_services, o_1.discounts, o_1.extra_charges, o_1.common_currency_code, o_1.declared_cost_currency_code, o_1.cash_on_delivery_currency_code, o_1.delivery_price, o_1.from_office, o_1.order_status_code, o_1.order_additional_status_code, o_1.interface_code, o_1.responsible_for_delivery_branch_code, o_1.deleted, o_1.create_time, o_1.update_time, o_1.reverse_ek5_id, o_1.straight_ek5_id, o_1.total_amount, o_1.thresholds, o_1.real_weight, o_1.courier_invitation_code
  • Buffers: shared hit=766 read=46 dirtied=1
6. 0.008 8.747 ↑ 1.0 30 1

Append (cost=916.62..2,750.38 rows=30 width=5,755) (actual time=8.402..8.747 rows=30 loops=1)

  • Buffers: shared hit=73 read=32
7. 0.006 8.407 ↑ 1.0 10 1

Limit (cost=916.62..916.64 rows=10 width=1,983) (actual time=8.401..8.407 rows=10 loops=1)

  • Buffers: shared hit=3 read=32
8. 0.152 8.401 ↑ 67.1 10 1

Sort (cost=916.62..918.30 rows=671 width=1,983) (actual time=8.399..8.401 rows=10 loops=1)

  • Sort Key: o_1.date_time_create DESC
  • Sort Method: top-N heapsort Memory: 65kB
  • Buffers: shared hit=3 read=32
9. 8.249 8.249 ↑ 25.8 26 1

Index Scan using idx_order_sender_code on "order" o_1 (cost=0.56..902.12 rows=671 width=1,983) (actual time=0.841..8.249 rows=26 loops=1)

  • Index Cond: ((sender_contragent_code)::text = '1e5ea3d6-ba81-479f-af0f-e9a4d2bd0aaa'::text)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=3 read=32
10. 0.004 0.153 ↑ 1.0 10 1

Limit (cost=916.62..916.64 rows=10 width=1,983) (actual time=0.149..0.153 rows=10 loops=1)

  • Buffers: shared hit=35
11. 0.063 0.149 ↑ 67.1 10 1

Sort (cost=916.62..918.30 rows=671 width=1,983) (actual time=0.148..0.149 rows=10 loops=1)

  • Sort Key: o_2.date_time_create DESC
  • Sort Method: top-N heapsort Memory: 65kB
  • Buffers: shared hit=35
12. 0.086 0.086 ↑ 25.8 26 1

Index Scan using idx_order_sender_code on "order" o_2 (cost=0.56..902.12 rows=671 width=1,983) (actual time=0.025..0.086 rows=26 loops=1)

  • Index Cond: ((sender_contragent_code)::text = '1e5ea3d6-ba81-479f-af0f-e9a4d2bd0aaa'::text)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=35
13. 0.004 0.179 ↑ 1.0 10 1

Limit (cost=916.62..916.64 rows=10 width=1,983) (actual time=0.175..0.179 rows=10 loops=1)

  • Buffers: shared hit=35
14. 0.106 0.175 ↑ 67.1 10 1

Sort (cost=916.62..918.30 rows=671 width=1,983) (actual time=0.174..0.175 rows=10 loops=1)

  • Sort Key: o_3.date_time_create DESC
  • Sort Method: top-N heapsort Memory: 65kB
  • Buffers: shared hit=35
15. 0.069 0.069 ↑ 25.8 26 1

Index Scan using idx_order_sender_code on "order" o_3 (cost=0.56..902.12 rows=671 width=1,983) (actual time=0.022..0.069 rows=26 loops=1)

  • Index Cond: ((sender_contragent_code)::text = '1e5ea3d6-ba81-479f-af0f-e9a4d2bd0aaa'::text)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=35
16. 0.346 17.221 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.83..107.35 rows=10 width=3,814) (actual time=15.799..17.221 rows=10 loops=1)

  • Buffers: shared hit=905 read=56 dirtied=1
17. 0.041 16.815 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.40..80.83 rows=10 width=3,821) (actual time=15.733..16.815 rows=10 loops=1)

  • Join Filter: (o.order_additional_status_code = delivery_detail_additional_status.code)
  • Rows Removed by Join Filter: 270
  • Buffers: shared hit=819 read=56 dirtied=1
18. 0.014 16.474 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.40..77.34 rows=10 width=3,305) (actual time=15.437..16.474 rows=10 loops=1)

  • Buffers: shared hit=819 read=54 dirtied=1
19. 0.020 14.490 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.12..60.05 rows=10 width=3,272) (actual time=14.320..14.490 rows=10 loops=1)

  • Buffers: shared hit=794 read=49 dirtied=1
20. 0.048 14.440 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.56..32.25 rows=10 width=3,278) (actual time=14.276..14.440 rows=10 loops=1)

  • Join Filter: (o.order_status_code = order_status.code)
  • Rows Removed by Join Filter: 250
  • Buffers: shared hit=794 read=49 dirtied=1
21. 0.030 13.002 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.56..28.00 rows=10 width=2,242) (actual time=12.920..13.002 rows=10 loops=1)

  • Buffers: shared hit=769 read=46 dirtied=1
22. 12.952 12.952 ↑ 1.0 10 1

CTE Scan on orders o (cost=0.00..0.20 rows=10 width=2,248) (actual time=12.892..12.952 rows=10 loops=1)

  • Buffers: shared hit=769 read=46 dirtied=1
23. 0.020 0.020 ↓ 0.0 0 10

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

  • Index Cond: (o.reverse_ek5_id = id_ek5)
24. 0.076 1.390 ↓ 26.0 26 10

Materialize (cost=0.00..4.11 rows=1 width=1,040) (actual time=0.121..0.139 rows=26 loops=10)

  • Buffers: shared hit=25 read=3
25. 0.077 1.314 ↓ 26.0 26 1

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

  • Join Filter: (order_status."groupCode" = order_status_group.code)
  • Rows Removed by Join Filter: 29
  • Buffers: shared hit=25 read=3
26. 0.665 0.665 ↓ 26.0 26 1

Seq Scan on order_status (cost=0.00..2.98 rows=1 width=524) (actual time=0.640..0.665 rows=26 loops=1)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 52
  • Buffers: shared read=2
27. 0.572 0.572 ↓ 2.0 2 26

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

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=25 read=1
28. 0.030 0.030 ↓ 0.0 0 10

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

  • Index Cond: (o.straight_ek5_id = id_ek5)
29. 1.970 1.970 ↑ 1.0 1 10

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

  • Index Cond: ((o.tariff_type_code = code) AND ((lang)::text = 'rus'::text))
  • Buffers: shared hit=25 read=5
30. 0.022 0.300 ↓ 27.0 27 10

Materialize (cost=0.00..3.34 rows=1 width=520) (actual time=0.027..0.030 rows=27 loops=10)

  • Buffers: shared read=2
31. 0.278 0.278 ↓ 27.0 27 1

Seq Scan on delivery_detail_additional_status (cost=0.00..3.34 rows=1 width=520) (actual time=0.262..0.278 rows=27 loops=1)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 80
  • Buffers: shared read=2
32. 0.060 0.060 ↑ 1.0 1 10

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

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

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

  • Index Cond: ((code = (((o.sender)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
  • Buffers: shared hit=40
Planning time : 16.905 ms
Execution time : 18.951 ms