explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wmqy

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=658,945.90..1,810,039,751.84 rows=9,875 width=62) (actual rows= loops=)

  • Group Key: ug.short_name
2.          

CTE oiv

3. 0.000 0.000 ↓ 0.0

Index Only Scan using ug_idname_idx on user_group ug1 (cost=0.55..236.81 rows=51 width=49) (actual rows= loops=)

  • Index Cond: (id = ANY ('{5,2488,34,24,20,5972,61,35,54,32,59,64,16,1663529,68,53,66,38,6,29,231,13859,2734,35923603,766,624,711,23,21,734,33,719,48,8,63,230,39,4216532,56,45,58,31,60,2115,649,65,918,204,46,3724544,25}'::bigint[]))
4.          

CTE oiv_ampm

5. 0.000 0.000 ↓ 0.0

Index Only Scan using ug_idname_idx on user_group ug1_1 (cost=0.55..241.38 rows=52 width=49) (actual rows= loops=)

  • Index Cond: (id = ANY ('{5,2488,34,24,20,5972,61,35,54,32,59,64,16,1663529,68,53,66,38,6,29,231,13859,2734,35923603,766,624,711,23,21,734,33,719,48,8,63,230,39,4216532,56,45,58,31,60,2115,649,65,918,204,46,3724544,25,7}'::bigint[]))
6. 0.000 0.000 ↓ 0.0

Sort (cost=658,467.71..661,254.69 rows=1,114,791 width=38) (actual rows= loops=)

  • Sort Key: ug.short_name
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=22,277.74..546,496.20 rows=1,114,791 width=38) (actual rows= loops=)

  • Hash Cond: (dn.org_id = ug.id)
8. 0.000 0.000 ↓ 0.0

Index Only Scan using document_n588_test1 on document_n dn (cost=0.70..508,890.78 rows=1,114,791 width=24) (actual rows= loops=)

  • Index Cond: ((org_id = ANY ('{15,44,11,12,10,43,13,247519,42,265521,14}'::bigint[])) AND (rdate >= '2019-01-01 00:00:00'::timestamp without time zone) AND (rdate <= '2019-06-07 23:59:59.99999'::timestamp without time zone))
9. 0.000 0.000 ↓ 0.0

Hash (cost=18,198.13..18,198.13 rows=326,313 width=22) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on user_group ug (cost=0.00..18,198.13 rows=326,313 width=22) (actual rows= loops=)

11.          

SubPlan (forGroupAggregate)

12. 0.000 0.000 ↓ 0.0

Index Scan using document_id_hp_idx on document d_1 (cost=0.57..8.59 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (id = dn.document_id)
  • Filter: (document_kind = 128)
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=446.78..456.81 rows=1 width=0) (actual rows= loops=)

  • Hash Cond: (dr_4.recipient = ur_8.id)
14. 0.000 0.000 ↓ 0.0

Index Only Scan using dr_docid_recip_idx on document_r dr_4 (cost=0.57..10.23 rows=95 width=8) (actual rows= loops=)

  • Index Cond: (document_id = dn.document_id)
15. 0.000 0.000 ↓ 0.0

Hash (cost=439.94..439.94 rows=502 width=8) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..439.94 rows=502 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

CTE Scan on oiv_ampm (cost=0.00..1.04 rows=52 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_727b0001" on usr ur_8 (cost=0.42..8.34 rows=10 width=16) (actual rows= loops=)

  • Index Cond: (group_id = oiv_ampm.id)
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..103,940.06 rows=94,143 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..439.94 rows=502 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

CTE Scan on oiv_ampm oiv_ampm_1 (cost=0.00..1.04 rows=52 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_727b0001" on usr ur_9 (cost=0.42..8.34 rows=10 width=16) (actual rows= loops=)

  • Index Cond: (group_id = oiv_ampm_1.id)
23. 0.000 0.000 ↓ 0.0

Index Scan using dr_recipient_idx on document_r dr_5 (cost=0.57..135.84 rows=7,034 width=16) (actual rows= loops=)

  • Index Cond: (recipient = ur_9.id)
24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..469.45 rows=1 width=0) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Index Only Scan using dr_docid_recip_idx on document_r dr_2 (cost=0.57..10.23 rows=95 width=8) (actual rows= loops=)

  • Index Cond: (document_id = dn.document_id)
26. 0.000 0.000 ↓ 0.0

Index Only Scan using usr_ugid_idx on usr ur_5 (cost=0.42..4.82 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((id = dr_2.recipient) AND (group_id = 7))
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,161.27..3,293,707.58 rows=532,261 width=8) (actual rows= loops=)

  • Hash Cond: (dr_3.recipient = ur_6.id)
28. 0.000 0.000 ↓ 0.0

Seq Scan on document_r dr_3 (cost=0.00..2,830,071.21 rows=121,907,521 width=16) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=1,125.78..1,125.78 rows=2,839 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_6d740003" on usr ur_6 (cost=0.42..1,125.78 rows=2,839 width=8) (actual rows= loops=)

  • Index Cond: (group_id = 7)
31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.99..37.31 rows=1 width=0) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.57..36.86 rows=1 width=8) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Merge Join (cost=1.00..31.45 rows=1 width=8) (actual rows= loops=)

  • Merge Cond: (r_3.author = ua_7.id)
34. 0.000 0.000 ↓ 0.0

Index Scan using res_docid_author_test_3_idx on resolution r_3 (cost=0.57..22.80 rows=6 width=16) (actual rows= loops=)

  • Index Cond: (document_id = dn.document_id)
35. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_727b0001" on usr ua_7 (cost=0.42..8.60 rows=10 width=8) (actual rows= loops=)

  • Index Cond: (group_id = dn.org_id)
36. 0.000 0.000 ↓ 0.0

Index Only Scan using rt_resid_usrid_idx on resolution_to rt_3 (cost=0.57..5.34 rows=6 width=16) (actual rows= loops=)

  • Index Cond: (resolution_id = r_3.id)
37. 0.000 0.000 ↓ 0.0

Index Only Scan using usr_ugid_idx on usr ur_7 (cost=0.42..0.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((id = rt_3.user_id) AND (group_id = 7))
38. 0.000 0.000 ↓ 0.0

Hash Join (cost=437.93..447.96 rows=1 width=0) (actual rows= loops=)

  • Hash Cond: (dr.recipient = ur_2.id)
39. 0.000 0.000 ↓ 0.0

Index Only Scan using dr_docid_recip_idx on document_r dr (cost=0.57..10.23 rows=95 width=8) (actual rows= loops=)

  • Index Cond: (document_id = dn.document_id)
40. 0.000 0.000 ↓ 0.0

Hash (cost=431.22..431.22 rows=492 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..431.22 rows=492 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

CTE Scan on oiv oiv_3 (cost=0.00..1.02 rows=51 width=8) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_727b0001" on usr ur_2 (cost=0.42..8.34 rows=10 width=16) (actual rows= loops=)

  • Index Cond: (group_id = oiv_3.id)
44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..101,869.59 rows=92,332 width=8) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..431.22 rows=492 width=8) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

CTE Scan on oiv oiv_4 (cost=0.00..1.02 rows=51 width=8) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_727b0001" on usr ur_3 (cost=0.42..8.34 rows=10 width=16) (actual rows= loops=)

  • Index Cond: (group_id = oiv_4.id)
48. 0.000 0.000 ↓ 0.0

Index Scan using dr_recipient_idx on document_r dr_1 (cost=0.57..135.84 rows=7,034 width=16) (actual rows= loops=)

  • Index Cond: (recipient = ur_3.id)
49. 0.000 0.000 ↓ 0.0

Hash Join (cost=37.32..38.54 rows=1 width=0) (actual rows= loops=)

  • Hash Cond: (oiv_5.id = ur_4.group_id)
50. 0.000 0.000 ↓ 0.0

CTE Scan on oiv oiv_5 (cost=0.00..1.02 rows=51 width=8) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Hash (cost=37.31..37.31 rows=1 width=8) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.99..37.31 rows=1 width=8) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.57..36.86 rows=1 width=8) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Merge Join (cost=1.00..31.45 rows=1 width=8) (actual rows= loops=)

  • Merge Cond: (r_2.author = ua_6.id)
55. 0.000 0.000 ↓ 0.0

Index Scan using res_docid_author_test_3_idx on resolution r_2 (cost=0.57..22.80 rows=6 width=16) (actual rows= loops=)

  • Index Cond: (document_id = dn.document_id)
56. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_727b0001" on usr ua_6 (cost=0.42..8.60 rows=10 width=8) (actual rows= loops=)

  • Index Cond: (group_id = dn.org_id)
57. 0.000 0.000 ↓ 0.0

Index Only Scan using rt_resid_usrid_idx on resolution_to rt_2 (cost=0.57..5.34 rows=6 width=16) (actual rows= loops=)

  • Index Cond: (resolution_id = r_2.id)
58. 0.000 0.000 ↓ 0.0

Index Only Scan using usr_ugid_idx on usr ur_4 (cost=0.42..0.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = rt_2.user_id)
59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..17.04 rows=1 width=0) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Only Scan using da_docid_author_idx on document_a da_2 (cost=0.57..8.59 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (document_id = dn.document_id)
61. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_727b0001" on usr ua_3 (cost=0.42..8.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((group_id = 7) AND (id = da_2.author))
62. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,161.27..2,165,493.66 rows=391,986 width=8) (actual rows= loops=)

  • Hash Cond: (da_3.author = ua_4.id)
63. 0.000 0.000 ↓ 0.0

Seq Scan on document_a da_3 (cost=0.00..1,823,740.39 rows=89,779,239 width=16) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash (cost=1,125.78..1,125.78 rows=2,839 width=8) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_6d740003" on usr ua_4 (cost=0.42..1,125.78 rows=2,839 width=8) (actual rows= loops=)

  • Index Cond: (group_id = 7)
66. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.99..59.39 rows=1 width=0) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.57..58.94 rows=1 width=8) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.00..53.53 rows=1 width=8) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Index Scan using resolution_did_id on resolution r_1 (cost=0.57..22.80 rows=6 width=16) (actual rows= loops=)

  • Index Cond: (document_id = dn.document_id)
70. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_727b0001" on usr ua_5 (cost=0.42..5.11 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((group_id = 7) AND (id = r_1.author))
71. 0.000 0.000 ↓ 0.0

Index Only Scan using rt_resid_usrid_idx on resolution_to rt_1 (cost=0.57..5.34 rows=6 width=16) (actual rows= loops=)

  • Index Cond: (resolution_id = r_1.id)
72. 0.000 0.000 ↓ 0.0

Index Only Scan using usr_ugid_idx on usr ur_1 (cost=0.42..0.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((id = rt_1.user_id) AND (group_id = dn.org_id))
73. 0.000 0.000 ↓ 0.0

Hash Join (cost=17.05..18.27 rows=1 width=0) (actual rows= loops=)

  • Hash Cond: (oiv.id = ua.group_id)
74. 0.000 0.000 ↓ 0.0

CTE Scan on oiv (cost=0.00..1.02 rows=51 width=8) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Hash (cost=17.04..17.04 rows=1 width=8) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..17.04 rows=1 width=8) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Index Only Scan using da_docid_author_idx on document_a da (cost=0.57..8.59 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (document_id = dn.document_id)
78. 0.000 0.000 ↓ 0.0

Index Only Scan using usr_ugid_idx on usr ua (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = da.author)
79. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..74,120.26 rows=67,998 width=8) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..431.22 rows=492 width=8) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

CTE Scan on oiv oiv_1 (cost=0.00..1.02 rows=51 width=8) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Index Only Scan using "idx$$_727b0001" on usr ua_1 (cost=0.42..8.34 rows=10 width=16) (actual rows= loops=)

  • Index Cond: (group_id = oiv_1.id)
83. 0.000 0.000 ↓ 0.0

Index Scan using quest_sx_idx3ecde9986d29fa9890 on document_a da_1 (cost=0.57..98.24 rows=5,153 width=16) (actual rows= loops=)

  • Index Cond: (author = ua_1.id)
84. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.65..61.07 rows=1 width=0) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.23..60.61 rows=1 width=8) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.65..55.21 rows=1 width=8) (actual rows= loops=)

  • Hash Cond: (ua_2.group_id = oiv_2.id)
87. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.00..53.52 rows=6 width=16) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Index Scan using resolution_did_id on resolution r (cost=0.57..22.80 rows=6 width=16) (actual rows= loops=)

  • Index Cond: (document_id = dn.document_id)
89. 0.000 0.000 ↓ 0.0

Index Only Scan using usr_ugid_idx on usr ua_2 (cost=0.42..5.11 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = r.author)
90. 0.000 0.000 ↓ 0.0

Hash (cost=1.02..1.02 rows=51 width=8) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

CTE Scan on oiv oiv_2 (cost=0.00..1.02 rows=51 width=8) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Index Only Scan using rt_resid_usrid_idx on resolution_to rt (cost=0.57..5.34 rows=6 width=16) (actual rows= loops=)

  • Index Cond: (resolution_id = r.id)
93. 0.000 0.000 ↓ 0.0

Index Only Scan using usr_ugid_idx on usr ur (cost=0.42..0.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((id = rt.user_id) AND (group_id = dn.org_id))
94. 0.000 0.000 ↓ 0.0

Index Scan using document_id_hp_idx on document d (cost=0.57..8.59 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (id = dn.document_id)
  • Filter: (r_org_id = dn.org_id)