explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XVgn

Settings
# exclusive inclusive rows x rows loops node
1. 2,873.213 62,199.431 ↓ 0.0 0 1

Insert on pg_temp_13.tmp_links (cost=2,777,902.89..3,487,682.99 rows=14,195,602 width=34) (actual time=62,199.431..62,199.431 rows=0 loops=1)

  • Buffers: shared read=2, local hit=3775942 read=88389 dirtied=30951 written=27077, temp read=87059 written=87059
2. 475.069 59,326.218 ↑ 3.8 3,714,049 1

Subquery Scan on *SELECT* (cost=2,777,902.89..3,487,682.99 rows=14,195,602 width=34) (actual time=46,357.874..59,326.218 rows=3,714,049 loops=1)

  • Output: "*SELECT*".callerid, "*SELECT*".callerkind, "*SELECT*".calleeid, "*SELECT*".calleekind, "*SELECT*".projectid, "*SELECT*".projectkind, "*SELECT*"."case", "*SELECT*".acctyplo, "*SELECT*".acctyphi, "*SELECT*".min
  • Buffers: shared read=2, local hit=3 read=57429 written=220, temp read=87059 written=87059
3. 6,710.926 58,851.149 ↑ 3.8 3,714,049 1

GroupAggregate (cost=2,777,902.89..3,345,726.97 rows=14,195,602 width=81) (actual time=46,357.873..58,851.149 rows=3,714,049 loops=1)

  • Output: al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, CASE WHEN (ao.prototypename IS NOT NULL) THEN 1 ELSE 0 END, kdat.acctyplo, kdat.acctyphi, min(CASE WHEN (ail.ignorable = 1) THEN 1 ELSE 0 END), ao.prototypename
  • Group Key: al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, kdat.acctyplo, kdat.acctyphi, ao.prototypename
  • Buffers: shared read=2, local hit=3 read=57429 written=220, temp read=87059 written=87059
4. 42,281.918 52,140.223 ↓ 1.0 14,207,783 1

Sort (cost=2,777,902.89..2,813,391.90 rows=14,195,602 width=77) (actual time=46,357.846..52,140.223 rows=14,207,783 loops=1)

  • Output: al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, kdat.acctyplo, kdat.acctyphi, ao.prototypename, ail.ignorable
  • Sort Key: al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, kdat.acctyplo, kdat.acctyphi, ao.prototypename
  • Sort Method: external merge Disk: 696088kB
  • Buffers: shared read=2, local hit=3 read=57429 written=220, temp read=87059 written=87059
5. 3,297.791 9,858.305 ↓ 1.0 14,207,783 1

Hash Join (cost=12,876.36..460,777.56 rows=14,195,602 width=77) (actual time=181.613..9,858.305 rows=14,207,783 loops=1)

  • Output: al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, kdat.acctyplo, kdat.acctyphi, ao.prototypename, ail.ignorable
  • Hash Cond: (al.linktypeid = kdat.linktypeid)
  • Buffers: shared read=2, local hit=3 read=57429 written=220
6. 1,551.360 6,559.575 ↑ 1.0 6,817,750 1

Hash Left Join (cost=12,869.64..267,681.20 rows=6,817,816 width=73) (actual time=180.663..6,559.575 rows=6,817,750 loops=1)

  • Output: al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid, ao.prototypename, ail.ignorable
  • Hash Cond: (al.linkid = ail.linkid)
  • Buffers: local hit=3 read=57429 written=220
7. 3,407.123 5,003.420 ↑ 1.0 6,817,750 1

Hash Left Join (cost=12,475.96..241,574.63 rows=6,817,816 width=73) (actual time=175.741..5,003.420 rows=6,817,750 loops=1)

  • Output: al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid, al.linkid, ao.prototypename
  • Hash Cond: (al.calleeid = ao.objectid)
  • Join Filter: (al.calleekind = 'S'::bpchar)
  • Buffers: local hit=3 read=57364 written=220
8. 1,422.134 1,422.134 ↑ 1.0 6,817,750 1

Seq Scan on pg_temp_13.amt_links al (cost=0.00..118,309.16 rows=6,817,816 width=26) (actual time=0.015..1,422.134 rows=6,817,750 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid
  • Buffers: local hit=1 read=50130 written=220
9. 54.712 174.163 ↑ 1.0 232,817 1

Hash (cost=9,564.87..9,564.87 rows=232,887 width=51) (actual time=174.163..174.163 rows=232,817 loops=1)

  • Output: ao.prototypename, ao.objectid
  • Buckets: 262144 Batches: 1 Memory Usage: 10407kB
  • Buffers: local hit=2 read=7234
10. 119.451 119.451 ↑ 1.0 232,817 1

Seq Scan on pg_temp_13.amt_objects ao (cost=0.00..9,564.87 rows=232,887 width=51) (actual time=0.018..119.451 rows=232,817 loops=1)

  • Output: ao.prototypename, ao.objectid
  • Buffers: local hit=2 read=7234
11. 2.442 4.795 ↑ 1.0 14,608 1

Hash (cost=211.08..211.08 rows=14,608 width=8) (actual time=4.795..4.795 rows=14,608 loops=1)

  • Output: ail.ignorable, ail.linkid
  • Buckets: 16384 Batches: 1 Memory Usage: 699kB
  • Buffers: local read=65
12. 2.353 2.353 ↑ 1.0 14,608 1

Seq Scan on pg_temp_13.amt_ignorablelinks ail (cost=0.00..211.08 rows=14,608 width=8) (actual time=0.039..2.353 rows=14,608 loops=1)

  • Output: ail.ignorable, ail.linkid
  • Buffers: local read=65
13. 0.042 0.939 ↑ 1.0 210 1

Hash (cost=4.10..4.10 rows=210 width=12) (actual time=0.939..0.939 rows=210 loops=1)

  • Output: kdat.acctyplo, kdat.acctyphi, kdat.linktypeid
  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared read=2
14. 0.897 0.897 ↑ 1.0 210 1

Seq Scan on castoncast_local.kb_decodeacctyp kdat (cost=0.00..4.10 rows=210 width=12) (actual time=0.505..0.897 rows=210 loops=1)

  • Output: kdat.acctyplo, kdat.acctyphi, kdat.linktypeid
  • Buffers: shared read=2