explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jnf1

Settings
# exclusive inclusive rows x rows loops node
1. 46.067 686.761 ↓ 1.0 4,647 1

GroupAggregate (cost=3,991.08..5,325.48 rows=4,448 width=776) (actual time=635.450..686.761 rows=4,647 loops=1)

  • Group Key: ahgg.goal_id, ahrv.id, "user".id, ahg.question, ahg.created, ahg.user_id, ahg.public_goal, ahr.user_id, ahr.external_email
2. 633.444 637.079 ↓ 1.4 6,445 1

Sort (cost=3,991.08..4,002.20 rows=4,448 width=739) (actual time=634.679..637.079 rows=6,445 loops=1)

3. 3.635 3.635 ↓ 23.8 143 1

Sort Key: ahgg.goal_id, ahrv.id, "user".id, ahg.question, ahg.cAppend (cost=33.23..242.01 rows=6 width=438) (actual time=2.239..3.635 rows=143 loops=1)

4. 0.000 0.009 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=33.23..33.27 rows=1 width=430) (actual time=0.009..0.009 rows=0 loops=1)

5. 0.001 0.009 ↓ 0.0 0 1

GroupAggregate (cost=33.23..33.26 rows=1 width=534) (actual time=0.009..0.009 rows=0 loops=1)

  • Group Key: gc.id, g.id, "user".id
6. 0.002 0.008 ↓ 0.0 0 1

Sort (cost=33.23..33.24 rows=1 width=489) (actual time=0.008..0.008 rows=0 loops=1)

  • Sort Key: gc.id, "user".id
  • Sort Method: quicksort Memory: 25kB
7. 0.001 0.006 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.12..33.22 rows=1 width=489) (actual time=0.006..0.006 rows=0 loops=1)

  • Join Filter: (gs.goal_id = g.id)
8. 0.000 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.85..24.91 rows=1 width=473) (actual time=0.005..0.005 rows=0 loops=1)

9. 0.000 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.56..16.60 rows=1 width=441) (actual time=0.005..0.005 rows=0 loops=1)

10. 0.005 0.005 ↓ 0.0 0 1

Index Scan using goal_comment_goal_id_fk on goal_comment gc (cost=0.28..8.29 rows=1 width=353) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (goal_id = '3fc829a0-3dba-48f5-9a3b-1f1f5d4fd9f5'::uuid)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using user_pkey on "user" (cost=0.29..8.30 rows=1 width=88) (never executed)

  • Index Cond: (id = gc.user_id)
  • Filter: (status = ANY ('{invited,active}'::user_status[]))
12. 0.000 0.000 ↓ 0.0 0

Index Scan using goal_pkey on goal g (cost=0.29..8.30 rows=1 width=32) (never executed)

  • Index Cond: (id = '3fc829a0-3dba-48f5-9a3b-1f1f5d4fd9f5'::uuid)
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using goal_share_pkey on goal_share gs (cost=0.28..8.30 rows=1 width=32) (never executed)

  • Index Cond: (goal_id = '3fc829a0-3dba-48f5-9a3b-1f1f5d4fd9f5'::uuid)
  • Heap Fetches: 0
14. 0.000 0.383 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=137.79..137.86 rows=2 width=381) (actual time=0.383..0.383 rows=0 loops=1)

15. 0.001 0.383 ↓ 0.0 0 1

GroupAggregate (cost=137.79..137.84 rows=2 width=397) (actual time=0.383..0.383 rows=0 loops=1)

  • Group Key: gn.id, g_1.user_id
16. 0.002 0.382 ↓ 0.0 0 1

Sort (cost=137.79..137.80 rows=2 width=312) (actual time=0.382..0.382 rows=0 loops=1)

  • Sort Key: gn.id, g_1.user_id
  • Sort Method: quicksort Memory: 25kB
17. 0.000 0.380 ↓ 0.0 0 1

Nested Loop (cost=0.57..137.78 rows=2 width=312) (actual time=0.380..0.380 rows=0 loops=1)

18. 0.002 0.013 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..16.61 rows=1 width=48) (actual time=0.011..0.013 rows=1 loops=1)

  • Join Filter: (gs_1.goal_id = g_1.id)
19. 0.007 0.007 ↑ 1.0 1 1

Index Scan using goal_pkey on goal g_1 (cost=0.29..8.30 rows=1 width=32) (actual time=0.005..0.007 rows=1 loops=1)

  • Index Cond: (id = '3fc829a0-3dba-48f5-9a3b-1f1f5d4fd9f5'::uuid)
20. 0.004 0.004 ↓ 0.0 0 1

Index Only Scan using goal_share_pkey on goal_share gs_1 (cost=0.28..8.30 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (goal_id = '3fc829a0-3dba-48f5-9a3b-1f1f5d4fd9f5'::uuid)
  • Heap Fetches: 0
21. 0.367 0.367 ↓ 0.0 0 1

Seq Scan on goal_note gn (cost=0.00..121.15 rows=2 width=280) (actual time=0.366..0.367 rows=0 loops=1)

  • Filter: (goal_id = '3fc829a0-3dba-48f5-9a3b-1f1f5d4fd9f5'::uuid)
  • Rows Removed by Filter: 2360
22. 0.027 3.216 ↓ 71.5 143 1

Subquery Scan on *SELECT* 3 (cost=28.48..29.10 rows=2 width=632) (actual time=1.845..3.216 rows=143 loops=1)

23. 1.563 3.189 ↓ 71.5 143 1

GroupAggregate (cost=28.48..29.08 rows=2 width=776) (actual time=1.845..3.189 rows=143 loops=1)

  • Group Key: ahgg.goal_id, ahrv.id, user_1.id, ahg.question, ahg.created, ahg.user_id, ahg.public_goal, ahr.user_id, ahr.external_email
24. 0.125 1.626 ↓ 71.5 143 1

Sort (cost=28.48..28.49 rows=2 width=739) (actual time=1.600..1.626 rows=143 loops=1)

  • Sort Key: ahrv.id, user_1.id, ahg.question, ahg.created, ahg.user_id, ahg.public_goal, ahr.user_id, ahr.external_email
  • Sort Method: quicksort Memory: 107kB
25. 0.082 1.501 ↓ 71.5 143 1

Nested Loop Left Join (cost=1.99..28.47 rows=2 width=739) (actual time=0.054..1.501 rows=143 loops=1)

26. 0.056 1.133 ↓ 71.5 143 1

Nested Loop (cost=1.70..27.76 rows=2 width=723) (actual time=0.049..1.133 rows=143 loops=1)

27. 0.069 0.648 ↓ 71.5 143 1

Nested Loop Left Join (cost=1.42..26.69 rows=2 width=341) (actual time=0.043..0.648 rows=143 loops=1)

  • Filter: ((ahr.user_id IS NULL) = (user_1.id IS NULL))
  • Rows Removed by Filter: 19
28. 0.035 0.093 ↓ 54.0 162 1

Nested Loop (cost=1.13..25.45 rows=3 width=253) (actual time=0.027..0.093 rows=162 loops=1)

29. 0.002 0.020 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.84..24.91 rows=1 width=230) (actual time=0.019..0.020 rows=1 loops=1)

  • Join Filter: (gs_2.goal_id = ahgg.goal_id)
30. 0.003 0.015 ↑ 1.0 1 1

Nested Loop (cost=0.56..16.60 rows=1 width=214) (actual time=0.015..0.015 rows=1 loops=1)

31. 0.008 0.008 ↑ 1.0 1 1

Index Scan using ad_hoc_request_goal_goal_id_fk on ad_hoc_request_goal ahgg (cost=0.28..8.29 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (goal_id = '3fc829a0-3dba-48f5-9a3b-1f1f5d4fd9f5'::uuid)
32. 0.004 0.004 ↑ 1.0 1 1

Index Scan using ad_hoc_request_group_pkey on ad_hoc_request_group ahg (cost=0.29..8.30 rows=1 width=182) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (id = ahgg.ad_hoc_request_group_id)
33. 0.003 0.003 ↓ 0.0 0 1

Index Only Scan using goal_share_pkey on goal_share gs_2 (cost=0.28..8.30 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (goal_id = '3fc829a0-3dba-48f5-9a3b-1f1f5d4fd9f5'::uuid)
  • Heap Fetches: 0
34. 0.038 0.038 ↓ 54.0 162 1

Index Scan using ad_hoc_request_group_id_email_uk on ad_hoc_request ahr (cost=0.29..0.52 rows=3 width=71) (actual time=0.006..0.038 rows=162 loops=1)

  • Index Cond: (ad_hoc_request_group_id = ahg.id)
35. 0.486 0.486 ↑ 1.0 1 162

Index Scan using user_pkey on "user" user_1 (cost=0.29..0.40 rows=1 width=88) (actual time=0.003..0.003 rows=1 loops=162)

  • Index Cond: (id = ahr.user_id)
  • Filter: (status = ANY ('{invited,active}'::user_status[]))
  • Rows Removed by Filter: 0
36. 0.429 0.429 ↑ 1.0 1 143

Index Scan using ad_hoc_review_ad_hoc_request_id_key on ad_hoc_review ahrv (cost=0.29..0.54 rows=1 width=414) (actual time=0.003..0.003 rows=1 loops=143)

  • Index Cond: (ad_hoc_request_id = ahr.id)
37. 0.286 0.286 ↓ 0.0 0 143

Index Only Scan using ad_hoc_review_share_pkey on ad_hoc_review_share ahrs (cost=0.28..0.33 rows=2 width=32) (actual time=0.002..0.002 rows=0 loops=143)

  • Index Cond: (ad_hoc_review_id = ahrv.id)
  • Heap Fetches: 11
38. 0.000 0.007 ↓ 0.0 0 1

Nested Loop (cost=1.83..41.74 rows=1 width=170) (actual time=0.007..0.007 rows=0 loops=1)

  • Sort Method: external merge Disk: 5064kB
39. 0.000 0.007 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.54..18.42 rows=1 width=246) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: ((qr.user_id IS NULL) = (user_2.id IS NULL))
40. 0.001 0.007 ↓ 0.0 0 1

Nested Loop (cost=1.26..17.68 rows=2 width=158) (actual time=0.007..0.007 rows=0 loops=1)

41. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=0.84..17.02 rows=1 width=134) (actual time=0.006..0.006 rows=0 loops=1)

42. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=0.56..16.60 rows=1 width=123) (actual time=0.006..0.006 rows=0 loops=1)

43. 0.006 0.006 ↓ 0.0 0 1

Index Scan using questionnaire_request_goal_goal_id_fk on questionnaire_request_goal qgg (cost=0.28..8.29 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (goal_id = '3fc829a0-3dba-48f5-9a3b-1f1f5d4fd9f5'::uuid)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using questionnaire_request_group_pkey on questionnaire_request_group qg (cost=0.29..8.30 rows=1 width=91) (never executed)

  • Index Cond: (id = qgg.questionnaire_request_group_id)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using questionnaire_pkey on questionnaire q (cost=0.28..0.43 rows=1 width=43) (never executed)

  • Index Cond: (id = qg.questionnaire_id)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using questionnaire_request_group_id_email_uk on questionnaire_request qr (cost=0.41..0.63 rows=3 width=72) (never executed)

  • Index Cond: (questionnaire_request_group_id = qg.id)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using user_pkey on "user" user_2 (cost=0.29..0.36 rows=1 width=88) (never executed)

  • Index Cond: (id = qr.user_id)
  • Filter: (status = ANY ('{invited,active}'::user_status[]))
48. 0.000 0.000 ↓ 0.0 0

Index Scan using questionnaire_review_questionnaire_request_id_key on questionnaire_review qrv (cost=0.29..0.69 rows=1 width=32) (never executed)

  • Index Cond: (questionnaire_request_id = qr.id)
49. 3.023 612.556 ↓ 1.4 6,445 1

Hash Right Join (cost=3,495.68..3,721.56 rows=4,448 width=739) (actual time=609.236..612.556 rows=6,445 loops=1)

  • Hash Cond: (gs.goal_id = ahgg.goal_id)
50. 0.780 0.780 ↑ 1.0 3,784 1

Seq Scan on goal_share gs (cost=0.00..76.24 rows=3,824 width=32) (actual time=0.017..0.780 rows=3,784 loops=1)

51. 5.373 608.753 ↓ 2.2 5,232 1

Hash (cost=3,466.58..3,466.58 rows=2,328 width=723) (actual time=608.753..608.753 rows=5,232 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3906kB
52. 4.743 603.380 ↓ 2.2 5,232 1

Hash Join (cost=3,043.04..3,466.58 rows=2,328 width=723) (actual time=561.046..603.380 rows=5,232 loops=1)

  • Hash Cond: (ahg.id = ahgg.ad_hoc_request_group_id)
53. 38.068 38.068 ↑ 1.0 9,477 1

Seq Scan on ad_hoc_request_group ahg (cost=0.00..364.84 rows=9,584 width=182) (actual time=0.026..38.068 rows=9,477 loops=1)

54. 3.986 560.569 ↓ 2.3 5,232 1

Hash (cost=3,014.59..3,014.59 rows=2,276 width=589) (actual time=560.569..560.569 rows=5,232 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3121kB
55. 3.447 556.583 ↓ 2.3 5,232 1

Hash Right Join (cost=2,887.56..3,014.59 rows=2,276 width=589) (actual time=552.104..556.583 rows=5,232 loops=1)

  • Hash Cond: (ahrs.ad_hoc_review_id = ahrv.id)
56. 1.944 1.944 ↑ 1.0 5,164 1

Seq Scan on ad_hoc_review_share ahrs (cost=0.00..102.64 rows=5,164 width=32) (actual time=0.012..1.944 rows=5,164 loops=1)

57. 29.414 551.192 ↓ 2.0 4,647 1

Hash (cost=2,859.11..2,859.11 rows=2,276 width=573) (actual time=551.192..551.192 rows=4,647 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2742kB
58. 5.599 521.778 ↓ 2.0 4,647 1

Nested Loop (cost=904.15..2,859.11 rows=2,276 width=573) (actual time=17.673..521.778 rows=4,647 loops=1)

59. 5.022 150.583 ↓ 1.9 4,748 1

Hash Join (cost=903.86..1,537.65 rows=2,462 width=191) (actual time=17.093..150.583 rows=4,748 loops=1)

  • Hash Cond: (ahr.ad_hoc_request_group_id = ahgg.ad_hoc_request_group_id)
60. 11.130 136.390 ↓ 1.8 22,981 1

Hash Left Join (cost=844.98..1,406.67 rows=12,660 width=159) (actual time=7.902..136.390 rows=22,981 loops=1)

  • Hash Cond: (ahr.user_id = "user".id)
  • Filter: ((ahr.user_id IS NULL) = ("user".id IS NULL))
  • Rows Removed by Filter: 1503
61. 117.808 117.808 ↑ 1.0 24,484 1

Seq Scan on ad_hoc_request ahr (cost=0.00..495.21 rows=25,321 width=71) (actual time=0.427..117.808 rows=24,484 loops=1)

62. 3.588 7.452 ↑ 1.0 12,172 1

Hash (cost=692.83..692.83 rows=12,172 width=88) (actual time=7.452..7.452 rows=12,172 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1566kB
63. 3.864 3.864 ↑ 1.0 12,172 1

Seq Scan on "user" (cost=0.00..692.83 rows=12,172 width=88) (actual time=0.009..3.864 rows=12,172 loops=1)

  • Filter: (status = ANY ('{invited,active}'::user_status[]))
  • Rows Removed by Filter: 3574
64. 1.405 9.171 ↑ 1.0 1,818 1

Hash (cost=35.06..35.06 rows=1,906 width=32) (actual time=9.171..9.171 rows=1,818 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 130kB
65. 7.766 7.766 ↑ 1.0 1,818 1

Seq Scan on ad_hoc_request_goal ahgg (cost=0.00..35.06 rows=1,906 width=32) (actual time=0.893..7.766 rows=1,818 loops=1)

66. 365.596 365.596 ↑ 1.0 1 4,748

Index Scan using ad_hoc_review_ad_hoc_request_id_key on ad_hoc_review ahrv (cost=0.29..0.54 rows=1 width=414) (actual time=0.077..0.077 rows=1 loops=4,748)

  • Index Cond: (ad_hoc_request_id = ahr.id)
67.          

SubPlan (for Nested Loop)

68. 0.000 0.000 ↓ 0.0 0

HashSetOp Intersect (cost=4.30..22.62 rows=2 width=20) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Append (cost=4.30..22.61 rows=4 width=20) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1_1 (cost=4.30..11.63 rows=2 width=20) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on questionnaire_review_share qrs (cost=4.30..11.61 rows=2 width=16) (never executed)

  • Recheck Cond: (questionnaire_review_id = qrv.id)
72. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on questionnaire_review_share_pkey (cost=0.00..4.30 rows=2 width=0) (never executed)

  • Index Cond: (questionnaire_review_id = qrv.id)
73. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_1 (cost=4.30..10.96 rows=2 width=20) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on goal_share gs_3 (cost=4.30..10.94 rows=2 width=16) (never executed)

  • Recheck Cond: (goal_id = qgg.goal_id)
75. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on goal_share_pkey (cost=0.00..4.29 rows=2 width=0) (never executed)

  • Index Cond: (goal_id = qgg.goal_id)
Planning time : 19.632 ms
Execution time : 691.821 ms