explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1HSe : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #8tg; plan #9rN1; plan #KO6D; plan #Jn51; plan #5S8s; plan #rQtg; plan #e8ot; plan #x1v9; plan #60x7; plan #OmB0s; plan #kT8M; plan #hBBo; plan #PBsg; plan #jWRF; plan #vUhJ; plan #guMs; plan #tNcR; plan #AIoi; plan #7xDy; plan #i9ct; plan #aXXT; plan #W38d; plan #bZt4; plan #ECjq; plan #yhLv; plan #gq6g; plan #C4qS; plan #tdsw

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.062 60,912.935 ↑ 1.5 2 1

Sort (cost=2,799.19..2,799.20 rows=3 width=1,507) (actual time=60,912.934..60,912.935 rows=2 loops=1)

  • Sort Key: o.date_time_create DESC
  • Sort Method: quicksort Memory: 26kB
2.          

CTE filtered_orders

3. 0.004 60,911.759 ↑ 1.5 2 1

Limit (cost=2,784.20..2,784.21 rows=3 width=589) (actual time=60,911.758..60,911.759 rows=2 loops=1)

4. 0.019 60,911.755 ↑ 1.5 2 1

Sort (cost=2,784.20..2,784.21 rows=3 width=589) (actual time=60,911.755..60,911.755 rows=2 loops=1)

  • Sort Key: o_1.date_time_create DESC
  • Sort Method: quicksort Memory: 26kB
5. 0.087 60,911.736 ↑ 1.5 2 1

HashAggregate (cost=2,784.12..2,784.15 rows=3 width=589) (actual time=60,911.735..60,911.736 rows=2 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. 0.010 60,911.649 ↓ 1.3 4 1

Append (cost=1,798.06..2,784.07 rows=3 width=589) (actual time=19,502.490..60,911.649 rows=4 loops=1)

7. 0.002 19,502.489 ↓ 2.0 2 1

Limit (cost=1,798.06..1,798.07 rows=1 width=248) (actual time=19,502.488..19,502.489 rows=2 loops=1)

8. 0.024 19,502.487 ↓ 2.0 2 1

Sort (cost=1,798.06..1,798.07 rows=1 width=248) (actual time=19,502.487..19,502.487 rows=2 loops=1)

  • Sort Key: o_1.date_time_create DESC
  • Sort Method: quicksort Memory: 26kB
9. 5.450 19,502.463 ↓ 2.0 2 1

Bitmap Heap Scan on "order" o_1 (cost=1,796.93..1,798.05 rows=1 width=248) (actual time=19,502.056..19,502.463 rows=2 loops=1)

  • Recheck Cond: (((sender_contragent_code)::text = 'e443aa33-f405-4d76-8341-23474ccf691a'::text) AND (((receiver)::json #>> '{phones,0,number}'::text[]) ~~ '%+79219628734%'::text))
  • Rows Removed by Index Recheck: 3
  • Filter: (NOT deleted)
  • Heap Blocks: exact=4
10. 3.373 19,497.013 ↓ 0.0 0 1

BitmapAnd (cost=1,796.93..1,796.93 rows=1 width=0) (actual time=19,497.013..19,497.013 rows=0 loops=1)

11. 651.253 651.253 ↓ 1.7 22,730 1

Bitmap Index Scan on idx_order_sender_code_number_department (cost=0.00..422.03 rows=13,657 width=0) (actual time=651.253..651.253 rows=22,730 loops=1)

  • Index Cond: (((sender_contragent_code)::text = 'e443aa33-f405-4d76-8341-23474ccf691a'::text) AND (deleted = false))
12. 18,842.387 18,842.387 ↑ 548.0 5 1

Bitmap Index Scan on idx_order_receiver_phone_trgm (cost=0.00..1,374.65 rows=2,740 width=0) (actual time=18,842.387..18,842.387 rows=5 loops=1)

  • Index Cond: (((receiver)::json #>> '{phones,0,number}'::text[]) ~~ '%+79219628734%'::text)
13. 0.005 169.856 ↓ 0.0 0 1

Limit (cost=0.56..158.69 rows=1 width=248) (actual time=169.856..169.856 rows=0 loops=1)

14. 169.851 169.851 ↓ 0.0 0 1

Index Scan Backward using idx_order_receiver_code_number_department on "order" o_2 (cost=0.56..158.69 rows=1 width=248) (actual time=169.851..169.851 rows=0 loops=1)

  • Index Cond: (((receiver_contragent_code)::text = 'e443aa33-f405-4d76-8341-23474ccf691a'::text) AND (deleted = false))
  • Filter: ((NOT deleted) AND (((receiver)::json #>> '{phones,0,number}'::text[]) ~~ '%+79219628734%'::text))
  • Rows Removed by Filter: 113
15. 0.005 41,239.294 ↓ 2.0 2 1

Limit (cost=0.56..827.27 rows=1 width=248) (actual time=21,655.504..41,239.294 rows=2 loops=1)

16. 41,239.289 41,239.289 ↓ 2.0 2 1

Index Scan Backward using idx_order_payer_code_number_department on "order" o_3 (cost=0.56..827.27 rows=1 width=248) (actual time=21,655.501..41,239.289 rows=2 loops=1)

  • Index Cond: (((payer_contragent_code)::text = 'e443aa33-f405-4d76-8341-23474ccf691a'::text) AND (deleted = false))
  • Filter: ((NOT deleted) AND (((receiver)::json #>> '{phones,0,number}'::text[]) ~~ '%+79219628734%'::text))
  • Rows Removed by Filter: 22750
17. 0.755 60,912.873 ↑ 1.5 2 1

Nested Loop Left Join (cost=2.29..14.96 rows=3 width=1,507) (actual time=60,912.605..60,912.873 rows=2 loops=1)

18. 0.009 60,912.110 ↑ 1.5 2 1

Nested Loop Left Join (cost=1.87..13.23 rows=3 width=1,642) (actual time=60,911.915..60,912.110 rows=2 loops=1)

19. 0.011 60,912.081 ↑ 1.5 2 1

Nested Loop Left Join (cost=1.45..11.55 rows=3 width=1,621) (actual time=60,911.899..60,912.081 rows=2 loops=1)

20. 0.012 60,912.048 ↑ 1.5 2 1

Hash Left Join (cost=1.18..10.66 rows=3 width=1,588) (actual time=60,911.879..60,912.048 rows=2 loops=1)

  • Hash Cond: (o.straight_ek5_id = so.id_ek5)
21. 0.029 60,912.027 ↑ 1.5 2 1

Hash Left Join (cost=1.08..10.54 rows=3 width=1,088) (actual time=60,911.861..60,912.027 rows=2 loops=1)

  • Hash Cond: (o.reverse_ek5_id = ro.id_ek5)
22. 0.011 60,911.991 ↑ 1.5 2 1

Nested Loop Left Join (cost=0.98..10.42 rows=3 width=588) (actual time=60,911.828..60,911.991 rows=2 loops=1)

23. 0.011 60,911.978 ↑ 1.5 2 1

Nested Loop Left Join (cost=0.84..9.93 rows=3 width=535) (actual time=60,911.821..60,911.978 rows=2 loops=1)

24. 0.011 60,911.943 ↑ 1.5 2 1

Nested Loop Left Join (cost=0.70..8.88 rows=3 width=523) (actual time=60,911.800..60,911.943 rows=2 loops=1)

25. 0.016 60,911.810 ↑ 1.5 2 1

Nested Loop (cost=0.56..8.40 rows=3 width=486) (actual time=60,911.784..60,911.810 rows=2 loops=1)

26. 60,911.764 60,911.764 ↑ 1.5 2 1

CTE Scan on filtered_orders t (cost=0.00..0.06 rows=3 width=16) (actual time=60,911.761..60,911.764 rows=2 loops=1)

27. 0.030 0.030 ↑ 1.0 1 2

Index Scan using "idx-order_id" on "order" o (cost=0.56..2.78 rows=1 width=502) (actual time=0.015..0.015 rows=1 loops=2)

  • Index Cond: (id = t.id)
28. 0.122 0.122 ↑ 1.0 1 2

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

  • Index Cond: ((o.order_status_code = code) AND ((lang)::text = 'rus'::text))
29. 0.024 0.024 ↑ 1.0 1 2

Index Scan using "idx-order_status_group-code-lang" on order_status_group (cost=0.14..0.39 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=2)

  • Index Cond: ((order_status."groupCode" = code) AND ((lang)::text = 'rus'::text))
30. 0.002 0.002 ↓ 0.0 0 2

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.001..0.001 rows=0 loops=2)

  • Index Cond: ((o.order_additional_status_code = code) AND ((lang)::text = 'rus'::text))
31. 0.003 0.007 ↑ 1.5 2 1

Hash (cost=0.06..0.06 rows=3 width=532) (actual time=0.007..0.007 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.004 0.004 ↑ 1.5 2 1

CTE Scan on filtered_orders ro (cost=0.00..0.06 rows=3 width=532) (actual time=0.002..0.004 rows=2 loops=1)

33. 0.008 0.009 ↑ 1.5 2 1

Hash (cost=0.06..0.06 rows=3 width=532) (actual time=0.009..0.009 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.001 0.001 ↑ 1.5 2 1

CTE Scan on filtered_orders so (cost=0.00..0.06 rows=3 width=532) (actual time=0.001..0.001 rows=2 loops=1)

35. 0.022 0.022 ↑ 1.0 1 2

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

  • Index Cond: ((o.tariff_type_code = code) AND ((lang)::text = 'rus'::text))
36. 0.020 0.020 ↑ 1.0 1 2

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

  • Index Cond: ((code = o.receiver_city_code) AND ((lang)::text = 'rus'::text))
37. 0.008 0.008 ↑ 1.0 1 2

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

  • Index Cond: ((code = o.sender_city_code) AND ((lang)::text = 'rus'::text))
Planning time : 192.744 ms
Execution time : 60,916.557 ms