explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j3YH

Settings
# exclusive inclusive rows x rows loops node
1. 62.836 4,790.229 ↑ 1.0 1 1

Aggregate (cost=856,031.93..856,031.94 rows=1 width=8) (actual time=4,790.229..4,790.229 rows=1 loops=1)

  • Buffers: shared hit=44379 read=726128, temp read=2355 written=2342
2. 25.084 4,727.393 ↓ 1.3 254,375 1

Nested Loop (cost=581,410.42..855,539.64 rows=196,916 width=8) (actual time=3,722.940..4,727.393 rows=254,375 loops=1)

  • Buffers: shared hit=44379 read=726128, temp read=1981 written=1967
3. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on sens_match_group matchgroup3_ (cost=0.00..1.88 rows=1 width=8) (actual time=0.017..0.026 rows=1 loops=1)

  • Filter: (match_group_id = 1)
  • Rows Removed by Filter: 69
  • Buffers: shared hit=1
4. 53.210 4,702.283 ↓ 1.3 254,375 1

Hash Join (cost=581,410.42..853,568.60 rows=196,916 width=16) (actual time=3,722.919..4,702.283 rows=254,375 loops=1)

  • Hash Cond: ((alert0_.batch_type_id)::text = (batchtype1_.batch_type_id)::text)
  • Buffers: shared hit=44378 read=726128, temp read=1981 written=1967
5. 142.934 4,648.717 ↓ 1.3 254,375 1

Hash Join (cost=581,393.56..853,013.78 rows=196,916 width=29) (actual time=3,722.544..4,648.717 rows=254,375 loops=1)

  • Hash Cond: (matches2_.alert_id = alert0_.alert_id)
  • Buffers: shared hit=44377 read=726128, temp read=1981 written=1967
6. 783.651 783.651 ↓ 1.0 358,669 1

Seq Scan on sens_match matches2_ (cost=0.00..265,414.69 rows=352,959 width=16) (actual time=0.050..783.651 rows=358,669 loops=1)

  • Filter: (match_group_id = 1)
  • Rows Removed by Filter: 1078163
  • Buffers: shared hit=14780 read=232673
7. 43.747 3,722.132 ↑ 2.3 135,639 1

Hash (cost=575,658.20..575,658.20 rows=312,349 width=21) (actual time=3,722.132..3,722.132 rows=135,639 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 1401kB
  • Buffers: shared hit=29597 read=493455, temp written=589
8. 600.953 3,678.385 ↑ 2.3 135,639 1

Seq Scan on sens_alert alert0_ (cost=539,114.23..575,658.20 rows=312,349 width=21) (actual time=3,256.055..3,678.385 rows=135,639 loops=1)

  • Filter: ((hashed SubPlan 1) OR ((hashed SubPlan 2) AND (analyst_decision IS NULL)))
  • Rows Removed by Filter: 424226
  • Buffers: shared hit=29597 read=493455
9.          

SubPlan (forSeq Scan)

10. 693.564 1,572.510 ↑ 2.5 17,603 1

HashAggregate (cost=269,007.03..269,447.10 rows=44,007 width=8) (actual time=1,440.205..1,572.510 rows=17,603 loops=1)

  • Group Key: match4_.alert_id
  • Filter: (count(match4_.alert_id) > 1)
  • Rows Removed by Filter: 533712
  • Buffers: shared hit=14717 read=232736
11. 878.946 878.946 ↑ 1.0 1,436,832 1

Seq Scan on sens_match match4_ (cost=0.00..261,822.35 rows=1,436,935 width=8) (actual time=0.005..878.946 rows=1,436,832 loops=1)

  • Buffers: shared hit=14717 read=232736
12. 660.777 1,504.922 ↓ 12.1 533,712 1

HashAggregate (cost=269,007.03..269,447.10 rows=44,007 width=8) (actual time=1,329.587..1,504.922 rows=533,712 loops=1)

  • Group Key: match5_.alert_id
  • Filter: (count(match5_.alert_id) = 1)
  • Rows Removed by Filter: 17603
  • Buffers: shared hit=14749 read=232704
13. 844.145 844.145 ↑ 1.0 1,436,832 1

Seq Scan on sens_match match5_ (cost=0.00..261,822.35 rows=1,436,935 width=8) (actual time=0.017..844.145 rows=1,436,832 loops=1)

  • Buffers: shared hit=14749 read=232704
14. 0.043 0.356 ↑ 1.0 104 1

Hash (cost=15.56..15.56 rows=104 width=13) (actual time=0.356..0.356 rows=104 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=1
15. 0.313 0.313 ↑ 1.0 104 1

Seq Scan on sens_batch_type batchtype1_ (cost=0.00..15.56 rows=104 width=13) (actual time=0.015..0.313 rows=104 loops=1)

  • Filter: ((batch_type_id)::text = ANY ('{BH_SCIC_DUDL,PL_PERD_DENY,HK_PRVB_DUDL,CN_EMPL_PEPL,VN_EMPL_DUDL,NP_BTCH_DENY,RU_BTCH_DENY,AE_EMPL_DUDL,PH_PRVB_DENY,AE_PRVB_PEPL,HK_PERD