#	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)

Unique  (cost=177734.69..181116.56 rows=54110 width=1021) (actual time=6006.844..12430.597 rows=1634 loops=1)
   Buffers: shared hit=2926 read=67371, temp read=23497 written=23497
   ->  Sort  (cost=177734.69..177869.96 rows=54110 width=1021) (actual time=6006.842..11094.882 rows=110144 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.
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.o
bligor_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.tran
saction_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.iss
ue_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 W
HEN ((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 W
HEN ((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.p
ortfolio_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.portfoli
o_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))
         Sort Method: external merge  Disk: 143200kB
         Buffers: shared hit=2926 read=67371, temp read=23497 written=23497
         ->  Append  (cost=841.82..150980.91 rows=54110 width=1021) (actual time=24.012..2847.873 rows=110144 loops=1)
               Buffers: shared hit=2898 read=67371
               ->  Hash Right Join  (cost=841.82..71781.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
                     ->  Seq Scan on portfolio_transaction t  (cost=0.00..70092.46 rows=224046 width=1233) (never executed)
                     ->  Hash  (cost=841.80..841.80 rows=1 width=1261) (actual time=5.406..5.406 rows=0 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 8kB
                           Buffers: shared hit=791
                           ->  Seq Scan on portfolio_staging s  (cost=0.00..841.80 rows=1 width=1261) (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
                     SubPlan 3
                       ->  Limit  (cost=0.01..0.06 rows=1 width=24) (never executed)
                             ->  Result  (cost=0.01..12.30 rows=230 width=24) (never executed)
                                   One-Time Filter: ((s.project_id = 38705) AND (s.batch_id = 18))
                                   ->  Seq Scan on batch batch_1  (cost=0.01..12.30 rows=230 width=24) (never executed)
               ->  Hash Right Join  (cost=1727.70..78658.04 rows=54044 width=993) (actual time=18.593..2826.772 rows=110144 loops=1)
                     Hash Cond: (t_1.portfolio_staging_id = s_1.portfolio_staging_id)
                     Buffers: shared hit=2107 read=67371
                     ->  Seq Scan on portfolio_transaction t_1  (cost=0.00..70092.46 rows=224046 width=1233) (actual time=0.092..437.488 rows=226752 loops=1)
                           Buffers: shared hit=481 read=67371
                     ->  Hash  (cost=1717.49..1717.49 rows=817 width=1261) (actual time=18.383..18.383 rows=1634 loops=1)
                           Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 2147kB
                           Buffers: shared hit=1626
                           ->  Seq Scan on portfolio_staging s_1  (cost=850.28..1717.49 rows=817 width=1261) (actual time=14.195..15.708 rows=1634 loops=1)
                                 Filter: ((batch_id < 18) AND (project_id = 38705) AND (NOT (hashed SubPlan 2)))
                                 Rows Removed by Filter: 1753
                                 Buffers: shared hit=1626
                                 SubPlan 2
                                   ->  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
h_id = 18))
                                         Rows Removed by Filter: 3387
                                         Buffers: shared hit=835
                     SubPlan 1
                       ->  Limit  (cost=0.01..0.06 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=110144)
                             ->  Result  (cost=0.01..12.30 rows=230 width=24) (actual time=0.000..0.000 rows=0 loops=110144)
                                   One-Time Filter: ((s_1.project_id = 38705) AND (s_1.batch_id = 18))
                                   ->  Seq Scan on batch  (cost=0.01..12.30 rows=230 width=24) (never executed)
 Planning time: 3.034 ms
 Execution time: 12468.178 ms
(48 rows)

Per node type stats

node type	count	sum of times	% of query
Append	1	15.685 ms	0.1 %
Hash	2	2.676 ms	0.0 %
Hash Right Join	2	2,370.911 ms	19.1 %
Limit	2	0.000 ms	0.0 %
Result	2	0.000 ms	0.0 %
Seq Scan	7	458.601 ms	3.7 %
Sort	1	8,247.009 ms	66.3 %
Unique	1	1,335.715 ms	10.7 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
batch	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
portfolio_staging	3	21.113 ms	0.2 %
Seq Scan	3	21.113 ms	100.0 %
portfolio_transaction	2	437.488 ms	3.5 %
Seq Scan	2	437.488 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HnL : test33q4

Color mode:

Visible columns:

Per node type stats

Per table stats