explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RJ1U : test1

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 2,148.544 ↓ 74.3 1,634 1

HashAggregate (cost=592,369.03..592,369.25 rows=22 width=1,021) (actual time=2,147.027..2,148.544 rows=1,634 loops=1)

  • Group 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.obligor_nme IS NOT NULL)) THEN t.obligor_nme ELSE s.obligor_nme END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.ifc_obligor_nbr IS NOT NULL)) THEN t.ifc_obligor_nbr ELSE s.ifc_obligor_nbr END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.obligor_country_nme IS NOT NULL)) THEN t.obligor_country_nme ELSE s.obligor_country_nme END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.period_start_date IS NOT NULL)) THEN t.period_start_date ELSE s.period_start_date END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.transaction_details_json_txt IS NOT NULL)) THEN t.transaction_details_json_txt ELSE s.portfolio_staging_json_txt END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.reference_nbr IS NOT NULL)) THEN 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.issue_date END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.maturity_date IS NOT NULL)) THEN t.maturity_date ELSE s.maturity_date END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.transaction_ccy_code IS NOT NULL)) THEN t.transaction_ccy_code ELSE s.transaction_ccy_code END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.participation_pct IS NOT NULL)) THEN t.participation_pct ELSE s.participation_pct END), (CASE WHEN ((t.portfolio_transaction_id IS NOT NULL) AND (t.participation_usd_amt IS NOT NULL)) THEN t.participation_usd_amt ELSE s.participation_usd_amt END), (CASE WHEN (t.portfolio_transaction_id IS NOT NULL) THEN '0'::bigint ELSE s.error_count_nbr END), (CASE WHEN (((s.portfolio_staging_json_txt ->> 'SortErrorCount'::text))::bigint IS NOT NULL) THEN ((s.portfolio_staging_json_txt ->> 'SortErrorCount'::text))::bigint ELSE CASE WHEN (t.portfolio_transaction_id IS NOT NULL) THEN '0'::bigint ELSE s.error_count_nbr END END), s.transaction_status_code, (((s.portfolio_staging_json_txt ->> 'PreviousStagingId'::text))::bigint), (((s.portfolio_staging_json_txt ->> 'IsChild'::text))::boolean), (((s.portfolio_staging_json_txt ->> 'ChildCount'::text))::bigint), (CASE WHEN ((s.portfolio_staging_json_txt ->> 'RollOverTransactionFlag'::text) IS NOT NULL) THEN (s.portfolio_staging_json_txt ->> 'RollOverTransactionFlag'::text) ELSE 'No'::text END), ((SubPlan 3))
  • Hash Cond: ((t.project_id = s.project_id) AND (t.portfolio_staging_id = s.portfolio_staging_id))
  • Recheck Cond: (project_id = 38705)
  • Heap Blocks: exact=20951
  • Index Cond: (project_id = 38705)
  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2147kB
  • Index Cond: ((project_id = 38705) AND (batch_id = 2))
  • One-Time Filter: ((s.project_id = 38705) AND (s.batch_id = 2))
  • Hash Cond: ((t_1.project_id = s_1.project_id) AND (t_1.portfolio_staging_id = s_1.portfolio_staging_id))
  • Recheck Cond: (project_id = 38705)
  • Index Cond: (project_id = 38705)
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Index Cond: ((project_id = 38705) AND (batch_id < 2))
  • Filter: (NOT (hashed SubPlan 2))
  • Index Cond: ((portfolio_staging_json_txt ->> 'PreviousStagingId'::text) IS NOT NULL)
  • Filter: ((project_id = 38705) AND (batch_id = 2))
  • One-Time Filter: ((s_1.project_id = 38705) AND (s_1.batch_id = 2))
2. 2,129.956 2,129.956 ↓ 78.2 1,721 1

Append (cost=2,089.96..592,367.71 rows=22 width=1,021) (actual time=973.853..2,129.956 rows=1,721 loops=1)

3. 2,129.682 2,129.682 ↓ 82.0 1,721 1

Hash Right Join (cost=2,089.96..296,216.07 rows=21 width=993) (actual time=973.853..2,129.682 rows=1,721 loops=1)

4. 2,031.455 2,031.455 ↑ 1.1 104,663 1

Bitmap Heap Scan on portfolio_transaction t (cost=2,011.82..295,267.00 rows=115,921 width=1,224) (actual time=32.510..2,031.455 rows=104,663 loops=1)

5. 25.048 25.048 ↑ 1.1 104,663 1

Bitmap Index Scan on ie1_portfolio_transaction (cost=0.00..1,982.84 rows=115,921 width=0) (actual time=25.048..25.048 rows=104,663 loops=1)

6. 6.739 6.739 ↓ 77.8 1,634 1

Hash (cost=77.82..77.82 rows=21 width=1,244) (actual time=6.739..6.739 rows=1,634 loops=1)

7. 3.875 3.875 ↓ 77.8 1,634 1

Index Scan using ie6_portfolio_staging on portfolio_staging s (cost=0.43..77.82 rows=21 width=1,244) (actual time=0.099..3.875 rows=1,634 loops=1)

8.          

SubPlan (forHashAggregate)

9. 3.442 3.442 ↑ 1.0 1 1,721

Limit (cost=0.01..0.03 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,721)

10. 1.721 1.721 ↑ 2,664.0 1 1,721

Result (cost=0.01..65.64 rows=2,664 width=4) (actual time=0.001..0.001 rows=1 loops=1,721)

11. 1.721 1.721 ↑ 2,664.0 1 1,721

Seq Scan on batch batch_1 (cost=0.01..65.64 rows=2,664 width=4) (actual time=0.001..0.001 rows=1 loops=1,721)

12. 0.059 0.059 ↓ 0.0 0 1

Hash Right Join (cost=2,026.75..296,151.42 rows=1 width=993) (actual time=0.059..0.059 rows=0 loops=1)

13. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on portfolio_transaction t_1 (cost=2,011.82..295,267.00 rows=115,921 width=1,224) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ie1_portfolio_transaction (cost=0.00..1,982.84 rows=115,921 width=0) (never executed)

15. 0.030 0.030 ↓ 0.0 0 1

Hash (cost=14.91..14.91 rows=1 width=1,244) (actual time=0.030..0.030 rows=0 loops=1)

16. 0.028 0.028 ↓ 0.0 0 1

Index Scan using ie6_portfolio_staging on portfolio_staging s_1 (cost=7.89..14.91 rows=1 width=1,244) (actual time=0.028..0.028 rows=0 loops=1)

17. 0.000 0.000 ↓ 0.0 0

Index Scan using idx1_portfolio_staging on portfolio_staging (cost=0.43..7.46 rows=1 width=32) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.01..0.03 rows=1 width=4) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Result (cost=0.01..65.64 rows=2,664 width=4) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on batch (cost=0.01..65.64 rows=2,664 width=4) (never executed)