explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7T4c

Settings
# exclusive inclusive rows x rows loops node
1. 2,862.075 61,580.441 ↓ 0.0 0 1

Insert on pg_temp_12.tmp_links (cost=2,782,723.53..3,493,839.93 rows=14,222,328 width=34) (actual time=61,580.441..61,580.441 rows=0 loops=1)

  • Buffers: shared read=2, local hit=3775940 read=88390 dirtied=30951 written=27077, temp read=87059 written=87059
2. 475.732 58,718.366 ↑ 3.8 3,714,049 1

Subquery Scan on *SELECT* (cost=2,782,723.53..3,493,839.93 rows=14,222,328 width=34) (actual time=45,746.426..58,718.366 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=1 read=57430 written=220, temp read=87059 written=87059
3. 6,728.965 58,242.634 ↑ 3.8 3,714,049 1

GroupAggregate (cost=2,782,723.53..3,351,616.65 rows=14,222,328 width=83) (actual time=45,746.425..58,242.634 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=1 read=57430 written=220, temp read=87059 written=87059
4. 41,728.676 51,513.669 ↑ 1.0 14,207,783 1

Sort (cost=2,782,723.53..2,818,279.35 rows=14,222,328 width=79) (actual time=45,746.405..51,513.669 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=1 read=57430 written=220, temp read=87059 written=87059
5. 3,295.724 9,784.993 ↑ 1.0 14,207,783 1

Hash Join (cost=12,875.36..461,043.82 rows=14,222,328 width=79) (actual time=174.486..9,784.993 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=1 read=57430 written=220
6. 1,514.804 6,489.120 ↑ 1.0 6,817,750 1

Hash Left Join (cost=12,868.64..267,680.20 rows=6,817,816 width=75) (actual time=174.324..6,489.120 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=1 read=57430 written=220
7. 3,407.739 4,968.732 ↑ 1.0 6,817,750 1

Hash Left Join (cost=12,474.96..241,573.63 rows=6,817,816 width=75) (actual time=168.619..4,968.732 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=1 read=57365 written=220
8. 1,393.941 1,393.941 ↑ 1.0 6,817,750 1

Seq Scan on pg_temp_12.amt_links al (cost=0.00..118,309.16 rows=6,817,816 width=26) (actual time=0.011..1,393.941 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. 51.338 167.052 ↑ 1.0 232,817 1

Hash (cost=9,563.87..9,563.87 rows=232,887 width=53) (actual time=167.052..167.052 rows=232,817 loops=1)

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

Seq Scan on pg_temp_12.amt_objects ao (cost=0.00..9,563.87 rows=232,887 width=53) (actual time=0.030..115.714 rows=232,817 loops=1)

  • Output: ao.prototypename, ao.objectid
  • Buffers: local read=7235
11. 2.798 5.584 ↑ 1.0 14,608 1

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

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

Seq Scan on pg_temp_12.amt_ignorablelinks ail (cost=0.00..211.08 rows=14,608 width=8) (actual time=0.038..2.786 rows=14,608 loops=1)

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

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

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

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

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