explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cPDq

Settings
# exclusive inclusive rows x rows loops node
1. 0.862 3,398.544 ↓ 0.0 0 1

Insert on pg_temp_11.tmp_missingselfprojectdependencylinks (cost=0.00..1,690,641.89 rows=3,185 width=4) (actual time=3,398.544..3,398.544 rows=0 loops=1)

  • Buffers: local hit=311 read=4670 dirtied=1 written=303
2. 0.753 3,397.682 ↑ 37.0 86 1

Nested Loop (cost=0.00..1,690,641.89 rows=3,185 width=4) (actual time=3,397.169..3,397.682 rows=86 loops=1)

  • Output: (- ao.objectid), ao.objectid, 'S'::character(1)
  • Join Filter: (ao.objecttypeid = tnupt.projecttypeid)
  • Rows Removed by Join Filter: 7577
  • Buffers: local hit=226 read=4669 written=303
3. 0.008 0.008 ↑ 30.4 79 1

Seq Scan on pg_temp_11.tmp_nonuaprojecttypes tnupt (cost=0.00..34.00 rows=2,400 width=4) (actual time=0.003..0.008 rows=79 loops=1)

  • Output: tnupt.projecttypeid
  • Buffers: local hit=1
4. 0.320 3,396.921 ↑ 2.7 97 79

Materialize (cost=0.00..1,681,060.59 rows=265 width=8) (actual time=0.001..42.999 rows=97 loops=79)

  • Output: ao.objectid, ao.objecttypeid
  • Buffers: local hit=225 read=4669 written=303
5. 1,509.759 3,396.601 ↑ 2.7 97 1

Nested Loop Semi Join (cost=0.00..1,681,059.26 rows=265 width=8) (actual time=0.095..3,396.601 rows=97 loops=1)

  • Output: ao.objectid, ao.objecttypeid
  • Join Filter: (((apdl0.projectid = ao.objectid) AND (apdl0.projectkind = 'S'::bpchar)) OR ((apdl0.mainprojectid = ao.objectid) AND (apdl0.mainprojectkind = 'S'::bpchar)))
  • Rows Removed by Join Filter: 13314989
  • Buffers: local hit=225 read=4669 written=303
6. 856.617 1,398.501 ↓ 1.3 69,763 1

Nested Loop Anti Join (cost=0.00..6,869.76 rows=53,148 width=8) (actual time=0.088..1,398.501 rows=69,763 loops=1)

  • Output: ao.objectid, ao.objecttypeid
  • Join Filter: ((apdl0.projectid = ao.objectid) AND (apdl0.mainprojectid = ao.objectid))
  • Rows Removed by Join Filter: 13324733
  • Buffers: local hit=223 read=4669 written=303
7. 53.543 53.543 ↑ 1.0 69,763 1

Seq Scan on pg_temp_11.amt_objects ao (cost=0.00..5,598.64 rows=70,864 width=8) (actual time=0.002..53.543 rows=69,763 loops=1)

  • Output: ao.objectid, ao.guid, ao.shortguid, ao.objecttypeid, ao.prototypename
  • Buffers: local hit=223 read=4667 written=303
8. 488.289 488.341 ↓ 191.0 191 69,763

Materialize (cost=0.00..31.00 rows=1 width=8) (actual time=0.000..0.007 rows=191 loops=69,763)

  • Output: apdl0.projectid, apdl0.mainprojectid
  • Buffers: local read=2
9. 0.052 0.052 ↓ 191.0 191 1

Seq Scan on pg_temp_11.amt_projectdependencylinks apdl0 (cost=0.00..31.00 rows=1 width=8) (actual time=0.014..0.052 rows=191 loops=1)

  • Output: apdl0.projectid, apdl0.mainprojectid
  • Filter: ((apdl0.projectkind = 'S'::bpchar) AND (apdl0.mainprojectkind = 'S'::bpchar))
  • Buffers: local read=2
10. 488.321 488.341 ↑ 7.3 191 69,763

Materialize (cost=0.00..31.00 rows=1,400 width=24) (actual time=0.000..0.007 rows=191 loops=69,763)

  • Output: apdl0.projectid, apdl0.projectkind, apdl0.mainprojectid, apdl0.mainprojectkind
  • Buffers: local hit=2
11. 0.020 0.020 ↑ 7.3 191 1

Seq Scan on pg_temp_11.amt_projectdependencylinks apdl0 (cost=0.00..24.00 rows=1,400 width=24) (actual time=0.002..0.020 rows=191 loops=1)

  • Output: apdl0.projectid, apdl0.projectkind, apdl0.mainprojectid, apdl0.mainprojectkind
  • Buffers: local hit=2