explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xpk2

Settings
# exclusive inclusive rows x rows loops node
1. 1,017.477 1,767.568 ↓ 1.4 1,852,028 1

Nested Loop Left Join (cost=20.94..92,852.42 rows=1,310,670 width=105) (actual time=0.226..1,767.568 rows=1,852,028 loops=1)

  • Join Filter: (zac.zone_id = z.id)
  • Rows Removed by Join Filter: 3638172
2. 0.143 0.643 ↓ 1.1 42 1

Nested Loop Left Join (cost=19.45..31.82 rows=40 width=82) (actual time=0.165..0.643 rows=42 loops=1)

  • Join Filter: (col.zone_id = z.id)
  • Rows Removed by Join Filter: 168
3. 0.013 0.260 ↓ 1.2 5 1

Nested Loop Left Join (cost=19.45..27.92 rows=4 width=57) (actual time=0.154..0.260 rows=5 loops=1)

  • Join Filter: (o.id = z.organization_id)
4. 0.025 0.237 ↓ 1.2 5 1

Nested Loop (cost=19.45..26.85 rows=4 width=43) (actual time=0.149..0.237 rows=5 loops=1)

  • Join Filter: (z_1.id = z.id)
  • Rows Removed by Join Filter: 20
5. 0.052 0.052 ↓ 1.2 5 1

Index Scan using idx_zone_name on zone z (cost=0.13..6.19 rows=4 width=43) (actual time=0.006..0.052 rows=5 loops=1)

6. 0.012 0.160 ↓ 1.2 5 5

Materialize (cost=19.32..20.43 rows=4 width=4) (actual time=0.028..0.032 rows=5 loops=5)

7. 0.023 0.148 ↓ 1.2 5 1

Hash Join (cost=19.32..20.41 rows=4 width=4) (actual time=0.137..0.148 rows=5 loops=1)

  • Hash Cond: (z_1.organization_id = so.organization_id)
8. 0.006 0.006 ↓ 1.2 5 1

Seq Scan on zone z_1 (cost=0.00..1.04 rows=4 width=8) (actual time=0.004..0.006 rows=5 loops=1)

9. 0.003 0.119 ↑ 3.0 1 1

Hash (cost=19.28..19.28 rows=3 width=4) (actual time=0.119..0.119 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.002 0.116 ↑ 3.0 1 1

Subquery Scan on so (cost=19.22..19.28 rows=3 width=4) (actual time=0.115..0.116 rows=1 loops=1)

11. 0.004 0.114 ↑ 3.0 1 1

HashAggregate (cost=19.22..19.25 rows=3 width=9) (actual time=0.114..0.114 rows=1 loops=1)

  • Group Key: u.id, u.superuser, o_1.id
12. 0.001 0.110 ↑ 3.0 1 1

Append (cost=6.46..19.20 rows=3 width=9) (actual time=0.067..0.110 rows=1 loops=1)

13. 0.001 0.068 ↑ 1.0 1 1

Group (cost=6.46..6.47 rows=1 width=9) (actual time=0.067..0.068 rows=1 loops=1)

  • Group Key: u.id, o_1.id
14. 0.067 0.067 ↑ 1.0 1 1

Sort (cost=6.46..6.47 rows=1 width=9) (actual time=0.066..0.067 rows=1 loops=1)

  • Sort Key: u.id, o_1.id
  • Sort Method: quicksort Memory: 25kB
  • -> Nested Loop (cost=4.17..6.45 rows=1 width=9) (actual time=0.050..0.051 rows=1 loops
  • -> Hash Join (cost=4.17..5.43 rows=1 width=5) (actual time=0.046..0.047 rows=1 l
  • Hash Cond: (u.id = sess.user_id)
  • -> Seq Scan on "user" u (cost=0.00..1.19 rows=16 width=5) (actual time=0.0
  • Filter: superuser
  • Rows Removed by Filter: 3
  • -> Hash (cost=4.16..4.16 rows=1 width=4) (actual time=0.019..0.019 rows=1
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Index Scan using pk_session on session sess (cost=0.14..4.16 rows
  • Index Cond: (id = 'b185ab80-449e-4cd2-aff1-b94eab4c1872'::text)
  • -> Seq Scan on organization o_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.
15. 0.000 0.027 ↓ 0.0 0 1

Nested Loop (cost=4.44..6.28 rows=1 width=9) (actual time=0.027..0.027 rows=0 loops=1)

16. 0.027 0.027 ↓ 0.0 0 1

Nested Loop (cost=4.31..5.82 rows=1 width=9) (actual time=0.027..0.027 rows=0 loops=1)

  • Join Filter: (sess_1.user_id = ur.user_id)
  • -> Hash Join (cost=4.17..5.44 rows=1 width=9) (actual time=0.021..0.021 rows=1 loops=1
  • Hash Cond: (u_1.id = sess_1.user_id)
  • -> Seq Scan on "user" u_1 (cost=0.00..1.19 rows=19 width=5) (actual time=0.001..
  • -> Hash (cost=4.16..4.16 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Index Scan using pk_session on session sess_1 (cost=0.14..4.16 rows=1 w
  • Index Cond: (id = 'b185ab80-449e-4cd2-aff1-b94eab4c1872'::text)
17. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_user_role on user_role ur (cost=0.14..0.37 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (user_id = u_1.id)
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_role_id on role r (cost=0.13..0.45 rows=1 width=8) (never executed)

  • Index Cond: (id = ur.role_id)
19. 0.000 0.014 ↓ 0.0 0 1

Group (cost=6.40..6.41 rows=1 width=9) (actual time=0.014..0.014 rows=0 loops=1)

  • Group Key: u_2.id, o_2.id
20. 0.014 0.014 ↓ 0.0 0 1

Sort (cost=6.40..6.41 rows=1 width=9) (actual time=0.014..0.014 rows=0 loops=1)

  • Sort Key: u_2.id, o_2.id
  • Sort Method: quicksort Memory: 25kB
  • -> Nested Loop (cost=0.14..6.39 rows=1 width=9) (actual time=0.008..0.008 rows=0 loops
  • -> Nested Loop (cost=0.14..5.38 rows=1 width=5) (actual time=0.008..0.008 rows=0
  • Join Filter: (u_2.id = sess_2.user_id)
  • Rows Removed by Join Filter: 2
  • -> Index Scan using pk_session on session sess_2 (cost=0.14..4.16 rows=1 w
  • Index Cond: (id = 'b185ab80-449e-4cd2-aff1-b94eab4c1872'::text)
  • -> Seq Scan on "user" u_2 (cost=0.00..1.19 rows=2 width=5) (actual time=0.
  • Filter: hidden
  • Rows Removed by Filter: 17
  • -> Seq Scan on organization o_2 (cost=0.00..1.01 rows=1 width=4) (never executed
21. 0.007 0.010 ↑ 1.0 1 5

Materialize (cost=0.00..1.01 rows=1 width=18) (actual time=0.001..0.002 rows=1 loops=5)

22. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on organization o (cost=0.00..1.01 rows=1 width=18) (actual time=0.002..0.003 rows=1 loops=1)

23. 0.148 0.240 ↓ 1.1 42 5

Materialize (cost=0.00..1.60 rows=40 width=29) (actual time=0.002..0.048 rows=42 loops=5)

24. 0.092 0.092 ↓ 1.1 42 1

Seq Scan on collector col (cost=0.00..1.40 rows=40 width=29) (actual time=0.005..0.092 rows=42 loops=1)

25. 555.506 749.448 ↑ 1.0 130,719 42

Materialize (cost=1.50..14,508.07 rows=131,067 width=19) (actual time=0.001..17.844 rows=130,719 loops=42)

26. 42.434 193.942 ↑ 1.0 130,719 1

Hash Left Join (cost=1.50..13,852.73 rows=131,067 width=19) (actual time=0.033..193.942 rows=130,719 loops=1)

  • Hash Cond: (zac.attributetype_id = att.id)
27. 36.252 151.490 ↑ 1.0 130,719 1

Append (cost=0.00..12,049.07 rows=131,067 width=15) (actual time=0.006..151.490 rows=130,719 loops=1)

28. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on attribute_cidr zac (cost=0.00..0.00 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (collector_id IS NULL)
29. 0.009 0.009 ↑ 1.0 20 1

Seq Scan on attribute_cidr zac_1 (cost=0.00..1.38 rows=20 width=15) (actual time=0.004..0.009 rows=20 loops=1)

  • Filter: (collector_id IS NULL)
  • Rows Removed by Filter: 18
30. 97.910 97.910 ↑ 1.0 62,778 1

Seq Scan on attribute_cidr zac_2 (cost=0.00..10,276.75 rows=63,177 width=15) (actual time=0.004..97.910 rows=62,778 loops=1)

  • Filter: (collector_id IS NULL)
  • Rows Removed by Filter: 500797
31. 10.128 10.128 ↓ 1.0 38,612 1

Seq Scan on attribute_cidr zac_3 (cost=0.00..1,089.31 rows=38,536 width=15) (actual time=0.031..10.128 rows=38,612 loops=1)

  • Filter: (collector_id IS NULL)
  • Rows Removed by Filter: 22819
32. 7.177 7.177 ↑ 1.0 29,309 1

Seq Scan on attribute_cidr zac_4 (cost=0.00..667.13 rows=29,328 width=15) (actual time=0.011..7.177 rows=29,309 loops=1)

  • Filter: (collector_id IS NULL)
  • Rows Removed by Filter: 8604
33. 0.003 0.013 ↓ 0.0 0 1

Bitmap Heap Scan on attribute_cidr zac_5 (cost=8.98..14.50 rows=5 width=40) (actual time=0.013..0.013 rows=0 loops=1)

  • Recheck Cond: (collector_id IS NULL)
34. 0.010 0.010 ↓ 0.0 0 1

Bitmap Index Scan on uk_attrcidr (cost=0.00..8.97 rows=5 width=0) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (collector_id IS NULL)
35. 0.008 0.018 ↓ 1.0 23 1

Hash (cost=1.22..1.22 rows=22 width=12) (actual time=0.018..0.018 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.010 0.010 ↓ 1.0 23 1

Seq Scan on attributetype att (cost=0.00..1.22 rows=22 width=12) (actual time=0.005..0.010 rows=23 loops=1)