explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P9AN

Settings
# exclusive inclusive rows x rows loops node
1. 4,029.221 58,088.291 ↓ 1.0 23,552 1

Sort (cost=471,902.50..471,961.17 rows=23,468 width=149) (actual time=58,045.787..58,088.291 rows=23,552 loops=1)

  • Sort Key: (CASE WHEN (pg_proc.proname = ANY ('{array_recv,oidvectorrecv}'::name[])) THEN 3 WHEN (a.typtype = 'r'::"char") THEN 2 WHEN (a.typtype = 'd'::"char") THEN 1 ELSE 0 END)
  • Sort Method: quicksort Memory: 7025kB
2. 16.662 54,059.070 ↓ 1.0 23,552 1

Hash Left Join (cost=379,032.06..470,198.91 rows=23,468 width=149) (actual time=40,856.885..54,059.070 rows=23,552 loops=1)

  • Hash Cond: (a.oid = pg_range.rngtypid)
3. 15.370 54,040.844 ↓ 1.0 23,552 1

Hash Join (cost=379,030.93..469,784.15 rows=23,468 width=209) (actual time=40,854.711..54,040.844 rows=23,552 loops=1)

  • Hash Cond: (a.typnamespace = ns.oid)
4. 45.087 53,998.365 ↓ 1.0 23,552 1

Hash Join (cost=378,932.20..469,623.15 rows=23,468 width=149) (actual time=40,827.247..53,998.365 rows=23,552 loops=1)

  • Hash Cond: ((a.typreceive)::oid = pg_proc.oid)
  • Join Filter: ((a.typtype = ANY ('{b,r,e,d}'::"char"[])) OR ((a.typtype = 'c'::"char") AND (cls.relkind = 'c'::"char")) OR ((pg_proc.proname = 'array_recv'::name) AND ((b.typtype = ANY ('{b,r,e,d}'::"char"[])) OR ((b.typtype = 'p'::"char") AND (b.typname = ANY ('{record,void}'::name[]))) OR ((b.typtype = 'c'::"char") AND (elemcls.relkind = 'c'::"char")))) OR ((a.typtype = 'p'::"char") AND (a.typname = ANY ('{record,void}'::name[]))))
  • Rows Removed by Join Filter: 45928
5. 80.858 52,244.117 ↓ 1.0 69,532 1

Hash Left Join (cost=377,454.46..467,962.88 rows=69,504 width=156) (actual time=39,113.337..52,244.117 rows=69,532 loops=1)

  • Hash Cond: (b.typrelid = elemcls.oid)
6. 107.819 39,006.335 ↓ 1.0 69,532 1

Hash Left Join (cost=234,142.15..324,468.11 rows=69,504 width=159) (actual time=25,951.795..39,006.335 rows=69,532 loops=1)

  • Hash Cond: (a.typelem = b.oid)
7. 164.969 25,270.534 ↓ 1.0 69,532 1

Hash Left Join (cost=143,312.31..233,455.81 rows=69,504 width=90) (actual time=12,318.698..25,270.534 rows=69,532 loops=1)

  • Hash Cond: (a.typrelid = cls.oid)
8. 12,811.951 12,811.951 ↓ 1.0 69,532 1

Seq Scan on pg_type a (cost=0.00..89,961.04 rows=69,504 width=89) (actual time=0.776..12,811.951 rows=69,532 loops=1)

9. 370.310 12,293.614 ↑ 1.2 106,361 1

Hash (cost=141,698.25..141,698.25 rows=129,125 width=5) (actual time=12,293.598..12,293.614 rows=106,361 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4868kB
10. 11,923.304 11,923.304 ↑ 1.2 106,361 1

Seq Scan on pg_class cls (cost=0.00..141,698.25 rows=129,125 width=5) (actual time=0.663..11,923.304 rows=106,361 loops=1)

11. 129.564 13,627.982 ↓ 1.0 69,532 1

Hash (cost=89,961.04..89,961.04 rows=69,504 width=73) (actual time=13,627.981..13,627.982 rows=69,532 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8154kB
12. 13,498.418 13,498.418 ↓ 1.0 69,532 1

Seq Scan on pg_type b (cost=0.00..89,961.04 rows=69,504 width=73) (actual time=0.219..13,498.418 rows=69,532 loops=1)

13. 70.301 13,156.924 ↑ 1.2 106,361 1

Hash (cost=141,698.25..141,698.25 rows=129,125 width=5) (actual time=13,156.924..13,156.924 rows=106,361 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4868kB
14. 13,086.623 13,086.623 ↑ 1.2 106,361 1

Seq Scan on pg_class elemcls (cost=0.00..141,698.25 rows=129,125 width=5) (actual time=0.225..13,086.623 rows=106,361 loops=1)

15. 150.145 1,709.161 ↑ 1.0 10,077 1

Hash (cost=1,351.77..1,351.77 rows=10,077 width=68) (actual time=1,709.161..1,709.161 rows=10,077 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1113kB
16. 1,559.016 1,559.016 ↑ 1.0 10,077 1

Seq Scan on pg_proc (cost=0.00..1,351.77 rows=10,077 width=68) (actual time=0.343..1,559.016 rows=10,077 loops=1)

17. 1.202 27.109 ↓ 1.1 409 1

Hash (cost=93.88..93.88 rows=388 width=68) (actual time=27.109..27.109 rows=409 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
18. 25.907 25.907 ↓ 1.1 409 1

Seq Scan on pg_namespace ns (cost=0.00..93.88 rows=388 width=68) (actual time=0.801..25.907 rows=409 loops=1)

19. 0.714 1.564 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=1.551..1.564 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.850 0.850 ↑ 1.0 6 1

Seq Scan on pg_range (cost=0.00..1.06 rows=6 width=8) (actual time=0.847..0.850 rows=6 loops=1)

Planning time : 8.626 ms
Execution time : 58,132.270 ms