explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bMAN

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 5.118 ↓ 0.0 0 1

GroupAggregate (cost=1,205.64..1,205.70 rows=1 width=200) (actual time=5.118..5.118 rows=0 loops=1)

  • Group Key: ind_atts.nspname, ind_atts.index_name, ind_atts.reltuples, ind_atts.relpages, ind_atts.relam, ind_atts.indrelid, ind_atts.index_oid, ((current_setting('block_size'::text))::numeric), 8
2.          

CTE btree_index_atts

3. 0.255 1.190 ↑ 85.8 268 1

Nested Loop (cost=28.47..168.01 rows=23,000 width=218) (actual time=0.401..1.190 rows=268 loops=1)

4. 0.050 0.569 ↓ 8.0 183 1

Hash Join (cost=28.19..39.77 rows=23 width=175) (actual time=0.370..0.569 rows=183 loops=1)

  • Hash Cond: (indexclass.relnamespace = pg_namespace.oid)
5. 0.141 0.499 ↓ 9.5 286 1

Hash Join (cost=26.99..38.22 rows=30 width=115) (actual time=0.342..0.499 rows=286 loops=1)

  • Hash Cond: (pg_index.indexrelid = indexclass.oid)
6. 0.042 0.042 ↑ 1.0 286 1

Seq Scan on pg_index (cost=0.00..9.86 rows=286 width=35) (actual time=0.004..0.042 rows=286 loops=1)

7. 0.070 0.316 ↓ 4.3 286 1

Hash (cost=26.17..26.17 rows=66 width=84) (actual time=0.316..0.316 rows=286 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
8. 0.078 0.246 ↓ 4.3 286 1

Hash Join (cost=1.09..26.17 rows=66 width=84) (actual time=0.041..0.246 rows=286 loops=1)

  • Hash Cond: (indexclass.relam = pg_am.oid)
9. 0.158 0.158 ↑ 1.0 395 1

Seq Scan on pg_class indexclass (cost=0.00..22.94 rows=395 width=84) (actual time=0.012..0.158 rows=395 loops=1)

  • Filter: (relpages > 0)
  • Rows Removed by Filter: 240
10. 0.002 0.010 ↑ 1.0 1 1

Hash (cost=1.07..1.07 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on pg_am (cost=0.00..1.07 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Filter: (amname = 'btree'::name)
  • Rows Removed by Filter: 5
12. 0.004 0.020 ↑ 1.0 7 1

Hash (cost=1.11..1.11 rows=7 width=68) (actual time=0.020..0.020 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.016 0.016 ↑ 1.0 7 1

Seq Scan on pg_namespace (cost=0.00..1.11 rows=7 width=68) (actual time=0.013..0.016 rows=7 loops=1)

  • Filter: (nspname <> ALL ('{pg_catalog,information_schema}'::name[]))
  • Rows Removed by Filter: 2
14. 0.366 0.366 ↑ 1.0 1 183

Index Scan using pg_class_oid_index on pg_class tableclass (cost=0.28..0.56 rows=1 width=68) (actual time=0.001..0.002 rows=1 loops=183)

  • Index Cond: (oid = pg_index.indrelid)
15. 0.009 5.116 ↓ 0.0 0 1

Sort (cost=1,037.64..1,037.64 rows=1 width=192) (actual time=5.116..5.116 rows=0 loops=1)

  • Sort Key: ind_atts.nspname, ind_atts.index_name, ind_atts.reltuples, ind_atts.relpages, ind_atts.relam, ind_atts.indrelid, ind_atts.index_oid
  • Sort Method: quicksort Memory: 25kB
16. 0.000 5.107 ↓ 0.0 0 1

Nested Loop (cost=243.45..1,037.63 rows=1 width=192) (actual time=5.107..5.107 rows=0 loops=1)

  • Join Filter: (((pg_stats.tablename = ind_atts.tablename) AND ((pg_stats.attname)::text = pg_get_indexdef(pg_attribute.attrelid, (pg_attribute.attnum)::integer, true))) OR ((pg_stats.tablename = ind_atts.index_name) AND (pg_stats.attname = pg_attribute.attname)))
17. 0.042 5.107 ↓ 0.0 0 1

Hash Join (cost=243.16..796.31 rows=690 width=354) (actual time=5.107..5.107 rows=0 loops=1)

  • Hash Cond: (ind_atts.nspname = pg_stats.schemaname)
18. 1.375 1.375 ↑ 85.8 268 1

CTE Scan on btree_index_atts ind_atts (cost=0.00..460.00 rows=23,000 width=218) (actual time=0.403..1.375 rows=268 loops=1)

19. 0.199 3.690 ↓ 65.0 390 1

Hash (cost=243.09..243.09 rows=6 width=200) (actual time=3.690..3.690 rows=390 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 97kB
20. 0.055 3.491 ↓ 65.0 390 1

Subquery Scan on pg_stats (cost=83.40..243.09 rows=6 width=200) (actual time=0.769..3.491 rows=390 loops=1)

21. 0.108 3.436 ↓ 65.0 390 1

Nested Loop Left Join (cost=83.40..243.03 rows=6 width=401) (actual time=0.768..3.436 rows=390 loops=1)

22. 1.021 2.938 ↓ 65.0 390 1

Hash Join (cost=83.27..241.94 rows=6 width=140) (actual time=0.761..2.938 rows=390 loops=1)

  • Hash Cond: ((a.attrelid = c.oid) AND (a.attnum = s.staattnum))
  • Join Filter: has_column_privilege(c.oid, a.attnum, 'select'::text)
  • Rows Removed by Join Filter: 162
23. 1.184 1.184 ↑ 1.0 4,425 1

Seq Scan on pg_attribute a (cost=0.00..125.25 rows=4,425 width=70) (actual time=0.005..1.184 rows=4,425 loops=1)

  • Filter: (NOT attisdropped)
24. 0.135 0.733 ↑ 1.0 552 1

Hash (cost=74.99..74.99 rows=552 width=86) (actual time=0.733..0.733 rows=552 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 73kB
25. 0.200 0.598 ↑ 1.0 552 1

Hash Join (cost=30.88..74.99 rows=552 width=86) (actual time=0.328..0.598 rows=552 loops=1)

  • Hash Cond: (s.starelid = c.oid)
26. 0.088 0.088 ↑ 1.0 552 1

Seq Scan on pg_statistic s (cost=0.00..36.52 rows=552 width=14) (actual time=0.003..0.088 rows=552 loops=1)

27. 0.135 0.310 ↑ 1.0 635 1

Hash (cost=22.94..22.94 rows=635 width=72) (actual time=0.310..0.310 rows=635 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 73kB
28. 0.175 0.175 ↑ 1.0 635 1

Seq Scan on pg_class c (cost=0.00..22.94 rows=635 width=72) (actual time=0.014..0.175 rows=635 loops=1)

  • Filter: ((NOT relrowsecurity) OR (NOT row_security_active(oid)))
29. 0.390 0.390 ↑ 1.0 1 390

Index Scan using pg_namespace_oid_index on pg_namespace n (cost=0.14..0.17 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=390)

  • Index Cond: (oid = c.relnamespace)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using pg_attribute_relid_attnum_index on pg_attribute (cost=0.28..0.32 rows=1 width=70) (never executed)

  • Index Cond: ((attrelid = ind_atts.indexrelid) AND (attnum = ind_atts.attnum) AND (attnum > 0))
Planning time : 1.217 ms
Execution time : 5.314 ms