explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0LDH : test2

Settings
# exclusive inclusive rows x rows loops node
1. 1,335.715 12,430.597 ↑ 33.1 1,634 1

Unique (cost=177,734.69..181,116.56 rows=54,110 width=1,021) (actual time=6,006.844..12,430.597 rows=1,634 loops=1)

  • Buffers: shared hit=2926 read=67371, temp read=23497 written=23497
  • t.reference_nbr ELSE s.reference_nbr END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.issue_date IS NOT NULL)) THEN t.issue_date ELSE s.iss
  • 3))
  • Planning time: 3.034 ms
  • Execution time: 12468.178 ms
2. 8,247.009 11,094.882 ↓ 2.0 110,144 1

Sort (cost=177,734.69..177,869.96 rows=54,110 width=1,021) (actual time=6,006.842..11,094.882 rows=110,144 loops=1)

  • Sort Key: s.created_on, s.active_ind, s.portfolio_staging_id, s.project_id, s.batch_id, (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.
  • Sort Method: external merge Disk: 143200kB
  • Buffers: shared hit=2926 read=67371, temp read=23497 written=23497
3. 15.685 2,847.873 ↓ 2.0 110,144 1

Append (cost=841.82..150,980.91 rows=54,110 width=1,021) (actual time=24.012..2,847.873 rows=110,144 loops=1)

  • Buffers: shared hit=2898 read=67371
4. 0.010 5.416 ↓ 0.0 0 1

Hash Right Join (cost=841.82..71,781.77 rows=66 width=993) (actual time=5.416..5.416 rows=0 loops=1)

  • Hash Cond: (t.portfolio_staging_id = s.portfolio_staging_id)
  • Buffers: shared hit=791
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on portfolio_transaction t (cost=0.00..70,092.46 rows=224,046 width=1,233) (never executed)

6. 0.001 5.406 ↓ 0.0 0 1

Hash (cost=841.80..841.80 rows=1 width=1,261) (actual time=5.406..5.406 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=791
7. 5.405 5.405 ↓ 0.0 0 1

Seq Scan on portfolio_staging s (cost=0.00..841.80 rows=1 width=1,261) (actual time=5.405..5.405 rows=0 loops=1)

  • Filter: ((project_id = 38705) AND (batch_id = 18))
  • Rows Removed by Filter: 3387
  • Buffers: shared hit=791
8.          

SubPlan (forHash Right Join)

9. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.01..0.06 rows=1 width=24) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Result (cost=0.01..12.30 rows=230 width=24) (never executed)

  • One-Time Filter: ((s.project_id = 38705) AND (s.batch_id = 18))
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on batch batch_1 (cost=0.01..12.30 rows=230 width=24) (never executed)

12. 2,370.901 2,826.772 ↓ 2.0 110,144 1

Hash Right Join (cost=1,727.70..78,658.04 rows=54,044 width=993) (actual time=18.593..2,826.772 rows=110,144 loops=1)

  • Hash Cond: (t_1.portfolio_staging_id = s_1.portfolio_staging_id)
  • Buffers: shared hit=2107 read=67371
13. 437.488 437.488 ↓ 1.0 226,752 1

Seq Scan on portfolio_transaction t_1 (cost=0.00..70,092.46 rows=224,046 width=1,233) (actual time=0.092..437.488 rows=226,752 loops=1)

  • Buffers: shared hit=481 read=67371
14. 2.675 18.383 ↓ 2.0 1,634 1

Hash (cost=1,717.49..1,717.49 rows=817 width=1,261) (actual time=18.383..18.383 rows=1,634 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2147kB
  • Buffers: shared hit=1626
15. 2.272 15.708 ↓ 2.0 1,634 1

Seq Scan on portfolio_staging s_1 (cost=850.28..1,717.49 rows=817 width=1,261) (actual time=14.195..15.708 rows=1,634 loops=1)

  • Filter: ((batch_id < 18) AND (project_id = 38705) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 1753
  • Buffers: shared hit=1626
16.          

SubPlan (forSeq Scan)

17. 13.436 13.436 ↓ 0.0 0 1

Seq Scan on portfolio_staging (cost=0.00..850.28 rows=1 width=32) (actual time=13.436..13.436 rows=0 loops=1)

  • Filter: (((portfolio_staging_json_txt ->> 'PreviousStagingId'::text) IS NOT NULL) AND (project_id = 38705) AND (batc
  • Rows Removed by Filter: 3387
  • Buffers: shared hit=835
18.          

SubPlan (forHash Right Join)

19. 0.000 0.000 ↓ 0.0 0 110,144

Limit (cost=0.01..0.06 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=110,144)

20. 0.000 0.000 ↓ 0.0 0 110,144

Result (cost=0.01..12.30 rows=230 width=24) (actual time=0.000..0.000 rows=0 loops=110,144)

  • One-Time Filter: ((s_1.project_id = 38705) AND (s_1.batch_id = 18))
21. 0.000 0.000 ↓ 0.0 0

Seq Scan on batch (cost=0.01..12.30 rows=230 width=24) (never executed)