explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qoL9

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 3.538 ↓ 3.6 18 1

Append (cost=489.98..507.19 rows=5 width=69) (actual time=3.320..3.538 rows=18 loops=1)

2. 0.118 3.481 ↑ 1.0 1 1

Hash Join (cost=489.98..493.02 rows=1 width=69) (actual time=3.320..3.481 rows=1 loops=1)

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

CTE user_groups_by_userid

4. 2.486 2.486 ↓ 2.4 1,269 1

CTE Scan on group_cte group_cte4_1 (cost=452.59..474.01 rows=536 width=64) (actual time=0.048..2.486 rows=1,269 loops=1)

  • Filter: underuserrg
5.          

CTE group_cte

6. 0.249 2.072 ↓ 1.2 1,269 1

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

7. 0.024 0.047 ↑ 1.0 1 1

Index Scan using group_parentid_idx on "group" repo3_group5 (cost=14.60..16.82 rows=1 width=47) (actual time=0.046..0.047 rows=1 loops=1)

  • Index Cond: (parentid IS NULL)
8.          

SubPlan (for Index Scan)

9. 0.013 0.023 ↑ 1.0 17 1

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

10. 0.010 0.010 ↑ 1.0 17 1

Index Scan using grouplink_parentid_idx on grouplink repo3_grouplink8 (cost=0.28..12.67 rows=17 width=32) (actual time=0.005..0.010 rows=17 loops=1)

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

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

12. 0.005 0.005 ↑ 1.0 1 1

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

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

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

14. 0.084 0.084 ↓ 21.2 212 6

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

15. 1.269 1.269 ↑ 11.0 1 1,269

Index Scan using group_parentid_idx on "group" repo3_group5_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_cte4.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_grouplink8_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_user7_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. 0.447 3.323 ↓ 6.3 1,269 1

HashAggregate (cost=12.06..14.06 rows=200 width=16) (actual time=3.203..3.323 rows=1,269 loops=1)

  • Group Key: user_groups_by_userid3.id
22. 2.876 2.876 ↓ 2.4 1,269 1

CTE Scan on user_groups_by_userid user_groups_by_userid3 (cost=0.00..10.72 rows=536 width=16) (actual time=0.049..2.876 rows=1,269 loops=1)

23. 0.003 0.040 ↑ 2.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.037 0.037 ↑ 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.036..0.037 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)))
25. 0.056 0.056 ↓ 4.2 17 1

Index Scan using ix_trackevents_deviceid_captured on trackevents public_trackevents9 (cost=0.56..14.13 rows=4 width=69) (actual time=0.024..0.056 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 : 1.085 ms
Execution time : 3.714 ms