explain.depesz.com

A tool for finding a real cause for slow queries.

Result: jUb

options
# exclusive inclusive rows x rows loops node
1. 71.981 36769.806 ↓ 15524.3 46573 1
→

Unique (cost=57993.76..57993.85 rows=3 width=391) (actual time=36605.850..36769.806 rows=46573 loops=1)

2. 9793.280 36697.825 ↓ 16634.0 49902 1
→

Sort (cost=57993.76..57993.77 rows=3 width=391) (actual time=36605.850..36697.825 rows=49902 loops=1)

  • Sort Key: reg.name, workareas.name, learninggroups.grade, workareas.name, ((((((p.surname)::text || ' '::text) || (p.name)::text) || ' '::text) || (p.patronymic)::text)), ((CASE WHEN (ok.id IS NOT NULL) THEN ((ok.name)::text || ' '::text) ELSE ''::text END || (ek.name)::text)), e.name, el.name, pf.name, ert.name, (array_to_string(array_agg(DISTINCT (sub.name)::text), ', '::text))
  • Sort Method: external sort Disk: 25664kB
3. 575.843 26904.545 ↓ 16634.0 49902 1
→

GroupAggregate (cost=57993.52..57993.74 rows=3 width=391) (actual time=25672.881..26904.545 rows=49902 loops=1)

4. 13972.134 26328.702 ↓ 20039.7 60119 1
→

Sort (cost=57993.52..57993.53 rows=3 width=391) (actual time=25672.881..26328.702 rows=60119 loops=1)

  • Sort Key: reg.name, workareas.name, learninggroups.grade, p.surname, p.name, p.patronymic, workareas.name, ek.name, e.name, el.name, ert.name, e.id, ok.name, ok.id, pf.name
  • Sort Method: external merge Disk: 25416kB
5. 127.965 12356.568 ↓ 20039.7 60119 1
→

Hash Join (cost=140.13..57993.49 rows=3 width=391) (actual time=747.793..12356.568 rows=60119 loops=1)

  • Hash Cond: ((e.kind)::integer = (ek.id)::integer)
6. 171.950 12228.603 ↓ 20039.7 60119 1
→

Hash Join (cost=138.95..57992.27 rows=3 width=363) (actual time=747.793..12228.603 rows=60119 loops=1)

  • Hash Cond: ((e.level)::integer = (el.id)::integer)
7. 143.689 12056.653 ↓ 20039.7 60119 1
→

Nested Loop Left Join (cost=137.73..57991.01 rows=3 width=335) (actual time=747.793..12056.653 rows=60119 loops=1)

8. 171.892 11792.726 ↓ 20039.7 60119 1
→

Nested Loop Left Join (cost=137.73..57990.15 rows=3 width=287) (actual time=747.793..11792.726 rows=60119 loops=1)

9. 115.967 11380.838 ↓ 19999.7 59999 1
→

Hash Join (cost=137.73..57968.07 rows=3 width=283) (actual time=747.793..11380.838 rows=59999 loops=1)

  • Hash Cond: ((ep.participationform)::integer = (pf.id)::integer)
10. 147.962 11264.871 ↓ 9999.8 59999 1
→

Hash Join (cost=136.59..57966.88 rows=6 width=255) (actual time=747.793..11264.871 rows=59999 loops=1)

  • Hash Cond: ((ep.resulttypes)::integer = (ert.id)::integer)
11. 131.962 11116.909 ↓ 4628.0 60164 1
→

Hash Left Join (cost=135.43..57965.62 rows=13 width=227) (actual time=747.793..11116.909 rows=60164 loops=1)

  • Hash Cond: ((o.olympkind)::integer = (ok.id)::integer)
12. 187.411 10984.947 ↓ 4628.0 60164 1
→

Nested Loop Left Join (cost=134.39..57964.39 rows=13 width=195) (actual time=747.793..10984.947 rows=60164 loops=1)

13. 288.742 10617.044 ↓ 4628.0 60164 1
→

Nested Loop (cost=134.39..57960.50 rows=13 width=191) (actual time=747.793..10617.044 rows=60164 loops=1)

14. 390.463 9965.224 ↓ 4654.8 60513 1
→

Nested Loop (cost=134.39..57956.31 rows=13 width=128) (actual time=747.793..9965.224 rows=60513 loops=1)

15. 653.754 8633.597 ↓ 21390.1 235291 1
→

Nested Loop Left Join (cost=134.39..57952.35 rows=11 width=124) (actual time=731.797..8633.597 rows=235291 loops=1)

16. 867.601 7273.970 ↓ 21390.1 235291 1
→

Nested Loop (cost=134.39..57949.00 rows=11 width=128) (actual time=731.797..7273.970 rows=235291 loops=1)

17. 1003.720 4994.623 ↓ 21390.1 235291 1
→

Hash Join (cost=134.39..57945.02 rows=11 width=75) (actual time=731.797..4994.623 rows=235291 loops=1)

  • Hash Cond: ((employees.department)::integer = (learninggroups.id)::integer)
18. 1827.500 3263.105 ↑ 1.2 591764 1
→

Merge Join (cost=0.00..55102.02 rows=722267 width=12) (actual time=0.000..3263.105 rows=591764 loops=1)

  • Merge Cond: ((students.id)::integer = (employees.id)::integer)
19. 671.819 671.819 ↑ 1.2 591764 1
→

Index Scan using "fki_students_FK1" on students (cost=0.00..20905.55 rows=722267 width=4) (actual time=0.000..671.819 rows=591764 loops=1)

20. 763.786 763.786 ↑ 1.1 646703 1
→

Index Scan using primary_employees on employees (cost=0.00..23360.30 rows=723132 width=16) (actual time=0.000..763.786 rows=646703 loops=1)

21. 35.990 727.798 ↓ 15724.0 15724 1
→

Hash (cost=134.38..134.38 rows=1 width=79) (actual time=727.798..727.798 rows=15724 loops=1)

22. 61.071 691.808 ↓ 15724.0 15724 1
→

Nested Loop (cost=0.00..134.38 rows=1 width=79) (actual time=0.000..691.808 rows=15724 loops=1)

23. 69.069 567.841 ↓ 15724.0 15724 1
→

Nested Loop (cost=0.00..133.99 rows=1 width=95) (actual time=0.000..567.841 rows=15724 loops=1)

24. 78.635 435.876 ↓ 15724.0 15724 1
→

Nested Loop (cost=0.00..133.68 rows=1 width=91) (actual time=0.000..435.876 rows=15724 loops=1)

25. 87.516 287.917 ↓ 23108.0 23108 1
→

Nested Loop (cost=0.00..133.37 rows=1 width=83) (actual time=0.000..287.917 rows=23108 loops=1)

26. 51.958 107.969 ↓ 23108.0 23108 1
→

Nested Loop (cost=0.00..133.07 rows=1 width=79) (actual time=0.000..107.969 rows=23108 loops=1)

27. 11.997 11.997 ↓ 746.0 746 1
→

Nested Loop (cost=0.00..127.58 rows=1 width=50) (actual time=0.000..11.997 rows=746 loops=1)

  • -> Index Scan using "fki_eduinstitutions_FK1" on eduinstitutions (cost=0.00..0.27 rows=1 width:
28. 0.000 0.000 ↓ 13.3 771 1
→

Nested Loop (cost=0.00..111.29 rows=58 width=46) (actual time=0.000..0.000 rows=771 loops=1)

  • Index Cond: ((eduinstitutions.id)::integer = (workareas.id)::integer)
29. 0.000 0.000 ↓ 4.0 28 1
→

Index Scan using "fki_workareas_FK3" on workareas reg (cost=0.00..13.12 rows=7 width=25) (actual time=0.000..0.000 rows=28 loops=1)

  • Index Cond: ((padre)::integer = 4974)
30. 0.000 0.000 ↓ 3.5 28 28
→

Index Scan using "fki_workareas_FK3" on workareas (cost=0.00..13.92 rows=8 width=29) (actual time=0.000..0.000 rows=28 loops=28)

  • Index Cond: ((workareas.padre)::integer = (reg.id)::integer)
31. 44.014 44.014 ↓ 3.9 31 746
→

Index Scan using "fki_workareas_FK3" on workareas (cost=0.00..5.39 rows=8 width=29) (actual time=0.016..0.059 rows=31 loops=746)

  • Index Cond: ((workareas.padre)::integer = (workareas.id)::integer)
32. 92.432 92.432 ↑ 1.0 1 23108
→

Index Scan using "fki_departments_FK1" on departments (cost=0.00..0.28 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=23108)

  • Index Cond: ((departments.id)::integer = (workareas.id)::integer)
33. 69.324 69.324 ↑ 1.0 1 23108
→

Index Scan using primary_learninggroups on learninggroups (cost=0.00..0.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=23108)

  • Index Cond: ((learninggroups.id)::integer = (workareas.id)::integer)
34. 62.896 62.896 ↑ 1.0 1 15724
→

Index Scan using "fki_schools_FK1" on schools (cost=0.00..0.30 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=15724)

  • Index Cond: ((schools.id)::integer = (workareas.id)::integer)
35. 62.896 62.896 ↑ 1.0 1 15724
→

Index Scan using "fki_organizations_FK1" on organizations (cost=0.00..0.37 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=15724)

  • Index Cond: ((organizations.id)::integer = (schools.id)::integer)
36. 1411.746 1411.746 ↑ 1.0 1 235291
→

Index Scan using primary_persons on persons p (cost=0.00..0.35 rows=1 width=53) (actual time=0.005..0.006 rows=1 loops=235291)

  • Index Cond: ((p.id)::integer = (employees.person)::integer)
37. 705.873 705.873 ↓ 0.0 0 235291
→

Index Scan using aspectinactive_pkey on aspectinactive stai (cost=0.00..0.29 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=235291)

  • Index Cond: ((stai.id)::integer = (employees.aspect)::integer)
38. 941.164 941.164 ↓ 0.0 0 235291
→

Index Scan using "fki_eventparticipants_FK273" on eventparticipants ep (cost=0.00..0.32 rows=3 width=16) (actual time=0.003..0.004 rows=0 loops=235291)

  • Index Cond: ((ep.person)::integer = (p.id)::integer)
39. 363.078 363.078 ↑ 1.0 1 60513
→

Index Scan using primary_events on events e (cost=0.00..0.31 rows=1 width=67) (actual time=0.005..0.006 rows=1 loops=60513)

  • Index Cond: ((e.id)::integer = (ep.event)::integer)
40. 180.492 180.492 ↓ 0.0 0 60164
→

Index Scan using "fki_olympiads_FK413" on olympiads o (cost=0.00..0.29 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=60164)

  • Index Cond: ((o.id)::integer = (e.id)::integer)
41. 0.000 0.000 ↑ 1.0 2 1
→

Hash (cost=1.02..1.02 rows=2 width=36) (actual time=0.000..0.000 rows=2 loops=1)

42. 0.000 0.000 ↑ 1.0 2 1
→

Seq Scan on olympiadkinds ok (cost=0.00..1.02 rows=2 width=36) (actual time=0.000..0.000 rows=2 loops=1)

43. 0.000 0.000 ↑ 1.0 7 1
→

Hash (cost=1.07..1.07 rows=7 width=36) (actual time=0.000..0.000 rows=7 loops=1)

44. 0.000 0.000 ↑ 1.0 7 1
→

Seq Scan on eventresulttypes ert (cost=0.00..1.07 rows=7 width=36) (actual time=0.000..0.000 rows=7 loops=1)

45. 0.000 0.000 ↑ 1.0 6 1
→

Hash (cost=1.06..1.06 rows=6 width=36) (actual time=0.000..0.000 rows=6 loops=1)

46. 0.000 0.000 ↑ 1.0 6 1
→

Seq Scan on participationforms pf (cost=0.00..1.06 rows=6 width=36) (actual time=0.000..0.000 rows=6 loops=1)

47. 239.996 239.996 ↓ 0.0 0 59999
→

Index Scan using "fki_event2subject_FK267" on event2subject e2s (cost=0.00..7.35 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=59999)

  • Index Cond: ((e2s.event)::integer = (e.id)::integer)
48. 120.238 120.238 ↓ 0.0 0 60119
→

Index Scan using primary_subjects on subjects sub (cost=0.00..0.27 rows=1 width=56) (actual time=0.001..0.002 rows=0 loops=60119)

  • Index Cond: ((sub.id)::integer = (e2s.subject)::integer)
49. 0.000 0.000 ↑ 1.0 10 1
→

Hash (cost=1.10..1.10 rows=10 width=36) (actual time=0.000..0.000 rows=10 loops=1)

50. 0.000 0.000 ↑ 1.0 10 1
→

Seq Scan on eventlevels el (cost=0.00..1.10 rows=10 width=36) (actual time=0.000..0.000 rows=10 loops=1)

51. 0.000 0.000 ↑ 1.0 8 1
→

Hash (cost=1.08..1.08 rows=8 width=36) (actual time=0.000..0.000 rows=8 loops=1)

52. 0.000 0.000 ↑ 1.0 8 1
→

Seq Scan on eventkinds ek (cost=0.00..1.08 rows=8 width=36) (actual time=0.000..0.000 rows=8 loops=1)