explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jDArc

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 5.710 ↓ 2.0 18 1

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

2. 0.250 5.584 ↑ 5.0 1 1

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

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

CTE user_groups_by_userid

4. 4.707 4.707 ↓ 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.046..4.707 rows=1,269 loops=1)

  • Filter: underuserrg
5.          

CTE group_cte

6. 0.385 3.915 ↓ 1.2 1,269 1

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

7. 0.018 0.044 ↑ 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.044..0.044 rows=1 loops=1)

  • Index Cond: (parentid IS NULL)
8.          

SubPlan (for Index Scan)

9. 0.000 0.026 ↑ 1.0 17 1

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

10. 0.010 0.010 ↑ 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.006..0.010 rows=17 loops=1)

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

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

12. 0.007 0.007 ↑ 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.006..0.007 rows=1 loops=1)

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

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

14. 0.132 0.132 ↓ 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.022 rows=212 loops=6)

15. 2.538 2.538 ↑ 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.002..0.002 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. 5.314 5.314 ↓ 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.047..5.314 rows=1,269 loops=1)

22. 0.003 0.020 ↑ 2.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.017 0.017 ↑ 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.017..0.017 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.122 0.122 ↓ 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.057..0.122 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 : 0.798 ms
Execution time : 5.861 ms