explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iWDR

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 130,752.575 ↑ 1.0 1 1

GroupAggregate (cost=22.56..27,770.31 rows=1 width=36) (actual time=130,752.575..130,752.575 rows=1 loops=1)

  • Output: count(*), party_0.id
  • Group Key: party_0.id
  • Buffers: shared hit=6932585
2. 7.717 130,752.570 ↑ 1.0 1 1

Nested Loop (cost=22.56..27,770.30 rows=1 width=36) (actual time=15,691.967..130,752.570 rows=1 loops=1)

  • Output: party_0.id
  • Join Filter: ((virtual_3.terminal_id)::text = (part_3.terminal_id)::text)
  • Rows Removed by Join Filter: 1914
  • Buffers: shared hit=6932585
3. 3.229 84.403 ↓ 1,915.0 1,915 1

Nested Loop (cost=22.56..21,634.55 rows=1 width=71) (actual time=0.320..84.403 rows=1,915 loops=1)

  • Output: virtual_3.terminal_id, party_0.id
  • Buffers: shared hit=25180
4. 6.403 58.194 ↓ 1,915.0 1,915 1

Hash Join (cost=22.28..21,626.24 rows=1 width=70) (actual time=0.310..58.194 rows=1,915 loops=1)

  • Output: virtual_3.terminal_id, site_3.party_id
  • Hash Cond: ((virtual_3.site_id)::text = (site_3.id)::text)
  • Buffers: shared hit=19435
5. 51.774 51.774 ↓ 1.1 11,489 1

Seq Scan on ingestate.asset_track_virtual_event virtual_3 (cost=0.00..21,563.99 rows=10,658 width=106) (actual time=0.004..51.774 rows=11,489 loops=1)

  • Output: virtual_3.site_id, virtual_3.terminal_id, virtual_3.owner_id
  • Filter: ((virtual_3.inventory_event_id IS NULL) AND (virtual_3.estate_owner_id = 358))
  • Rows Removed by Filter: 165086
  • Buffers: shared hit=19430
6. 0.002 0.017 ↑ 6.0 1 1

Hash (cost=22.20..22.20 rows=6 width=71) (actual time=0.017..0.017 rows=1 loops=1)

  • Output: site_3.id, site_3.party_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=5
7. 0.015 0.015 ↑ 6.0 1 1

Index Scan using plc_site_party_id_idx on ingestate.plc_site site_3 (cost=0.41..22.20 rows=6 width=71) (actual time=0.014..0.015 rows=1 loops=1)

  • Output: site_3.id, site_3.party_id
  • Index Cond: ((site_3.party_id)::text = '-6cd1dec3:15ec8ab382a:-7b6b:AC150046'::text)
  • Filter: ((site_3.site_type)::text = 'SITE_ADDRESS'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5
8. 22.980 22.980 ↑ 1.0 1 1,915

Index Scan using plc_party_pkey on ingestate.plc_party party_0 (cost=0.28..8.30 rows=1 width=36) (actual time=0.011..0.012 rows=1 loops=1,915)

  • Output: party_0.id
  • Index Cond: ((party_0.id)::text = '-6cd1dec3:15ec8ab382a:-7b6b:AC150046'::text)
  • Filter: (party_0.estate_owner_id = 358)
  • Buffers: shared hit=5745
9. 130,660.450 130,660.450 ↑ 839.0 1 1,915

Seq Scan on ingestate.plc_part part_3 (cost=0.00..6,125.26 rows=839 width=70) (actual time=20.737..68.230 rows=1 loops=1,915)

  • Output: part_3.terminal_id, part_3.part_template_id
  • Filter: (upper((part_3.serial_number)::text) ~~ '15168PP81550412'::text)
  • Rows Removed by Filter: 169857
  • Buffers: shared hit=6907405
Planning time : 3.420 ms
Execution time : 130,752.624 ms