explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F8pm

Settings
# exclusive inclusive rows x rows loops node
1. 1.102 2,865,340.651 ↓ 416.0 416 1

Sort (cost=762,500.67..762,500.68 rows=1 width=74) (actual time=2,865,340.624..2,865,340.651 rows=416 loops=1)

  • Sort Key: r.special_control DESC, ur.name
  • Sort Method: quicksort Memory: 84kB
2. 2.093 2,865,339.549 ↓ 416.0 416 1

Nested Loop Left Join (cost=4.82..762,500.66 rows=1 width=74) (actual time=10,378.211..2,865,339.549 rows=416 loops=1)

3. 1.091 2,861,688.720 ↓ 416.0 416 1

Nested Loop Left Join (cost=4.39..762,470.13 rows=1 width=61) (actual time=10,368.866..2,861,688.720 rows=416 loops=1)

4. 0.834 2,857,858.765 ↓ 416.0 416 1

Nested Loop (cost=3.82..762,466.54 rows=1 width=61) (actual time=10,366.663..2,857,858.765 rows=416 loops=1)

5. 6.503 2,857,818.411 ↓ 416.0 416 1

Nested Loop (cost=3.40..762,464.34 rows=1 width=55) (actual time=10,366.221..2,857,818.411 rows=416 loops=1)

6. 366.885 2,857,748.524 ↓ 7,923.0 7,923 1

Nested Loop (cost=2.97..762,462.14 rows=1 width=63) (actual time=237.625..2,857,748.524 rows=7,923 loops=1)

7. 211.519 684,079.559 ↓ 410.9 729,296 1

Nested Loop (cost=2.41..707,795.68 rows=1,775 width=39) (actual time=5.518..684,079.559 rows=729,296 loops=1)

8. 1,040.318 487,691.752 ↓ 346.4 4,670,864 1

Nested Loop (cost=1.83..658,977.37 rows=13,484 width=31) (actual time=5.177..487,691.752 rows=4,670,864 loops=1)

9. 587.977 23,858.765 ↓ 52.5 548,983 1

Nested Loop (cost=1.26..330,356.23 rows=10,461 width=29) (actual time=1.910..23,858.765 rows=548,983 loops=1)

10. 5,702.372 5,702.372 ↓ 51.7 549,013 1

Index Only Scan using document_n588_test1 on document_n dn (cost=0.70..280,375.38 rows=10,619 width=21) (actual time=1.394..5,702.372 rows=549,013 loops=1)

  • Index Cond: ((org_id = 29) AND (n = 0))
  • Filter: ((category <> 6) AND (CASE WHEN (num IS NULL) THEN 0 ELSE 1 END = 1) AND (((num)::text ~~ '01-02-06%'::text) OR ((num)::text ~~ '01-03-06%'::text) OR ((num)::text ~~ '01-04-06%'::text) OR ((num)::text ~~ '05-07%'::text) OR ((num)::text ~~ '05-08%'::text) OR ((num)::text ~~ '05-09%'::text) OR ((num)::text ~~ '05-10%'::text) OR ((num)::text ~~ '05-11%'::text) OR ((num)::text ~~ '05-12%'::text) OR ((num)::text ~~ '05-13%'::text) OR ((num)::text ~~ '05-14%'::text) OR ((num)::text ~~ '05-15%'::text) OR ((num)::text ~~ '05-16%'::text) OR ((num)::text ~~ 'ВН-01-02-06%'::text) OR ((num)::text ~~ 'ВН-01-03-06%'::text) OR ((num)::text ~~ 'ВН-01-04-06%'::text) OR ((num)::text ~~ 'ВН-05-01%'::text) OR ((num)::text ~~ 'ВН-05-02%'::text) OR ((num)::text ~~ 'ВН-05-03%'::text) OR ((num)::text ~~ 'ВН-05-04%'::text) OR ((num)::text ~~ 'ВН-05-05%'::text) OR ((num)::text ~~ 'ВН-05-06%'::text) OR ((num)::text ~~ 'ВН-05-07%'::text) OR ((num)::text ~~ 'ВН-05-08%'::text) OR ((num)::text ~~ 'ВН-05-09%'::text) OR ((num)::text ~~ 'ВН-05-10%'::text) OR ((num)::text ~~ 'ВН-05-11%'::text) OR ((num)::text ~~ 'ВН-05-12%'::text) OR ((num)::text ~~ 'ВН-05-13%'::text) OR ((num)::text ~~ 'ВН-05-14%'::text) OR ((num)::text ~~ 'ВН-05-15%'::text) OR ((num)::text ~~ 'ВН-05-16%'::text) OR ((num)::text ~~ 'ВН-05-17%'::text) OR ((num)::text ~~ 'ВН-05-18%'::text) OR ((num)::text ~~ 'ВН-05-18и%'::text) OR ((num)::text ~~ 'ВН-05-19%'::text) OR ((num)::text ~~ 'ВН-05-20%'::text) OR ((num)::text ~~ 'ВН-05-21%'::text) OR ((num)::text ~~ 'ВН-05-22%'::text) OR ((num)::text ~~ 'ВОЗВРАТ%'::text) OR ((num)::text ~~ 'ВОЗВРАТ ВН%'::text) OR ((num)::text ~~ 'ДПиООС 01-02-06%'::text) OR ((num)::text ~~ 'ДПиООС 01-03-06%'::text) OR ((num)::text ~~ 'ДПиООС 01-04-06%'::text) OR ((num)::text ~~ 'ДПиООС 05-01%'::text) OR ((num)::text ~~ 'ДПиООС 05-02%'::text) OR ((num)::text ~~ 'ДПиООС 05-03%'::text) OR ((num)::text ~~ 'ДПиООС 05-04%'::text) OR ((num)::text ~~ 'ДПиООС 05-05%'::text) OR ((num)::text ~~ 'ДПиООС 05-06%'::text) OR ((num)::text ~~ 'ДПиООС 05-07%'::text) OR ((num)::text ~~ 'ДПиООС 05-08%'::text) OR ((num)::text ~~ 'ДПиООС 05-09%'::text) OR ((num)::text ~~ 'ДПиООС 05-10%'::text) OR ((num)::text ~~ 'ДПиООС 05-11%'::text) OR ((num)::text ~~ 'ДПиООС 05-12%'::text) OR ((num)::text ~~ 'ДПиООС 05-13%'::text) OR ((num)::text ~~ 'ДПиООС 05-14%'::text) OR ((num)::text ~~ 'ДПиООС 05-15%'::text) OR ((num)::text ~~ 'ДПиООС 05-16%'::text) OR ((num)::text ~~ 'ДПиООС 05-17%'::text) OR ((num)::text ~~ 'ДПиООС 05-18%'::text) OR ((num)::text ~~ 'ДПиООС 05-18и%'::text) OR ((num)::text ~~ 'ДПиООС 05-19%'::text) OR ((num)::text ~~ 'ДПиООС 05-20%'::text) OR ((num)::text ~~ 'ДПиООС 05-21%'::text) OR ((num)::text ~~ 'ДПиООС 05-22%'::text) OR ((num)::text ~~ 'ДПиООС 05-69%'::text) OR ((num)::text ~~ 'ДПиООС 07-67%'::text) OR ((num)::text ~~ 'ДПиООС 07-68%'::text) OR ((num)::text ~~ 'ДПиООС 07-69%'::text)))
  • Rows Removed by Filter: 638838
  • Heap Fetches: 86479
11. 17,568.416 17,568.416 ↑ 1.0 1 549,013

Index Only Scan using d_del_id_idx on document d (cost=0.57..4.70 rows=1 width=8) (actual time=0.032..0.032 rows=1 loops=549,013)

  • Index Cond: ((deleted = 0) AND (id = dn.document_id))
  • Heap Fetches: 80294
12. 461,802.274 462,792.669 ↓ 3.0 9 548,983

Index Scan using "idx$$_96770001" on resolution r (cost=0.57..31.38 rows=3 width=18) (actual time=0.179..0.843 rows=9 loops=548,983)

  • Index Cond: ((is_project = 0) AND (document_id = d.id) AND (is_forward = 0))
  • Filter: ((is_csdr = 0) AND ((has_vzamen IS NULL) OR (NOT (SubPlan 7))))
  • Rows Removed by Filter: 0
13.          

SubPlan (forIndex Scan)

14. 990.395 990.395 ↑ 1.0 1 28,297

Index Only Scan using res_vzamen_isproj_isfwd_idx on resolution rv (cost=0.57..8.59 rows=1 width=0) (actual time=0.035..0.035 rows=1 loops=28,297)

  • Index Cond: ((vzamen = r.id) AND (is_project = 0) AND (is_forward = 0))
  • Heap Fetches: 5604
15. 196,176.288 196,176.288 ↓ 0.0 0 4,670,864

Index Scan using "idx$$_61550012" on resolution_to rt (cost=0.57..3.61 rows=1 width=24) (actual time=0.039..0.042 rows=0 loops=4,670,864)

  • Index Cond: ((another_control = 1) AND (resolution_id = r.id))
  • Filter: (primary_id IS NULL)
  • Rows Removed by Filter: 0
16. 2,069,388.072 2,173,302.080 ↓ 0.0 0 729,296

Index Scan using dc_resolution_to_id_idx on document_control dcc (cost=0.57..30.79 rows=1 width=50) (actual time=2.980..2.980 rows=0 loops=729,296)

  • Index Cond: (resolution_to_id = rt.id)
  • Filter: ((((stop_date IS NULL) AND (is_control = 1)) OR ((stop_date >= '2018-12-26 00:00:00'::timestamp without time zone) AND (stop_date <= '2018-12-20 00:00:00'::timestamp without time zone))) AND (((stop_date IS NULL) AND (is_control = 1) AND (CASE WHEN (has_prodl IS NULL) THEN exec_date ELSE (SubPlan 6) END <= '2018-12-20 00:00:00'::timestamp without time zone)) OR ((stop_date >= '2018-12-26 00:00:00'::timestamp without time zone) AND (stop_date <= '2018-12-20 00:00:00'::timestamp without time zone))))
  • Rows Removed by Filter: 1
17.          

SubPlan (forIndex Scan)

18. 0.000 103,914.008 ↑ 1.0 1 2,776

Unique (cost=27.37..27.38 rows=1 width=16) (actual time=37.432..37.433 rows=1 loops=2,776)

19.          

Initplan (forUnique)

20. 0.768 280.064 ↑ 1.0 1 256

Result (cost=8.59..8.60 rows=1 width=8) (actual time=1.094..1.094 rows=1 loops=256)

21.          

Initplan (forResult)

22. 0.256 279.296 ↑ 1.0 1 256

Limit (cost=0.57..8.59 rows=1 width=8) (actual time=1.091..1.091 rows=1 loops=256)

23. 279.040 279.040 ↑ 1.0 1 256

Index Only Scan Backward using exec_event_test3 on exec_event exec_event_1 (cost=0.57..8.59 rows=1 width=8) (actual time=1.090..1.090 rows=1 loops=256)

  • Index Cond: ((resolution_to_id = rt.id) AND ("TYPE" = 0) AND (cdate IS NOT NULL))
  • Heap Fetches: 16
24. 16.656 103,905.680 ↓ 4.0 4 2,776

Sort (cost=18.77..18.77 rows=1 width=16) (actual time=37.430..37.430 rows=4 loops=2,776)

  • Sort Key: (first_value(eep_1.exec_date) OVER (?))
  • Sort Method: quicksort Memory: 25kB
25. 19.432 103,889.024 ↓ 4.0 4 2,776

WindowAgg (cost=18.74..18.76 rows=1 width=16) (actual time=37.422..37.424 rows=4 loops=2,776)

26. 16.656 103,869.592 ↓ 4.0 4 2,776

Sort (cost=18.74..18.74 rows=1 width=16) (actual time=37.417..37.417 rows=4 loops=2,776)

  • Sort Key: eep_1.cdate DESC
  • Sort Method: quicksort Memory: 25kB
27. 14.186 103,852.936 ↓ 4.0 4 2,776

Nested Loop Anti Join (cost=1.14..18.73 rows=1 width=16) (actual time=12.130..37.411 rows=4 loops=2,776)

  • Join Filter: (ee2_1.cdate <= eep_1.cdate)
  • Rows Removed by Join Filter: 0
28. 101,931.944 101,931.944 ↓ 4.0 4 2,776

Index Scan using ee_res_to_id_idx on exec_event eep_1 (cost=0.57..10.11 rows=1 width=16) (actual time=10.990..36.719 rows=4 loops=2,776)

  • Index Cond: (resolution_to_id = rt.id)
  • Filter: (("TYPE" = 0) OR (("TYPE" = 1) AND (counters = 0)))
  • Rows Removed by Filter: 0
29. 10.798 1,906.806 ↓ 0.0 0 11,418

Materialize (cost=0.57..8.60 rows=1 width=8) (actual time=0.125..0.167 rows=0 loops=11,418)

30. 1,896.008 1,896.008 ↓ 0.0 0 2,776

Index Scan using exec_event_test on exec_event ee2_1 (cost=0.57..8.59 rows=1 width=8) (actual time=0.510..0.683 rows=0 loops=2,776)

  • Index Cond: ((resolution_to_id = rt.id) AND ("TYPE" = 1) AND (counters = 1))
  • Filter: (cdate >= $5)
31. 63.384 63.384 ↓ 0.0 0 7,923

Index Only Scan using usr_ugid_idx on usr ua (cost=0.42..2.18 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=7,923)

  • Index Cond: ((id = dcc.author) AND (group_id = 29))
  • Heap Fetches: 150
32. 39.520 39.520 ↑ 1.0 1 416

Index Only Scan using usr_id_name_freq_idx on usr ur (cost=0.42..2.19 rows=1 width=22) (actual time=0.094..0.095 rows=1 loops=416)

  • Index Cond: (id = dcc.executor)
  • Heap Fetches: 222
33. 3,828.864 3,828.864 ↑ 1.0 1 416

Index Scan using resolution_order_pk on resolution_order ro (cost=0.57..3.58 rows=1 width=16) (actual time=9.203..9.204 rows=1 loops=416)

  • Index Cond: (id = rt.resolution_order_id)
34. 0.832 0.832 ↓ 0.0 0 416

Index Scan using usr_ugid_idx on usr udc (cost=0.42..3.15 rows=1 width=29) (actual time=0.002..0.002 rows=0 loops=416)

  • Index Cond: (id = ro.curator)
35.          

SubPlan (forNested Loop Left Join)

36. 1.248 3,647.904 ↑ 1.0 1 416

Unique (cost=27.37..27.38 rows=1 width=16) (actual time=8.769..8.769 rows=1 loops=416)

37.          

Initplan (forUnique)

38. 0.000 0.000 ↓ 0.0 0

Result (cost=8.59..8.60 rows=1 width=8) (never executed)

39.          

Initplan (forResult)

40. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.57..8.59 rows=1 width=8) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Index Only Scan Backward using exec_event_test3 on exec_event (cost=0.57..8.59 rows=1 width=8) (never executed)

  • Index Cond: ((resolution_to_id = rt.id) AND ("TYPE" = 0) AND (cdate IS NOT NULL))
  • Heap Fetches: 0
42. 2.496 3,646.656 ↑ 1.0 1 416

Sort (cost=18.77..18.77 rows=1 width=16) (actual time=8.766..8.766 rows=1 loops=416)

  • Sort Key: (first_value(eep.exec_date) OVER (?))
  • Sort Method: quicksort Memory: 25kB
43. 2.496 3,644.160 ↑ 1.0 1 416

WindowAgg (cost=18.74..18.76 rows=1 width=16) (actual time=8.759..8.760 rows=1 loops=416)

44. 1.664 3,641.664 ↑ 1.0 1 416

Sort (cost=18.74..18.74 rows=1 width=16) (actual time=8.754..8.754 rows=1 loops=416)

  • Sort Key: eep.cdate DESC
  • Sort Method: quicksort Memory: 25kB
45. 1.827 3,640.000 ↑ 1.0 1 416

Nested Loop Anti Join (cost=1.14..18.73 rows=1 width=16) (actual time=8.683..8.750 rows=1 loops=416)

  • Join Filter: (ee2.cdate <= eep.cdate)
46. 3,450.720 3,450.720 ↑ 1.0 1 416

Index Scan using ee_res_to_id_idx on exec_event eep (cost=0.57..10.11 rows=1 width=16) (actual time=8.229..8.295 rows=1 loops=416)

  • Index Cond: (resolution_to_id = rt.id)
  • Filter: (("TYPE" = 0) OR (("TYPE" = 1) AND (counters = 0)))
47. 1.085 187.453 ↓ 0.0 0 427

Materialize (cost=0.57..8.60 rows=1 width=8) (actual time=0.439..0.439 rows=0 loops=427)

48. 186.368 186.368 ↓ 0.0 0 416

Index Scan using exec_event_test on exec_event ee2 (cost=0.57..8.59 rows=1 width=8) (actual time=0.448..0.448 rows=0 loops=416)

  • Index Cond: ((resolution_to_id = rt.id) AND ("TYPE" = 1) AND (counters = 1))
  • Filter: (cdate >= $2)