explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ariJ : Optimization for: Реестр запрсов; plan #iXHh

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7.793 11,188.254 ↓ 24.1 9,867 1

Unique (cost=7,624.28..7,673.48 rows=410 width=1,176) (actual time=11,177.376..11,188.254 rows=9,867 loops=1)

  • resp_inter.number, resp_final.agreement_send_date, resp_final.number, resp_final.registered_date, resp_final.email_send_date, (CASE WHEN ((req_status.code)::text = 'completed'::text) THEN req.finish_date ELSE NULL::timestamp without time
  • zone END), req_status.name, (CASE WHEN (decr_resp.id IS NULL) THEN NULL::text WHEN (decr_resp.template_id IS NOT NULL) THEN 'with_template'::text ELSE 'without_template'::text END)
  • '2020-07-01 20:59:59'::timestamp without time zone))
2.          

CTE itop_cte

3. 46.721 61.099 ↓ 1.1 7,565 1

GroupAggregate (cost=0.29..1,632.29 rows=6,789 width=31) (actual time=0.134..61.099 rows=7,565 loops=1)

  • Group Key: itop_tickets.decree_id
4. 14.378 14.378 ↓ 1.1 8,300 1

Index Scan using support_tickets_team_id on support_tickets itop_tickets (cost=0.29..1,322.55 rows=7,637 width=31) (actual time=0.046..14.378 rows=8,300 loops=1)

  • Index Cond: (team_id = 171,186)
5.          

CTE itop_osrd

6. 2.530 3.742 ↓ 1.1 951 1

GroupAggregate (cost=0.29..604.15 rows=833 width=31) (actual time=0.235..3.742 rows=951 loops=1)

  • Group Key: itop_tickets_1.decree_id
7. 1.212 1.212 ↓ 1.0 962 1

Index Scan using support_tickets_team_id on support_tickets itop_tickets_1 (cost=0.29..566.18 rows=936 width=31) (actual time=0.095..1.212 rows=962 loops=1)

  • Index Cond: (team_id = 169,559)
8.          

CTE itop_drso

9. 0.126 0.245 ↓ 1.3 35 1

GroupAggregate (cost=0.29..25.45 rows=26 width=31) (actual time=0.033..0.245 rows=35 loops=1)

  • Group Key: itop_tickets_2.decree_id
10. 0.119 0.119 ↓ 1.4 41 1

Index Scan using support_tickets_team_id on support_tickets itop_tickets_2 (cost=0.29..24.27 rows=29 width=31) (actual time=0.007..0.119 rows=41 loops=1)

  • Index Cond: (team_id = 1,081)
11. 59.917 11,180.461 ↓ 28.2 11,560 1

Sort (cost=5,362.39..5,363.41 rows=410 width=1,176) (actual time=11,177.375..11,180.461 rows=11,560 loops=1)

  • Sort Key: req.id, decr.id, req_t.name, corresp.name, req.start_date, req.outgoing_number, req.outgoing_date, req.number, req.registered_date, resp_inter.registered_date, req.deadline_date, ((SubPlan 4)), req.assignee_user, (CASE
  • Sort Method: external merge Disk: 6,080kB
12. 23.628 11,120.544 ↓ 28.2 11,560 1

Hash Left Join (cost=2,020.04..5,344.60 rows=410 width=1,176) (actual time=11,071.863..11,120.544 rows=11,560 loops=1)

  • Hash Cond: (decr.id = itop_drso.decree_id)
13. 9.740 11,085.079 ↓ 28.2 11,560 1

Hash Right Join (cost=2,019.19..2,039.04 rows=410 width=1,080) (actual time=11,071.485..11,085.079 rows=11,560 loops=1)

  • Hash Cond: (itop_osrd.decree_id = decr.id)
14. 4.124 4.124 ↓ 1.1 951 1

CTE Scan on itop_osrd (cost=0.00..16.66 rows=833 width=100) (actual time=0.241..4.124 rows=951 loops=1)

15. 37.733 11,071.215 ↓ 28.2 11,560 1

Hash (cost=2,014.07..2,014.07 rows=410 width=984) (actual time=11,071.215..11,071.215 rows=11,560 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 6,395kB
16. 29.506 11,033.482 ↓ 28.2 11,560 1

Nested Loop Left Join (cost=861.36..2,014.07 rows=410 width=984) (actual time=116.594..11,033.482 rows=11,560 loops=1)

  • Filter: ((COALESCE(ct.is_violator_violation, ct.is_violator_request, ct.is_violator_manual))::text = ANY ('{Да,Нет}'::text[]))
  • Rows Removed by Filter: 1,502
17. 4.329 410.694 ↓ 318.6 13,062 1

Nested Loop Left Join (cost=861.36..1,091.56 rows=41 width=444) (actual time=114.748..410.694 rows=13,062 loops=1)

18. 5.899 360.189 ↓ 288.6 11,544 1

Nested Loop Left Join (cost=861.07..1,077.39 rows=40 width=444) (actual time=114.721..360.189 rows=11,544 loops=1)

19. 18.310 342.746 ↓ 288.6 11,544 1

Nested Loop Left Join (cost=860.78..1,064.22 rows=40 width=448) (actual time=114.716..342.746 rows=11,544 loops=1)

20. 11.621 278.260 ↓ 288.6 11,544 1

Nested Loop Left Join (cost=860.49..1,049.07 rows=40 width=440) (actual time=114.710..278.260 rows=11,544 loops=1)

21. 16.087 243.939 ↓ 283.8 11,350 1

Nested Loop Left Join (cost=860.20..1,035.05 rows=40 width=440) (actual time=114.699..243.939 rows=11,350 loops=1)

22. 34.241 216.502 ↓ 283.8 11,350 1

Hash Right Join (cost=859.91..1,021.21 rows=40 width=436) (actual time=114.696..216.502 rows=11,350 loops=1)

  • Hash Cond: (itop_cte.decree_id = decr.id)
23. 67.733 67.733 ↓ 1.1 7,565 1

CTE Scan on itop_cte (cost=0.00..135.78 rows=6,789 width=100) (actual time=0.138..67.733 rows=7,565 loops=1)

24. 9.147 114.528 ↓ 283.8 11,350 1

Hash (cost=859.41..859.41 rows=40 width=340) (actual time=114.528..114.528 rows=11,350 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 3,446kB
25. 0.331 105.381 ↓ 283.8 11,350 1

Nested Loop Left Join (cost=1.74..859.41 rows=40 width=340) (actual time=0.124..105.381 rows=11,350 loops=1)

26. 16.671 83.640 ↓ 297.4 10,705 1

Nested Loop Left Join (cost=1.45..843.53 rows=36 width=279) (actual time=0.117..83.640 rows=10,705 loops=1)

  • -> Index Scan using index_responses_on_request_id on responses resp_final (cost=0.29..0.42 rows=1 width=43) (actual time=0.001..0.001 rows=1 loops=10
27. 21.495 66.969 ↓ 297.4 10,705 1

Nested Loop Left Join (cost=1.16..827.96 rows=36 width=240) (actual time=0.114..66.969 rows=10,705 loops=1)

  • -> Index Scan using index_responses_on_request_id on responses resp_inter (cost=0.29..0.42 rows=1 width=35) (actual time=0.002..0.002 rows=0 lo
  • Index Cond: (request_id = req.id)
  • Filter: (NOT intermediate)
  • Rows Removed by Filter: 0
28. 13.801 45.474 ↓ 297.4 10,705 1

Nested Loop Left Join (cost=0.86..812.38 rows=36 width=209) (actual time=0.093..45.474 rows=10,705 loops=1)

  • -> Index Scan using correspondents_pkey on correspondents corresp (cost=0.28..0.29 rows=1 width=53) (actual time=0.001..0.001 rows=1 loop
  • Index Cond: (request_id = req.id)
  • Filter: intermediate
  • Rows Removed by Filter: 1
29. 11.499 31.673 ↓ 297.4 10,705 1

Nested Loop (cost=0.59..801.43 rows=36 width=164) (actual time=0.085..31.673 rows=10,705 loops=1)

  • -> Index Scan using request_statuses_pkey on request_statuses req_status (cost=0.15..0.17 rows=1 width=68) (actual time=0.001..0.00
  • Index Cond: (id = req.correspondent_id)
30. 20.142 20.174 ↓ 297.4 10,705 1

Nested Loop (cost=0.44..794.91 rows=36 width=104) (actual time=0.061..20.174 rows=10,705 loops=1)

  • -> Index Scan using index_type_status_assignee_user on requests req (cost=0.29..358.32 rows=3,674 width=76) (actual time=0.022
  • Index Cond: (id = req.request_status_id)
31. 0.022 0.032 ↑ 1.0 2 1

Nested Loop (cost=0.15..4.79 rows=2 width=40) (actual time=0.023..0.032 rows=2 loops=1)

  • -> Index Scan using request_types_pkey on request_types req_t (cost=0.15..2.37 rows=1 width=36) (actual time=0.007..0.0
  • Index Cond: (request_type_id = req_t.id)
  • Filter: ((NOT hidden) AND (registered_date >= '2019-12-31 21:00:00'::timestamp without time zone) AND (registered_date <=
  • Rows Removed by Filter: 15,829
32. 0.010 0.010 ↑ 1.0 2 1

Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=4) (actual time=0.008..0.010 rows=2 loops=1)

  • Index Cond: (id = "*VALUES*".column1)
33. 21.410 21.410 ↑ 1.0 1 10,705

Index Scan using index_decrees_on_request_id on decrees decr (cost=0.29..0.43 rows=1 width=65) (actual time=0.001..0.002 rows=1 loops=10,705)

  • Index Cond: (request_id = req.id)
34. 11.350 11.350 ↓ 0.0 0 11,350

Index Scan using decrees_pkey on decrees parent_decr (cost=0.29..0.34 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=11,350)

  • Index Cond: (id = decr.parent_id)
35. 22.700 22.700 ↓ 0.0 0 11,350

Index Only Scan using index_decrees_on_parent_id on decrees child_decr (cost=0.29..0.33 rows=2 width=4) (actual time=0.002..0.002 rows=0 loops=11,350)

  • Index Cond: (parent_id = decr.id)
  • Heap Fetches: 26
36. 46.176 46.176 ↑ 1.0 1 11,544

Index Scan using decree_id_unique on decree_responses decr_resp (cost=0.29..0.37 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=11,544)

  • Index Cond: (decree_id = decr.id)
37. 11.544 11.544 ↓ 0.0 0 11,544

Index Only Scan using requests_pkey on requests parent_req (cost=0.29..0.32 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=11,544)

  • Index Cond: (id = parent_decr.request_id)
  • Heap Fetches: 567
38. 46.176 46.176 ↑ 1.0 1 11,544

Index Only Scan using index_support_tickets_on_decree_id on support_tickets st (cost=0.29..0.34 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=11,544)

  • Index Cond: (decree_id = decr.id)
  • Heap Fetches: 10,004
39. 10,593.282 10,593.282 ↑ 1,000.0 1 13,062

Function Scan on crosstab ct (cost=0.01..10.01 rows=1,000 width=544) (actual time=0.811..0.811 rows=1 loops=13,062)

40. 0.010 0.277 ↓ 1.3 34 1

Hash (cost=0.52..0.52 rows=26 width=100) (actual time=0.277..0.277 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 3kB
41. 0.267 0.267 ↓ 1.3 35 1

CTE Scan on itop_drso (cost=0.00..0.52 rows=26 width=100) (actual time=0.036..0.267 rows=35 loops=1)

42.          

SubPlan (for Hash Left Join)

43. 0.000 0.000 ↓ 0.0 0 11,560

Limit (cost=0.58..5.03 rows=1 width=5) (actual time=0.000..0.000 rows=0 loops=11,560)

44. 0.000 0.000 ↓ 0.0 0 11,560

Nested Loop (cost=0.58..5.03 rows=1 width=5) (actual time=0.000..0.000 rows=0 loops=11,560)

45. 0.000 0.000 ↓ 0.0 0 11,560

Index Scan using decrees_pkey on decrees d (cost=0.29..2.51 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=11,560)

  • Index Cond: (id = decr.parent_id)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using requests_pkey on requests r (cost=0.29..2.51 rows=1 width=9) (never executed)

  • Index Cond: (id = d.request_id)
47. 0.000 11.560 ↑ 1.0 1 11,560

Aggregate (cost=3.01..3.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=11,560)

48. 11.560 11.560 ↓ 0.0 0 11,560

Index Scan using index_decrees_on_parent_id on decrees d_1 (cost=0.29..3.01 rows=2 width=4) (actual time=0.001..0.001 rows=0 loops=11,560)

  • Index Cond: (parent_id = decr.id)
Planning time : 9.515 ms
Execution time : 11,229.358 ms