explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X1UE

Settings
# exclusive inclusive rows x rows loops node
1. 0.114 51.633 ↓ 7.0 7 1

Result (cost=116.11..116.14 rows=1 width=212) (actual time=51.547..51.633 rows=7 loops=1)

2. 0.222 51.519 ↓ 7.0 7 1

Sort (cost=116.11..116.11 rows=1 width=180) (actual time=51.518..51.519 rows=7 loops=1)

  • Sort Key: c.relname, i.relname
  • Sort Method: quicksort Memory: 27kB
3. 0.198 51.297 ↓ 7.0 7 1

Nested Loop (cost=64.08..116.10 rows=1 width=180) (actual time=15.803..51.297 rows=7 loops=1)

  • Join Filter: (c.oid = c_2.oid)
  • Rows Removed by Join Filter: 28
4. 0.054 50.140 ↓ 7.0 7 1

Nested Loop (cost=20.79..70.88 rows=1 width=144) (actual time=14.806..50.140 rows=7 loops=1)

  • Join Filter: ((i.relname = i_1.relname) AND (n.nspname = n_1.nspname))
  • Rows Removed by Join Filter: 224
5. 0.030 11.586 ↓ 11.0 11 1

Nested Loop (cost=0.69..25.21 rows=1 width=204) (actual time=10.920..11.586 rows=11 loops=1)

6. 0.007 11.390 ↓ 83.0 83 1

Nested Loop (cost=0.42..24.79 rows=1 width=144) (actual time=10.893..11.390 rows=83 loops=1)

7. 0.000 11.217 ↓ 83.0 83 1

Nested Loop (cost=0.14..24.30 rows=1 width=72) (actual time=10.865..11.217 rows=83 loops=1)

8. 10.965 10.965 ↓ 136.5 273 1

Seq Scan on pg_class i (cost=0.00..19.55 rows=2 width=68) (actual time=10.844..10.965 rows=273 loops=1)

  • Filter: (pg_stat_get_numscans(oid) = 0)
  • Rows Removed by Filter: 97
9. 0.273 0.273 ↓ 0.0 0 273

Index Scan using pg_index_indexrelid_index on pg_index x (cost=0.14..2.36 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=273)

  • Index Cond: (indexrelid = i.oid)
10. 0.166 0.166 ↑ 1.0 1 83

Index Scan using pg_class_oid_index on pg_class c (cost=0.27..0.48 rows=1 width=72) (actual time=0.002..0.002 rows=1 loops=83)

  • Index Cond: (oid = x.indrelid)
  • Filter: (relkind = ANY ('{r,t,m}'::"char"[]))
11. 0.166 0.166 ↓ 0.0 0 83

Index Scan using pg_namespace_oid_index on pg_namespace n (cost=0.27..0.42 rows=1 width=68) (actual time=0.002..0.002 rows=0 loops=83)

  • Index Cond: (oid = c.relnamespace)
  • Filter: ((nspname <> ALL ('{pg_catalog,information_schema}'::name[])) AND (nspname !~ '^pg_toast'::text))
  • Rows Removed by Filter: 1
12. 0.176 38.500 ↓ 1.8 21 11

Nested Loop (cost=20.10..45.48 rows=12 width=132) (actual time=0.622..3.500 rows=21 loops=11)

13. 0.055 38.093 ↓ 1.8 21 11

Nested Loop (cost=19.82..40.04 rows=12 width=72) (actual time=0.620..3.463 rows=21 loops=11)

14. 0.499 0.858 ↓ 4.3 130 11

Hash Join (cost=19.55..25.47 rows=30 width=8) (actual time=0.019..0.078 rows=130 loops=11)

  • Hash Cond: (x_1.indrelid = c_1.oid)
15. 0.209 0.209 ↑ 1.0 152 11

Seq Scan on pg_index x_1 (cost=0.00..5.52 rows=152 width=8) (actual time=0.001..0.019 rows=152 loops=11)

16. 0.018 0.150 ↑ 1.0 74 1

Hash (cost=18.62..18.62 rows=74 width=8) (actual time=0.150..0.150 rows=74 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 0.132 0.132 ↑ 1.0 74 1

Seq Scan on pg_class c_1 (cost=0.00..18.62 rows=74 width=8) (actual time=0.007..0.132 rows=74 loops=1)

  • Filter: (relkind = ANY ('{r,m}'::"char"[]))
  • Rows Removed by Filter: 296
18. 37.180 37.180 ↓ 0.0 0 1,430

Index Scan using pg_class_oid_index on pg_class i_1 (cost=0.27..0.49 rows=1 width=72) (actual time=0.026..0.026 rows=0 loops=1,430)

  • Index Cond: (oid = x_1.indexrelid)
  • Filter: ((relkind = 'i'::"char") AND (pg_get_indexdef(oid) !~* 'unique'::text))
  • Rows Removed by Filter: 1
19. 0.231 0.231 ↑ 1.0 1 231

Index Scan using pg_namespace_oid_index on pg_namespace n_1 (cost=0.27..0.45 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=231)

  • Index Cond: (oid = c_1.relnamespace)
20. 0.040 0.959 ↑ 9.6 5 7

HashAggregate (cost=43.29..44.13 rows=48 width=284) (actual time=0.134..0.137 rows=5 loops=7)

  • Group Key: c_2.oid, n_2.nspname, c_2.relname
21. 0.027 0.919 ↑ 3.4 14 1

Hash Right Join (cost=36.64..42.93 rows=48 width=132) (actual time=0.912..0.919 rows=14 loops=1)

  • Hash Cond: (i_2.indrelid = c_2.oid)
22. 0.015 0.015 ↑ 1.0 152 1

Seq Scan on pg_index i_2 (cost=0.00..5.52 rows=152 width=4) (actual time=0.003..0.015 rows=152 loops=1)

23. 0.018 0.877 ↑ 9.6 5 1

Hash (cost=36.04..36.04 rows=48 width=132) (actual time=0.877..0.877 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.028 0.859 ↑ 9.6 5 1

Hash Join (cost=16.70..36.04 rows=48 width=132) (actual time=0.759..0.859 rows=5 loops=1)

  • Hash Cond: (c_2.relnamespace = n_2.oid)
25. 0.102 0.102 ↑ 1.0 96 1

Seq Scan on pg_class c_2 (cost=0.00..19.09 rows=96 width=72) (actual time=0.010..0.102 rows=96 loops=1)

  • Filter: (relkind = ANY ('{r,t,m}'::"char"[]))
  • Rows Removed by Filter: 274
26. 0.053 0.729 ↑ 1.0 228 1

Hash (cost=13.84..13.84 rows=229 width=68) (actual time=0.729..0.729 rows=228 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
27. 0.676 0.676 ↑ 1.0 228 1

Seq Scan on pg_namespace n_2 (cost=0.00..13.84 rows=229 width=68) (actual time=0.012..0.676 rows=228 loops=1)

  • Filter: ((nspname <> ALL ('{pg_catalog,information_schema}'::name[])) AND (nspname !~ '^pg_toast'::text))
  • Rows Removed by Filter: 228
Planning time : 3.462 ms
Execution time : 53.484 ms