explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3nnQ

Settings
# exclusive inclusive rows x rows loops node
1. 1.537 139,951.074 ↓ 7.1 487 1

Sort (cost=186,677,052.14..186,677,052.31 rows=69 width=292) (actual time=139,951.035..139,951.074 rows=487 loops=1)

  • Sort Key: ct.relname, ci.relname, (((information_schema._pg_expandarray(i.indkey))).n)
  • Sort Method: quicksort Memory: 276kB
2. 3.095 139,949.537 ↓ 7.1 487 1

Nested Loop (cost=3,205,334.97..186,677,050.03 rows=69 width=292) (actual time=137,371.353..139,949.537 rows=487 loops=1)

  • Join Filter: (ct.oid = a.attrelid)
3. 2,587.723 139,935.728 ↑ 4.2 487 1

Hash Join (cost=3,205,334.40..186,610,609.67 rows=2,056 width=232) (actual time=137,371.124..139,935.728 rows=487 loops=1)

  • Hash Cond: (i.indexrelid = ci.oid)
4. 3,648.146 108,939.209 ↑ 1,223.1 487 1

Hash Join (cost=1,263,906.10..184,226,091.68 rows=595,637 width=172) (actual time=83,617.106..108,939.209 rows=487 loops=1)

  • Hash Cond: (i.indrelid = ct.oid)
5. 94,292.539 94,292.539 ↑ 534.6 17,938,909 1

Seq Scan on pg_index i (cost=0.00..51,091,790.47 rows=9,590,141,000 width=41) (actual time=0.695..94,292.539 rows=17,938,909 loops=1)

  • Filter: indisprimary
  • Rows Removed by Filter: 15198727
6. 1.063 10,998.524 ↑ 1.3 1,597 1

Hash (cost=1,263,880.40..1,263,880.40 rows=2,056 width=132) (actual time=10,998.524..10,998.524 rows=1,597 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 288kB
7. 3,021.307 10,997.461 ↑ 1.3 1,597 1

Hash Join (cost=8.31..1,263,880.40 rows=2,056 width=132) (actual time=9,374.584..10,997.461 rows=1,597 loops=1)

  • Hash Cond: (ct.relnamespace = n.oid)
8. 7,976.107 7,976.107 ↓ 1.1 35,293,581 1

Seq Scan on pg_class ct (cost=0.00..1,139,715.47 rows=33,102,947 width=72) (actual time=0.015..7,976.107 rows=35,293,581 loops=1)

9. 0.006 0.047 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=68) (actual time=0.047..0.047 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.041 0.041 ↑ 1.0 1 1

Index Scan using pg_namespace_nspname_index on pg_namespace n (cost=0.29..8.30 rows=1 width=68) (actual time=0.041..0.041 rows=1 loops=1)

  • Index Cond: (nspname = '20000'::name)
11. 16,349.063 28,408.796 ↓ 1.1 35,293,581 1

Hash (cost=1,139,715.47..1,139,715.47 rows=33,102,947 width=68) (actual time=28,408.796..28,408.796 rows=35,293,581 loops=1)

  • Buckets: 65536 Batches: 1024 Memory Usage: 3848kB
12. 12,059.733 12,059.733 ↓ 1.1 35,293,581 1

Seq Scan on pg_class ci (cost=0.00..1,139,715.47 rows=33,102,947 width=68) (actual time=0.013..12,059.733 rows=35,293,581 loops=1)

13. 10.714 10.714 ↑ 16.0 1 487

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.57..32.12 rows=16 width=70) (actual time=0.021..0.022 rows=1 loops=487)

  • Index Cond: ((attrelid = i.indrelid) AND (attnum = ((information_schema._pg_expandarray(i.indkey))).x))