explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OvcB

Settings
# exclusive inclusive rows x rows loops node
1. 51.654 1,383,076.257 ↓ 0.0 0 1

Insert on pg_temp_22.refinaccbook (cost=58,917.62..86,159.98 rows=1 width=459) (actual time=1,383,076.257..1,383,076.257 rows=0 loops=1)

  • Buffers: shared hit=104,366,617 read=171, local hit=33,727 read=899 dirtied=898
2. 11.059 1,383,024.603 ↓ 31,908.0 31,908 1

Nested Loop (cost=58,917.62..86,159.98 rows=1 width=459) (actual time=1,382,711.391..1,383,024.603 rows=31,908 loops=1)

  • Output: oclr.idkey, oclr.idnam, ocle.idkey, ocle.idnam, opro.idkey, opro.idnam
  • Buffers: shared hit=104,366,617 read=171
3. 155.567 1,382,949.728 ↓ 31,908.0 31,908 1

Hash Join (cost=58,917.20..86,159.47 rows=1 width=459) (actual time=1,382,711.359..1,382,949.728 rows=31,908 loops=1)

  • Output: oclr.idkey, oclr.idnam, ocle.idkey, ocle.idnam, opro.idkey, opro.idnam
  • Hash Cond: ((opro.idnam)::text = (siopro.name_id)::text)
  • Buffers: shared hit=104,238,985 read=171
4. 82.860 82.860 ↑ 1.0 490,346 1

Seq Scan on ref_castoncast_local_v2.objects opro (cost=0.00..25,403.46 rows=490,346 width=153) (actual time=0.027..82.860 rows=490,346 loops=1)

  • Output: opro.idkey, opro.idnam, opro.idshortnam, opro.objtyp
  • Buffers: shared hit=20,500
5. 144.252 1,382,711.301 ↓ 31,908.0 31,908 1

Hash (cost=58,917.19..58,917.19 rows=1 width=822) (actual time=1,382,711.301..1,382,711.301 rows=31,908 loops=1)

  • Output: oclr.idkey, oclr.idnam, ocle.idkey, ocle.idnam, siopro.name_id
  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7,173kB
  • Buffers: shared hit=104,218,485 read=171
6. 53,600.887 1,382,567.049 ↓ 31,908.0 31,908 1

Nested Loop (cost=30,007.20..58,917.19 rows=1 width=822) (actual time=533,747.109..1,382,567.049 rows=31,908 loops=1)

  • Output: oclr.idkey, oclr.idnam, ocle.idkey, ocle.idnam, siopro.name_id
  • Join Filter: ((sip.session_id = siopro.session_id) AND (sil.project_id = siopro.object_id))
  • Rows Removed by Join Filter: 435,959,004
  • Buffers: shared hit=104,218,485 read=171
7. 97.760 1,280,848.898 ↓ 31,908.0 31,908 1

Nested Loop (cost=30,007.20..58,550.59 rows=1 width=326) (actual time=533,746.989..1,280,848.898 rows=31,908 loops=1)

  • Output: sil.session_id, sil.project_id, sip.session_id, sioclr.session_id, oclr.idkey, oclr.idnam, siocle.session_id, ocle.idkey, ocle.idnam
  • Buffers: shared hit=94,263,189 read=171
8. 169.745 1,280,527.782 ↓ 31,908.0 31,908 1

Nested Loop (cost=30,006.77..58,550.08 rows=1 width=326) (actual time=533,746.963..1,280,527.782 rows=31,908 loops=1)

  • Output: sil.session_id, sil.project_id, sip.session_id, sioclr.session_id, oclr.idkey, oclr.idnam, siocle.session_id, ocle.idkey, ocle.idnam
  • Buffers: shared hit=94,135,532 read=141
9. 442,290.113 1,279,592.245 ↓ 31,908.0 31,908 1

Nested Loop (cost=30,006.35..58,549.57 rows=1 width=326) (actual time=533,744.164..1,279,592.245 rows=31,908 loops=1)

  • Output: sil.session_id, sil.project_id, sip.session_id, sioclr.session_id, oclr.idkey, oclr.idnam, siocle.session_id, ocle.idkey, ocle.idnam
  • Join Filter: ((sil.session_id = sip.session_id) AND (sil.link_id = sip.object_id))
  • Rows Removed by Join Filter: 3,636,788,252
  • Buffers: shared hit=94,007,947
10. 536.343 534,361.892 ↓ 96,080.0 96,080 1

Hash Join (cost=30,006.35..57,248.62 rows=1 width=326) (actual time=533,725.037..534,361.892 rows=96,080 loops=1)

  • Output: sil.session_id, sil.link_id, sil.project_id, sioclr.session_id, oclr.idkey, oclr.idnam, siocle.session_id, ocle.idkey, ocle.idnam
  • Hash Cond: ((ocle.idnam)::text = (siocle.name_id)::text)
  • Buffers: shared hit=59,995,627
11. 100.541 100.541 ↑ 1.0 490,346 1

Seq Scan on ref_castoncast_local_v2.objects ocle (cost=0.00..25,403.46 rows=490,346 width=153) (actual time=0.007..100.541 rows=490,346 loops=1)

  • Output: ocle.idkey, ocle.idnam, ocle.idshortnam, ocle.objtyp
  • Buffers: shared hit=20,500
12. 239.942 533,725.008 ↓ 96,080.0 96,080 1

Hash (cost=30,006.34..30,006.34 rows=1 width=689) (actual time=533,725.008..533,725.008 rows=96,080 loops=1)

  • Output: sil.session_id, sil.link_id, sil.project_id, sioclr.session_id, oclr.idkey, oclr.idnam, siocle.session_id, siocle.name_id
  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 17,092kB
  • Buffers: shared hit=59,975,127
13. 159,540.604 533,485.066 ↓ 96,080.0 96,080 1

Nested Loop (cost=2,397.47..30,006.34 rows=1 width=689) (actual time=267,123.895..533,485.066 rows=96,080 loops=1)

  • Output: sil.session_id, sil.link_id, sil.project_id, sioclr.session_id, oclr.idkey, oclr.idnam, siocle.session_id, siocle.name_id
  • Join Filter: ((sil.session_id = siocle.session_id) AND (sil.target_id = siocle.object_id))
  • Rows Removed by Join Filter: 1,312,741,040
  • Buffers: shared hit=59,975,127
14. 339.678 267,583.902 ↓ 96,080.0 96,080 1

Hash Join (cost=2,397.47..29,639.74 rows=1 width=173) (actual time=267,123.851..267,583.902 rows=96,080 loops=1)

  • Output: sil.session_id, sil.link_id, sil.target_id, sil.project_id, sioclr.session_id, oclr.idkey, oclr.idnam
  • Hash Cond: ((oclr.idnam)::text = (sioclr.name_id)::text)
  • Buffers: shared hit=29,998,167
15. 120.403 120.403 ↑ 1.0 490,346 1

Seq Scan on ref_castoncast_local_v2.objects oclr (cost=0.00..25,403.46 rows=490,346 width=153) (actual time=0.007..120.403 rows=490,346 loops=1)

  • Output: oclr.idkey, oclr.idnam, oclr.idshortnam, oclr.objtyp
  • Buffers: shared hit=20,500
16. 209.827 267,123.821 ↓ 96,080.0 96,080 1

Hash (cost=2,397.46..2,397.46 rows=1 width=536) (actual time=267,123.821..267,123.821 rows=96,080 loops=1)

  • Output: sil.session_id, sil.link_id, sil.target_id, sil.project_id, sioclr.session_id, sioclr.name_id
  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 12,356kB
  • Buffers: shared hit=29,977,667
17. 159,043.333 266,913.994 ↓ 96,080.0 96,080 1

Nested Loop (cost=0.00..2,397.46 rows=1 width=536) (actual time=0.034..266,913.994 rows=96,080 loops=1)

  • Output: sil.session_id, sil.link_id, sil.target_id, sil.project_id, sioclr.session_id, sioclr.name_id
  • Join Filter: ((sil.session_id = sioclr.session_id) AND (sil.source_id = sioclr.object_id))
  • Rows Removed by Join Filter: 1,312,741,040
  • Buffers: shared hit=29,977,667
18. 164.981 164.981 ↓ 96,080.0 96,080 1

Seq Scan on diw_castoncast_local_v2.sav_in_links_19 sil (cost=0.00..2,030.86 rows=1 width=20) (actual time=0.019..164.981 rows=96,080 loops=1)

  • Output: sil.session_id, sil.link_id, sil.source_id, sil.target_id, sil.project_id, sil.source_kind, sil.target_kind, sil.project_kind, sil.link_type_id
  • Filter: ((sil.source_kind = 'E'::bpchar) AND (sil.target_kind = 'E'::bpchar) AND (sil.project_kind = 'E'::bpchar))
  • Buffers: shared hit=707
19. 107,705.680 107,705.680 ↓ 6.3 13,664 96,080

Seq Scan on diw_castoncast_local_v2.sav_in_objects_19 sioclr (cost=0.00..333.84 rows=2,184 width=524) (actual time=0.002..1.121 rows=13,664 loops=96,080)

  • Output: sioclr.session_id, sioclr.object_id, sioclr.name_id, sioclr.short_name_id, sioclr.object_type_id
  • Buffers: shared hit=29,976,960
20. 106,360.560 106,360.560 ↓ 6.3 13,664 96,080

Seq Scan on diw_castoncast_local_v2.sav_in_objects_19 siocle (cost=0.00..333.84 rows=2,184 width=524) (actual time=0.002..1.107 rows=13,664 loops=96,080)

  • Output: siocle.session_id, siocle.object_id, siocle.name_id, siocle.short_name_id, siocle.object_type_id
  • Buffers: shared hit=29,976,960
21. 302,940.240 302,940.240 ↑ 1.0 37,852 96,080

Seq Scan on diw_castoncast_local_v2.sav_in_positions_19 sip (cost=0.00..732.78 rows=37,878 width=8) (actual time=0.004..3.153 rows=37,852 loops=96,080)

  • Output: sip.session_id, sip.object_id, sip.object_source_id, sip.object_source_kind, sip.seq_num, sip.position_mode, sip.position1, sip.position2, sip.position3, sip.position4, sip.group_num
  • Buffers: shared hit=34,012,320
22. 765.792 765.792 ↑ 1.0 1 31,908

Index Only Scan using pk_keysidkey on ref_castoncast_local_v2.keys kclr (cost=0.42..0.50 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=31,908)

  • Output: kclr.idkey
  • Index Cond: (kclr.idkey = oclr.idkey)
  • Heap Fetches: 31,908
  • Buffers: shared hit=127,585 read=141
23. 223.356 223.356 ↑ 1.0 1 31,908

Index Only Scan using pk_keysidkey on ref_castoncast_local_v2.keys kcle (cost=0.42..0.50 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=31,908)

  • Output: kcle.idkey
  • Index Cond: (kcle.idkey = ocle.idkey)
  • Heap Fetches: 31,908
  • Buffers: shared hit=127,657 read=30
24. 48,117.264 48,117.264 ↓ 6.3 13,664 31,908

Seq Scan on diw_castoncast_local_v2.sav_in_objects_19 siopro (cost=0.00..333.84 rows=2,184 width=524) (actual time=0.004..1.508 rows=13,664 loops=31,908)

  • Output: siopro.session_id, siopro.object_id, siopro.name_id, siopro.short_name_id, siopro.object_type_id
  • Buffers: shared hit=9,955,296
25. 63.816 63.816 ↑ 1.0 1 31,908

Index Only Scan using pk_keysidkey on ref_castoncast_local_v2.keys kpro (cost=0.42..0.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=31,908)

  • Output: kpro.idkey
  • Index Cond: (kpro.idkey = opro.idkey)
  • Heap Fetches: 31,908
  • Buffers: shared hit=127,632