explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Yg5

Settings
# exclusive inclusive rows x rows loops node
1. 763.731 88,234.799 ↑ 921.2 1,197 1

Unique (cost=6,157,149.20..6,168,176.00 rows=1,102,680 width=20) (actual time=81,972.697..88,234.799 rows=1,197 loops=1)

  • Buffers: shared hit=331131 read=1758624, temp read=156714 written=156460
2. 24,200.164 87,471.068 ↓ 1.0 1,149,545 1

Sort (cost=6,157,149.20..6,159,905.90 rows=1,102,680 width=20) (actual time=81,972.696..87,471.068 rows=1,149,545 loops=1)

  • Sort Key: (CASE WHEN ((sg_con_d4.sg_con4_carrier)::text = ''::text) THEN sg_con_m1.sg_con_carrier ELSE sg_con_d4.sg_con4_carrier END), sg_dlr_m1.sg_dlr_agent, sg_con_d4.sg_con4_plc
  • Sort Method: external merge Disk: 30224kB
  • Buffers: shared hit=331131 read=1758624, temp read=156714 written=156460
3. 7,542.440 63,270.904 ↓ 1.0 1,149,545 1

Hash Join (cost=4,813,139.11..6,030,326.02 rows=1,102,680 width=20) (actual time=52,891.280..63,270.904 rows=1,149,545 loops=1)

  • Hash Cond: (sg_con_d4.sg_con4_contract = sg_con_m1.sg_con_contract)
  • Buffers: shared hit=331127 read=1758624, temp read=152931 written=152677
4. 1,618.589 4,532.146 ↓ 1.0 1,149,545 1

Hash Join (cost=688,335.11..1,640,463.32 rows=1,102,680 width=27) (actual time=1,690.481..4,532.146 rows=1,149,545 loops=1)

  • Hash Cond: (sg_con_d4.sg_con4_dealer = sg_dlr_m1.sg_dlr_dealer)
  • Buffers: shared hit=235385
5. 1,232.756 2,814.226 ↓ 1.0 1,149,545 1

Bitmap Heap Scan on sg_con_d4 (cost=680,947.61..1,590,346.97 rows=1,102,680 width=29) (actual time=1,591.119..2,814.226 rows=1,149,545 loops=1)

  • Recheck Cond: (sg_con4_busdate >= '2019-06-01'::date)
  • Filter: ((sg_con4_locked)::text = 'y'::text)
  • Buffers: shared hit=233212
6. 1,581.470 1,581.470 ↑ 1.0 1,183,389 1

Bitmap Index Scan on sg_con_d402 (cost=0.00..680,671.94 rows=1,200,653 width=0) (actual time=1,581.470..1,581.470 rows=1,183,389 loops=1)

  • Index Cond: (sg_con4_busdate >= '2019-06-01'::date)
  • Buffers: shared hit=192516
7. 49.873 99.331 ↑ 1.1 76,900 1

Hash (cost=4,674.92..4,674.92 rows=83,464 width=16) (actual time=99.331..99.331 rows=76,900 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3605kB
  • Buffers: shared hit=2171
8. 49.458 49.458 ↑ 1.1 76,900 1

Seq Scan on sg_dlr_m1 (cost=0.00..4,674.92 rows=83,464 width=16) (actual time=0.006..49.458 rows=76,900 loops=1)

  • Buffers: shared hit=2171
9. 23,379.974 51,196.318 ↑ 1.0 33,517,266 1

Hash (cost=2,865,204.24..2,865,204.24 rows=33,694,608 width=15) (actual time=51,196.318..51,196.318 rows=33,517,266 loops=1)

  • Buckets: 65536 Batches: 128 Memory Usage: 12312kB
  • Buffers: shared hit=95742 read=1758624, temp written=145814
10. 27,816.344 27,816.344 ↑ 1.0 33,517,266 1

Seq Scan on sg_con_m1 (cost=0.00..2,865,204.24 rows=33,694,608 width=15) (actual time=0.008..27,816.344 rows=33,517,266 loops=1)

  • Buffers: shared hit=95742 read=1758624