explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aSCV

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 44.240 ↓ 2.0 18 1

Append (cost=477.92..505.66 rows=9 width=69) (actual time=41.157..44.240 rows=18 loops=1)

2. 41.050 44.033 ↑ 5.0 1 1

Hash Join (cost=477.92..491.45 rows=5 width=69) (actual time=41.156..44.033 rows=1 loops=1)

  • Hash Cond: (user_groups_by_userid7.id = public_map_notifications1.groupid)
3.          

CTE user_groups_by_userid

4. 2.666 2.666 ↓ 2.4 1,269 1

CTE Scan on group_cte group_cte2_1 (cost=452.59..474.01 rows=536 width=64) (actual time=0.122..2.666 rows=1,269 loops=1)

  • Filter: underuserrg
5.          

CTE group_cte

6. 0.240 2.151 ↓ 1.2 1,269 1

Recursive Union (cost=14.60..452.59 rows=1,071 width=47) (actual time=0.121..2.151 rows=1,269 loops=1)

7. 0.044 0.123 ↑ 1.0 1 1

Index Scan using group_parentid_idx on "group" repo3_group3 (cost=14.60..16.82 rows=1 width=47) (actual time=0.120..0.123 rows=1 loops=1)

  • Index Cond: (parentid IS NULL)
8.          

SubPlan (for Index Scan)

9. 0.004 0.079 ↑ 1.0 17 1

Nested Loop (cost=0.55..14.28 rows=17 width=16) (actual time=0.066..0.079 rows=17 loops=1)

10. 0.041 0.041 ↑ 1.0 17 1

Index Scan using grouplink_parentid_idx on grouplink repo3_grouplink6 (cost=0.28..12.67 rows=17 width=32) (actual time=0.029..0.041 rows=17 loops=1)

  • Index Cond: (parentid = 'b7e864bc-de9f-4c03-9320-f5133e252f04'::uuid)
11. 0.001 0.034 ↑ 1.0 1 17

Materialize (cost=0.28..1.40 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=17)

12. 0.033 0.033 ↑ 1.0 1 1

Index Only Scan using user_pkey on "user" repo3_user5 (cost=0.28..1.39 rows=1 width=16) (actual time=0.033..0.033 rows=1 loops=1)

  • Index Cond: (id = 'b7e864bc-de9f-4c03-9320-f5133e252f04'::uuid)
  • Heap Fetches: 0
13. 0.447 1.788 ↓ 2.0 211 6

Nested Loop (cost=14.60..41.44 rows=107 width=47) (actual time=0.018..0.298 rows=211 loops=6)

14. 0.072 0.072 ↓ 21.2 212 6

WorkTable Scan on group_cte group_cte2 (cost=0.00..0.20 rows=10 width=17) (actual time=0.000..0.012 rows=212 loops=6)

15. 1.269 1.269 ↑ 11.0 1 1,269

Index Scan using group_parentid_idx on "group" repo3_group3_1 (cost=0.28..2.55 rows=11 width=46) (actual time=0.001..0.001 rows=1 loops=1,269)

  • Index Cond: (parentid = group_cte2.id)
16.          

SubPlan (for Nested Loop)

17. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.55..14.28 rows=17 width=16) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Index Scan using grouplink_parentid_idx on grouplink repo3_grouplink6_1 (cost=0.28..12.67 rows=17 width=32) (never executed)

  • Index Cond: (parentid = 'b7e864bc-de9f-4c03-9320-f5133e252f04'::uuid)
19. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.28..1.40 rows=1 width=16) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_pkey on "user" repo3_user5_1 (cost=0.28..1.39 rows=1 width=16) (never executed)

  • Index Cond: (id = 'b7e864bc-de9f-4c03-9320-f5133e252f04'::uuid)
  • Heap Fetches: 0
21. 2.912 2.912 ↓ 2.4 1,269 1

CTE Scan on user_groups_by_userid user_groups_by_userid7 (cost=0.00..10.72 rows=536 width=16) (actual time=0.123..2.912 rows=1,269 loops=1)

22. 0.004 0.071 ↑ 2.0 1 1

Hash (cost=3.89..3.89 rows=2 width=101) (actual time=0.071..0.071 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.067 0.067 ↑ 2.0 1 1

Index Scan using map_notifications_deviceid_captured_idx on map_notifications public_map_notifications1 (cost=0.56..3.89 rows=2 width=101) (actual time=0.066..0.067 rows=1 loops=1)

  • Index Cond: ((deviceid = 'D2C8FFE64D0896F1189F35FEAD3DB75A'::text) AND (captured >= '2020-07-28 09:29:02+00'::timestamp with time zone) AND (captured < '2020-07-28 09:34:02+00'::timestamp with time zone))
  • Filter: ((eventtype <> 8) AND (eventtype <> 1) AND ((eventtype < 47) OR (eventtype >= 56)))
24. 0.204 0.204 ↓ 4.2 17 1

Index Scan using ix_trackevents_deviceid_captured on trackevents public_trackevents8 (cost=0.56..14.13 rows=4 width=69) (actual time=0.102..0.204 rows=17 loops=1)

  • Index Cond: ((deviceid = 'D2C8FFE64D0896F1189F35FEAD3DB75A'::text) AND (captured >= '2020-07-28 09:29:02+00'::timestamp with time zone) AND (captured < '2020-07-28 09:34:02+00'::timestamp with time zone))
  • Filter: ((eventtype = 8) OR (eventtype = 1) OR ((eventtype >= 47) AND (eventtype < 56)))
  • Rows Removed by Filter: 2
Planning time : 3.058 ms
Execution time : 44.512 ms