explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fViG : Optimization for: sss; plan #icTc

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.005 2,966,143.501 ↓ 0.0 0 1

unique (cost=100,670,903.31..100,686,469.71 rows=270,720 width=379) (actual time=2,966,143.501..2,966,143.501 rows=0 loops=1)

2. 0.095 2,966,143.496 ↓ 0.0 0 1

Sort (cost=100,670,903.31..100,671,580.11 rows=270,720 width=379) (actual time=2,966,143.496..2,966,143.496 rows=0 loops=1)

  • Sort Key: d_inv_admin_audit_trail.party_id, d_inv_admin_audit_trail.party_access_id, d_inv_admin_audit_trail.logged_in, d_inv_admin_audit_trail.logged_out, d_inv_admin_audit_trail.category_code, d_inv_admin_audit_trail.action_type, d_inv_admin_audit_trail.transfer_id, d_inv_admin_audit_trail.status_id, d_inv_admin_audit_trail.account1, d_inv_admin_audit_trail.account2, d_inv_admin_audit_trail.remarks, d_inv_admin_audit_trail.attr_1_name, d_inv_admin_audit_trail.attr_1_value, d_inv_admin_audit_trail.attr_2_name, d_inv_admin_audit_trail.attr_2_value, d_inv_admin_audit_trail.attr_3_name, d_inv_admin_audit_trail.attr_3_value, d_inv_admin_audit_trail.created_by, (to_char(d_inv_admin_audit_trail.created_on, 'DD-MON-YYYY HH24:MI:SS'::text)), d_inv_admin_audit_trail.msisdn, d_inv_admin_audit_trail.ip, d_inv_admin_audit_trail.cust_id
  • Sort Method: quicksort Memory: 25kB
3. 0.029 2,966,143.401 ↓ 0.0 0 1

Append (cost=50,340,787.96..100,504,893.65 rows=270,720 width=379) (actual time=2,966,143.401..2,966,143.401 rows=0 loops=1)

4. 1,172.049 1,498,499.384 ↓ 0.0 0 1

Hash Join (cost=50,340,787.96..50,636,961.25 rows=268,678 width=379) (actual time=1,498,499.384..1,498,499.384 rows=0 loops=1)

  • Hash Cond: ((d_inv_admin_audit_trail.attr_1_value)::text = (atti.transaction_key)::text)
5. 7.443 7.443 ↑ 1.0 485 1

Seq Scan on d_inv_admin_audit_trail (cost=0.00..380.52 rows=496 width=379) (actual time=0.283..7.443 rows=485 loops=1)

  • Filter: (((attr_1_name)::text = 'Transaction ID'::text) AND (created_on > ((now() - '1 day'::interval))::date))
  • Rows Removed by Filter: 6313
6. 2,207.637 1,497,319.892 ↑ 21.8 2,235,893 1

Hash (cost=49,446,729.21..49,446,729.21 rows=48,697,660 width=21) (actual time=1,497,319.892..1,497,319.892 rows=2,235,893 loops=1)

  • Buckets: 2048 Batches: 4096 Memory Usage: 62kB
7. 823,174.565 1,495,112.255 ↑ 21.8 2,235,893 1

Hash Join (cost=6,323.59..49,446,729.21 rows=48,697,660 width=21) (actual time=1,483,197.434..1,495,112.255 rows=2,235,893 loops=1)

  • Hash Cond: (atti.alert_sk = alert_summary_sna.alert_sk)
8. 671,930.952 671,930.952 ↑ 1.0 2,318,525,441 1

Seq Scan on alert_trig_transaction_items atti (cost=0.00..40,254,700.74 rows=2,319,660,874 width=29) (actual time=0.029..671,930.952 rows=2,318,525,441 loops=1)

9. 0.596 6.738 ↓ 1.6 535 1

Hash (cost=6,319.39..6,319.39 rows=336 width=8) (actual time=6.738..6.738 rows=535 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
10. 6.142 6.142 ↓ 1.6 535 1

Index Scan using idx_ass_actentid_idx1 on alert_summary_sna (cost=0.54..6,319.39 rows=336 width=8) (actual time=0.185..6.142 rows=535 loops=1)

  • Index Cond: ((actionableentityid)::text = 'MR1408291149677871_Fraud_DRC'::text)
  • Filter: ((alert_status_cd)::text = 'ACT'::text)
11. 642.912 1,467,643.988 ↓ 0.0 0 1

Hash Join (cost=59,561.60..49,865,225.19 rows=2,042 width=344) (actual time=1,467,643.988..1,467,643.988 rows=0 loops=1)

  • Hash Cond: ((atti_1.transaction_key)::text = (h_inv_admin_audit_trail.attr_1_value)::text)
12. 815,103.668 1,466,211.160 ↑ 21.8 2,235,893 1

Hash Join (cost=6,323.59..49,446,729.21 rows=48,697,660 width=21) (actual time=1,454,203.781..1,466,211.160 rows=2,235,893 loops=1)

  • Hash Cond: (atti_1.alert_sk = alert_summary_sna_1.alert_sk)
13. 651,093.541 651,093.541 ↑ 1.0 2,318,525,441 1

Seq Scan on alert_trig_transaction_items atti_1 (cost=0.00..40,254,700.74 rows=2,319,660,874 width=29) (actual time=0.085..651,093.541 rows=2,318,525,441 loops=1)

14. 0.491 13.951 ↓ 1.6 535 1

Hash (cost=6,319.39..6,319.39 rows=336 width=8) (actual time=13.951..13.951 rows=535 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
15. 13.460 13.460 ↓ 1.6 535 1

Index Scan using idx_ass_actentid_idx1 on alert_summary_sna alert_summary_sna_1 (cost=0.54..6,319.39 rows=336 width=8) (actual time=7.392..13.460 rows=535 loops=1)

  • Index Cond: ((actionableentityid)::text = 'MR1408291149677871_Fraud_DRC'::text)
  • Filter: ((alert_status_cd)::text = 'ACT'::text)
16. 0.045 789.916 ↓ 1.2 5 1

Hash (cost=53,237.96..53,237.96 rows=4 width=344) (actual time=789.916..789.916 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
17. 789.871 789.871 ↓ 1.2 5 1

Seq Scan on h_inv_admin_audit_trail (cost=0.00..53,237.96 rows=4 width=344) (actual time=789.383..789.871 rows=5 loops=1)

  • Filter: (((attr_1_name)::text = 'Transaction ID'::text) AND (created_on > ((now() - '1 day'::interval))::date))
  • Rows Removed by Filter: 1000577