explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XR8l

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 20.855 ↓ 2.0 2 1

GroupAggregate (cost=1,205.64..1,205.70 rows=1 width=200) (actual time=20.853..20.855 rows=2 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.621 1.724 ↑ 85.8 268 1

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

4. 0.082 0.737 ↓ 8.0 183 1

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

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

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

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

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

7. 0.080 0.370 ↓ 4.3 286 1

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

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

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

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

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

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

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

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

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

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

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

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

Seq Scan on pg_namespace (cost=0.00..1.11 rows=7 width=68) (actual time=0.012..0.017 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.002..0.002 rows=1 loops=183)

  • Index Cond: (oid = pg_index.indrelid)
15. 0.082 20.818 ↓ 2.0 2 1

Sort (cost=1,037.64..1,037.64 rows=1 width=192) (actual time=20.818..20.818 rows=2 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. 5.732 20.736 ↓ 2.0 2 1

Nested Loop (cost=243.45..1,037.63 rows=1 width=192) (actual time=5.833..20.736 rows=2 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)))
  • Rows Removed by Join Filter: 4,221
17. 1.538 8.049 ↓ 10.1 6,955 1

Hash Join (cost=243.16..796.31 rows=690 width=354) (actual time=5.605..8.049 rows=6,955 loops=1)

  • Hash Cond: (ind_atts.nspname = pg_stats.schemaname)
18. 1.962 1.962 ↑ 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.545..1.962 rows=268 loops=1)

19. 0.239 4.549 ↓ 92.0 552 1

Hash (cost=243.09..243.09 rows=6 width=200) (actual time=4.549..4.549 rows=552 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 134kB
20. 0.085 4.310 ↓ 92.0 552 1

Subquery Scan on pg_stats (cost=83.40..243.09 rows=6 width=200) (actual time=0.932..4.310 rows=552 loops=1)

21. 0.198 4.225 ↓ 92.0 552 1

Nested Loop Left Join (cost=83.40..243.03 rows=6 width=401) (actual time=0.931..4.225 rows=552 loops=1)

22. 1.134 3.475 ↓ 92.0 552 1

Hash Join (cost=83.27..241.94 rows=6 width=140) (actual time=0.918..3.475 rows=552 loops=1)

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

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

  • Filter: (NOT attisdropped)
24. 0.148 0.834 ↑ 1.0 552 1

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

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

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

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

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

27. 0.131 0.303 ↑ 1.0 635 1

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

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

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

  • Filter: ((NOT relrowsecurity) OR (NOT row_security_active(oid)))
29. 0.552 0.552 ↑ 1.0 1 552

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=552)

  • Index Cond: (oid = c.relnamespace)
30. 6.955 6.955 ↑ 1.0 1 6,955

Index Scan using pg_attribute_relid_attnum_index on pg_attribute (cost=0.28..0.32 rows=1 width=70) (actual time=0.001..0.001 rows=1 loops=6,955)

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