explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tdsw : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.052 47.995 ↓ 0.0 0 1

Sort (cost=234.03..234.04 rows=3 width=1,507) (actual time=47.995..47.995 rows=0 loops=1)

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

CTE filtered_orders

3. 0.003 47.932 ↓ 0.0 0 1

Limit (cost=219.05..219.05 rows=3 width=589) (actual time=47.932..47.932 rows=0 loops=1)

4. 0.010 47.929 ↓ 0.0 0 1

Sort (cost=219.05..219.05 rows=3 width=589) (actual time=47.929..47.929 rows=0 loops=1)

  • Sort Key: o_1.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.002 47.919 ↓ 0.0 0 1

HashAggregate (cost=218.96..218.99 rows=3 width=589) (actual time=47.919..47.919 rows=0 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 47.917 ↓ 0.0 0 1

Append (cost=85.10..218.92 rows=3 width=589) (actual time=47.917..47.917 rows=0 loops=1)

7. 0.001 31.964 ↓ 0.0 0 1

Limit (cost=85.10..85.11 rows=1 width=248) (actual time=31.964..31.964 rows=0 loops=1)

8. 0.035 31.963 ↓ 0.0 0 1

Sort (cost=85.10..85.11 rows=1 width=248) (actual time=31.962..31.963 rows=0 loops=1)

  • Sort Key: o_1.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
9. 0.014 31.928 ↓ 0.0 0 1

Bitmap Heap Scan on "order" o_1 (cost=83.98..85.09 rows=1 width=248) (actual time=31.928..31.928 rows=0 loops=1)

  • Recheck Cond: (((sender_contragent_code)::text = '82cdb59a-b8af-4e1f-a914-078c2cbdca2e'::text) AND (receiver_name ~~ '%Степанова Наталья Анатольевна%'::text))
  • Filter: (NOT deleted)
10. 0.052 31.914 ↓ 0.0 0 1

BitmapAnd (cost=83.98..83.98 rows=1 width=0) (actual time=31.914..31.914 rows=0 loops=1)

11. 27.850 27.850 ↑ 2.4 294 1

Bitmap Index Scan on idx_order_sender_code_number_department (cost=0.00..22.90 rows=694 width=0) (actual time=27.850..27.850 rows=294 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '82cdb59a-b8af-4e1f-a914-078c2cbdca2e'::text) AND (deleted = false))
12. 4.012 4.012 ↓ 0.0 0 1

Bitmap Index Scan on idx_order_receiver_code_receiver_name (cost=0.00..60.82 rows=2,683 width=0) (actual time=4.012..4.012 rows=0 loops=1)

  • Index Cond: (receiver_name ~~ '%Степанова Наталья Анатольевна%'::text)
13. 0.006 2.759 ↓ 0.0 0 1

Limit (cost=47.33..47.33 rows=1 width=248) (actual time=2.759..2.759 rows=0 loops=1)

14. 0.067 2.753 ↓ 0.0 0 1

Sort (cost=47.33..47.33 rows=1 width=248) (actual time=2.753..2.753 rows=0 loops=1)

  • Sort Key: o_2.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
15. 0.003 2.686 ↓ 0.0 0 1

Bitmap Heap Scan on "order" o_2 (cost=46.20..47.32 rows=1 width=248) (actual time=2.686..2.686 rows=0 loops=1)

  • Recheck Cond: ((receiver_name ~~ '%Степанова Наталья Анатольевна%'::text) AND ((receiver_contragent_code)::text = '82cdb59a-b8af-4e1f-a914-078c2cbdca2e'::text))
  • Filter: (NOT deleted)
16. 2.683 2.683 ↓ 0.0 0 1

Bitmap Index Scan on idx_order_receiver_code_receiver_name (cost=0.00..46.20 rows=1 width=0) (actual time=2.683..2.683 rows=0 loops=1)

  • Index Cond: ((receiver_name ~~ '%Степанова Наталья Анатольевна%'::text) AND ((receiver_contragent_code)::text = '82cdb59a-b8af-4e1f-a914-078c2cbdca2e'::text))
17. 0.006 13.184 ↓ 0.0 0 1

Limit (cost=86.43..86.44 rows=1 width=248) (actual time=13.184..13.184 rows=0 loops=1)

18. 0.043 13.178 ↓ 0.0 0 1

Sort (cost=86.43..86.44 rows=1 width=248) (actual time=13.178..13.178 rows=0 loops=1)

  • Sort Key: o_3.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
19. 0.007 13.135 ↓ 0.0 0 1

Bitmap Heap Scan on "order" o_3 (cost=85.31..86.42 rows=1 width=248) (actual time=13.135..13.135 rows=0 loops=1)

  • Recheck Cond: (((payer_contragent_code)::text = '82cdb59a-b8af-4e1f-a914-078c2cbdca2e'::text) AND (receiver_name ~~ '%Степанова Наталья Анатольевна%'::text))
  • Filter: (NOT deleted)
20. 0.015 13.128 ↓ 0.0 0 1

BitmapAnd (cost=85.31..85.31 rows=1 width=0) (actual time=13.128..13.128 rows=0 loops=1)

21. 13.031 13.031 ↑ 2.4 299 1

Bitmap Index Scan on idx_order_payer_code_number_department (cost=0.00..24.23 rows=717 width=0) (actual time=13.031..13.031 rows=299 loops=1)

  • Index Cond: (((payer_contragent_code)::text = '82cdb59a-b8af-4e1f-a914-078c2cbdca2e'::text) AND (deleted = false))
22. 0.082 0.082 ↓ 0.0 0 1

Bitmap Index Scan on idx_order_receiver_code_receiver_name (cost=0.00..60.82 rows=2,683 width=0) (actual time=0.082..0.082 rows=0 loops=1)

  • Index Cond: (receiver_name ~~ '%Степанова Наталья Анатольевна%'::text)
23. 0.001 47.943 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.29..14.96 rows=3 width=1,507) (actual time=47.943..47.943 rows=0 loops=1)

24. 0.000 47.942 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.87..13.23 rows=3 width=1,642) (actual time=47.942..47.942 rows=0 loops=1)

25. 0.001 47.942 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.45..11.55 rows=3 width=1,621) (actual time=47.942..47.942 rows=0 loops=1)

26. 0.002 47.941 ↓ 0.0 0 1

Hash Left Join (cost=1.18..10.66 rows=3 width=1,588) (actual time=47.941..47.941 rows=0 loops=1)

  • Hash Cond: (o.straight_ek5_id = so.id_ek5)
27. 0.002 47.939 ↓ 0.0 0 1

Hash Left Join (cost=1.08..10.54 rows=3 width=1,088) (actual time=47.938..47.939 rows=0 loops=1)

  • Hash Cond: (o.reverse_ek5_id = ro.id_ek5)
28. 0.000 47.937 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.98..10.42 rows=3 width=588) (actual time=47.937..47.937 rows=0 loops=1)

29. 0.001 47.937 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.84..9.93 rows=3 width=535) (actual time=47.937..47.937 rows=0 loops=1)

30. 0.001 47.936 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.70..8.88 rows=3 width=523) (actual time=47.936..47.936 rows=0 loops=1)

31. 0.001 47.935 ↓ 0.0 0 1

Nested Loop (cost=0.56..8.40 rows=3 width=486) (actual time=47.935..47.935 rows=0 loops=1)

32. 47.934 47.934 ↓ 0.0 0 1

CTE Scan on filtered_orders t (cost=0.00..0.06 rows=3 width=16) (actual time=47.934..47.934 rows=0 loops=1)

33. 0.000 0.000 ↓ 0.0 0

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

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

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

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

Index Scan using "idx-order_status_group-code-lang" on order_status_group (cost=0.14..0.39 rows=1 width=16) (never executed)

  • Index Cond: ((order_status."groupCode" = code) 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

Hash (cost=0.06..0.06 rows=3 width=532) (never executed)

38. 0.000 0.000 ↓ 0.0 0

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

39. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.06..0.06 rows=3 width=532) (never executed)

40. 0.000 0.000 ↓ 0.0 0

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

41. 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))
42. 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))
43. 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 : 302.197 ms
Execution time : 104.295 ms