explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hxJi : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #x38Q; plan #BUZl; plan #BPsk; plan #x1gn; plan #VRP7; plan #Bjlf

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.059 22,105.778 ↑ 1.0 10 1

Sort (cost=336,167.72..336,167.75 rows=10 width=2,961) (actual time=22,105.773..22,105.778 rows=10 loops=1)

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

CTE filtered_orders

3. 7.715 22,093.847 ↑ 1.0 10 1

Limit (cost=336,119.90..336,119.92 rows=10 width=557) (actual time=22,086.136..22,093.847 rows=10 loops=1)

4. 3.491 22,086.132 ↑ 3,160.4 10 1

Sort (cost=336,119.90..336,198.91 rows=31,604 width=557) (actual time=22,086.130..22,086.132 rows=10 loops=1)

  • Sort Key: o1.date_time_create DESC
  • Sort Method: top-N heapsort Memory: 27kB
5. 37.008 22,082.641 ↑ 3.0 10,366 1

HashAggregate (cost=334,804.87..335,120.91 rows=31,604 width=557) (actual time=22,067.732..22,082.641 rows=10,366 loops=1)

  • Group Key: o1.id, o1.deleted, o1.id_ek5, o1.number, o1.date_time_create
6. 0.000 22,045.633 ↑ 3.0 10,613 1

Append (cost=0.56..334,409.82 rows=31,604 width=557) (actual time=1.550..22,045.633 rows=10,613 loops=1)

7. 1,430.773 1,430.773 ↑ 3.9 2,866 1

Index Scan Backward using idx_order_payer_code_date_deleted_ex on "order" o1 (cost=0.56..12,858.39 rows=11,183 width=51) (actual time=1.549..1,430.773 rows=2,866 loops=1)

  • Index Cond: (((payer_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) 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))
  • Filter: (NOT deleted)
8. 36.795 15,916.804 ↓ 1.8 7,747 1

Sort (cost=37,782.30..37,792.81 rows=4,201 width=51) (actual time=15,913.661..15,916.804 rows=7,747 loops=1)

  • Sort Key: o1_1.date_time_create DESC
  • Sort Method: quicksort Memory: 1,282kB
9. 15,880.009 15,880.009 ↓ 1.8 7,747 1

Index Scan using idx_order_receiver_code on "order" o1_1 (cost=0.56..37,529.48 rows=4,201 width=51) (actual time=325.106..15,880.009 rows=7,747 loops=1)

  • Index Cond: ((receiver_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::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) AND (receiver_name ~~ '%а%'::text))
  • Rows Removed by Filter: 28,928
10. 35.247 4,703.371 ↓ 0.0 0 1

Gather Merge (cost=281,323.16..283,284.56 rows=16,220 width=51) (actual time=4,695.669..4,703.371 rows=0 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
11. 0.071 4,668.124 ↓ 0.0 0 6 / 6

Sort (cost=280,323.09..280,331.20 rows=3,244 width=51) (actual time=4,668.123..4,668.124 rows=0 loops=6)

  • Sort Key: o1_2.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
  • Worker 2: Sort Method: quicksort Memory: 25kB
  • Worker 3: Sort Method: quicksort Memory: 25kB
  • Worker 4: Sort Method: quicksort Memory: 25kB
12. 4,489.345 4,668.053 ↓ 0.0 0 6 / 6

Parallel Bitmap Heap Scan on "order" o1_2 (cost=10,028.54..280,133.90 rows=3,244 width=51) (actual time=4,668.053..4,668.053 rows=0 loops=6)

  • Recheck Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) 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))
  • Filter: ((NOT deleted) AND (sender_name ~~ '%a%'::text))
  • Rows Removed by Filter: 32,231
  • Heap Blocks: exact=28,771
13. 178.708 178.708 ↑ 1.3 193,870 1 / 6

Bitmap Index Scan on idx_order_sender_code_date_deleted_ex (cost=0.00..10,024.49 rows=252,315 width=0) (actual time=1,072.250..1,072.250 rows=193,870 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) 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))
14. 6.645 22,105.719 ↑ 1.0 10 1

Nested Loop Left Join (cost=2.62..47.63 rows=10 width=2,961) (actual time=22,095.383..22,105.719 rows=10 loops=1)

15. 0.024 22,099.024 ↑ 1.0 10 1

Nested Loop Left Join (cost=2.20..41.87 rows=10 width=3,093) (actual time=22,095.312..22,099.024 rows=10 loops=1)

16. 0.022 22,098.710 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.78..36.28 rows=10 width=3,072) (actual time=22,095.295..22,098.710 rows=10 loops=1)

17. 0.019 22,098.238 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.63..34.63 rows=10 width=2,556) (actual time=22,095.282..22,098.238 rows=10 loops=1)

  • Join Filter: (order_status."groupCode" = order_status_group.code)
  • Rows Removed by Join Filter: 12
18. 0.116 22,097.989 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.63..33.37 rows=10 width=2,040) (actual time=22,095.052..22,097.989 rows=10 loops=1)

19. 0.026 22,097.563 ↑ 1.0 10 1

Hash Left Join (cost=1.35..30.39 rows=10 width=2,007) (actual time=22,094.687..22,097.563 rows=10 loops=1)

  • Hash Cond: (o.straight_ek5_id = so.id_ek5)
20. 0.038 22,097.525 ↑ 1.0 10 1

Hash Left Join (cost=1.03..30.02 rows=10 width=1,507) (actual time=22,094.660..22,097.525 rows=10 loops=1)

  • Hash Cond: (o.reverse_ek5_id = ro.id_ek5)
21. 0.042 22,089.760 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.71..29.65 rows=10 width=1,007) (actual time=22,086.909..22,089.760 rows=10 loops=1)

22. 0.059 22,089.238 ↑ 1.0 10 1

Nested Loop (cost=0.56..28.00 rows=10 width=487) (actual time=22,086.473..22,089.238 rows=10 loops=1)

23. 22,086.149 22,086.149 ↑ 1.0 10 1

CTE Scan on filtered_orders t (cost=0.00..0.20 rows=10 width=16) (actual time=22,086.139..22,086.149 rows=10 loops=1)

24. 3.030 3.030 ↑ 1.0 1 10

Index Scan using "idx-order_id" on "order" o (cost=0.56..2.78 rows=1 width=503) (actual time=0.303..0.303 rows=1 loops=10)

  • Index Cond: (id = t.id)
25. 0.480 0.480 ↑ 1.0 1 10

Index Scan using "idx-order_status-code-lang" on order_status (cost=0.14..0.16 rows=1 width=524) (actual time=0.048..0.048 rows=1 loops=10)

  • Index Cond: ((o.order_status_code = code) AND ((lang)::text = 'rus'::text))
26. 0.007 7.727 ↑ 1.0 10 1

Hash (cost=0.20..0.20 rows=10 width=532) (actual time=7.726..7.727 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 7.720 7.720 ↑ 1.0 10 1

CTE Scan on filtered_orders ro (cost=0.00..0.20 rows=10 width=532) (actual time=0.002..7.720 rows=10 loops=1)

28. 0.005 0.012 ↑ 1.0 10 1

Hash (cost=0.20..0.20 rows=10 width=532) (actual time=0.012..0.012 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 0.007 0.007 ↑ 1.0 10 1

CTE Scan on filtered_orders so (cost=0.00..0.20 rows=10 width=532) (actual time=0.003..0.007 rows=10 loops=1)

30. 0.310 0.310 ↑ 1.0 1 10

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

  • Index Cond: ((o.tariff_type_code = code) AND ((lang)::text = 'rus'::text))
31. 0.008 0.230 ↓ 2.0 2 10

Materialize (cost=0.00..1.12 rows=1 width=520) (actual time=0.023..0.023 rows=2 loops=10)

32. 0.222 0.222 ↓ 3.0 3 1

Seq Scan on order_status_group (cost=0.00..1.11 rows=1 width=520) (actual time=0.221..0.222 rows=3 loops=1)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 5
33. 0.450 0.450 ↓ 0.0 0 10

Index Scan using "idx-delivery_detail_additional_status-code-lang" on delivery_detail_additional_status (cost=0.14..0.16 rows=1 width=520) (actual time=0.045..0.045 rows=0 loops=10)

  • Index Cond: ((o.order_additional_status_code = code) AND ((lang)::text = 'rus'::text))
34. 0.290 0.290 ↑ 1.0 1 10

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

  • Index Cond: ((code = o.receiver_city_code) AND ((lang)::text = 'rus'::text))
35. 0.050 0.050 ↑ 1.0 1 10

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

  • Index Cond: ((code = o.sender_city_code) AND ((lang)::text = 'rus'::text))
Planning time : 28.034 ms
Execution time : 22,106.888 ms