explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.008 1,700.077 ↓ 0.0 0 1

Sort (cost=4,836.15..4,836.16 rows=1 width=1,511) (actual time=1,700.077..1,700.077 rows=0 loops=1)

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

CTE filtered_orders

3. 0.001 1,700.057 ↓ 0.0 0 1

Limit (cost=4,830.35..4,830.35 rows=1 width=557) (actual time=1,700.057..1,700.057 rows=0 loops=1)

4. 0.002 1,700.056 ↓ 0.0 0 1

Sort (cost=4,830.30..4,830.35 rows=19 width=557) (actual time=1,700.056..1,700.056 rows=0 loops=1)

  • Sort Key: o1.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.003 1,700.054 ↓ 0.0 0 1

HashAggregate (cost=4,829.52..4,829.71 rows=19 width=557) (actual time=1,700.054..1,700.054 rows=0 loops=1)

  • Group Key: o1.id, o1.deleted, o1.id_ek5, o1.number, o1.date_time_create
6. 0.010 1,700.051 ↓ 0.0 0 1

Append (cost=1,083.17..4,829.28 rows=19 width=557) (actual time=1,700.051..1,700.051 rows=0 loops=1)

7. 0.004 223.602 ↓ 0.0 0 1

Sort (cost=1,083.17..1,083.18 rows=1 width=51) (actual time=223.601..223.602 rows=0 loops=1)

  • Sort Key: o1.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
8. 0.025 223.598 ↓ 0.0 0 1

Bitmap Heap Scan on "order" o1 (cost=1,082.04..1,083.16 rows=1 width=51) (actual time=223.598..223.598 rows=0 loops=1)

  • Recheck Cond: (((payer_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) AND (sender_name ~~ '%петров%'::text))
  • Filter: (NOT deleted)
9. 0.716 223.573 ↓ 0.0 0 1

BitmapAnd (cost=1,082.04..1,082.04 rows=1 width=0) (actual time=223.573..223.573 rows=0 loops=1)

10. 1.563 1.563 ↑ 4.1 3,138 1

Bitmap Index Scan on idx_order_payer_code_date_deleted_ex (cost=0.00..530.66 rows=12,973 width=0) (actual time=1.563..1.563 rows=3,138 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))
11. 221.294 221.294 ↓ 87.4 167,075 1

Bitmap Index Scan on idx_order_sender_name (cost=0.00..551.13 rows=1,911 width=0) (actual time=221.294..221.294 rows=167,075 loops=1)

  • Index Cond: (sender_name ~~ '%петров%'::text)
12. 0.012 245.022 ↓ 0.0 0 1

Sort (cost=1,056.87..1,056.87 rows=1 width=51) (actual time=245.021..245.022 rows=0 loops=1)

  • Sort Key: o1_1.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
13. 1.945 245.010 ↓ 0.0 0 1

Bitmap Heap Scan on "order" o1_1 (cost=1,053.50..1,056.86 rows=1 width=51) (actual time=245.010..245.010 rows=0 loops=1)

  • Recheck Cond: (((receiver_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (sender_name ~~ '%петров%'::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))
14. 7.074 243.065 ↓ 0.0 0 1

BitmapAnd (cost=1,053.50..1,053.50 rows=3 width=0) (actual time=243.065..243.065 rows=0 loops=1)

15. 13.284 13.284 ↑ 1.1 40,127 1

Bitmap Index Scan on idx_order_receiver_code_receiver_name (cost=0.00..502.11 rows=43,189 width=0) (actual time=13.284..13.284 rows=40,127 loops=1)

  • Index Cond: ((receiver_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text)
16. 222.707 222.707 ↓ 87.4 167,075 1

Bitmap Index Scan on idx_order_sender_name (cost=0.00..551.13 rows=1,911 width=0) (actual time=222.707..222.707 rows=167,075 loops=1)

  • Index Cond: (sender_name ~~ '%петров%'::text)
17. 0.028 1,231.417 ↓ 0.0 0 1

Sort (cost=2,688.91..2,688.95 rows=17 width=51) (actual time=1,231.417..1,231.417 rows=0 loops=1)

  • Sort Key: o1_2.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
18. 1,003.497 1,231.389 ↓ 0.0 0 1

Bitmap Heap Scan on "order" o1_2 (cost=551.14..2,688.56 rows=17 width=51) (actual time=1,231.389..1,231.389 rows=0 loops=1)

  • Recheck Cond: (sender_name ~~ '%петров%'::text)
  • Rows Removed by Index Recheck: 153,253
  • 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 ((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text))
  • Rows Removed by Filter: 1,952
  • Heap Blocks: exact=160,588
19. 227.892 227.892 ↓ 87.4 167,075 1

Bitmap Index Scan on idx_order_sender_name (cost=0.00..551.13 rows=1,911 width=0) (actual time=227.891..227.892 rows=167,075 loops=1)

  • Index Cond: (sender_name ~~ '%петров%'::text)
20. 0.004 1,700.069 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.97..5.79 rows=1 width=1,511) (actual time=1,700.069..1,700.069 rows=0 loops=1)

21. 0.000 1,700.065 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.55..5.21 rows=1 width=1,645) (actual time=1,700.065..1,700.065 rows=0 loops=1)

22. 0.001 1,700.065 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.13..4.65 rows=1 width=1,624) (actual time=1,700.065..1,700.065 rows=0 loops=1)

23. 0.000 1,700.064 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.98..4.49 rows=1 width=1,571) (actual time=1,700.064..1,700.064 rows=0 loops=1)

  • Join Filter: (order_status."groupCode" = order_status_group.code)
24. 0.000 1,700.064 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.98..3.34 rows=1 width=1,559) (actual time=1,700.064..1,700.064 rows=0 loops=1)

25. 0.001 1,700.064 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.71..3.05 rows=1 width=1,526) (actual time=1,700.064..1,700.064 rows=0 loops=1)

  • Join Filter: (o.straight_ek5_id = so.id_ek5)
26. 0.000 1,700.063 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.71..3.01 rows=1 width=1,026) (actual time=1,700.063..1,700.063 rows=0 loops=1)

  • Join Filter: (o.reverse_ek5_id = ro.id_ek5)
27. 0.001 1,700.063 ↓ 0.0 0 1

Nested Loop (cost=0.71..2.98 rows=1 width=526) (actual time=1,700.063..1,700.063 rows=0 loops=1)

28. 0.000 1,700.062 ↓ 0.0 0 1

Nested Loop (cost=0.56..2.80 rows=1 width=489) (actual time=1,700.062..1,700.062 rows=0 loops=1)

29. 1,700.062 1,700.062 ↓ 0.0 0 1

CTE Scan on filtered_orders t (cost=0.00..0.02 rows=1 width=16) (actual time=1,700.062..1,700.062 rows=0 loops=1)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-order_id" on "order" o (cost=0.56..2.78 rows=1 width=505) (never executed)

  • Index Cond: (id = t.id)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-order_status-code-lang" on order_status (cost=0.14..0.17 rows=1 width=41) (never executed)

  • Index Cond: ((code = o.order_status_code) AND ((lang)::text = 'rus'::text))
  • Filter: ("groupCode" = 3)
32. 0.000 0.000 ↓ 0.0 0

CTE Scan on filtered_orders ro (cost=0.00..0.02 rows=1 width=532) (never executed)

33. 0.000 0.000 ↓ 0.0 0

CTE Scan on filtered_orders so (cost=0.00..0.02 rows=1 width=532) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-tariff_type-code-lang" on tariff_type (cost=0.28..0.30 rows=1 width=41) (never executed)

  • Index Cond: ((o.tariff_type_code = code) AND ((lang)::text = 'rus'::text))
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on order_status_group (cost=0.00..1.14 rows=1 width=16) (never executed)

  • Filter: ((code = 3) AND ((lang)::text = 'rus'::text))
36. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-delivery_detail_additional_status-code-lang" on delivery_detail_additional_status (cost=0.14..0.16 rows=1 width=57) (never executed)

  • Index Cond: ((o.order_additional_status_code = code) AND ((lang)::text = 'rus'::text))
37. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-city-code-lang" on city rc (cost=0.42..0.56 rows=1 width=29) (never executed)

  • Index Cond: ((code = o.receiver_city_code) AND ((lang)::text = 'rus'::text))
38. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-city-code-lang" on city sc (cost=0.42..0.56 rows=1 width=29) (never executed)

  • Index Cond: ((code = o.sender_city_code) AND ((lang)::text = 'rus'::text))
Planning time : 6.500 ms
Execution time : 1,700.341 ms