explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gF1v

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=6,629,284.34..6,629,286.25 rows=762 width=512) (actual rows= loops=)

  • Sort Key: md_upload_trans.test_id DESC, md_upload_trans.local_source_id, md_upload_trans.transaction_seq_no
2. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,629,240.25..6,629,247.87 rows=762 width=512) (actual rows= loops=)

  • Group Key: cs_ius_transaction_data.property_id, cs_ius_transaction_data.property_index, cs_ius_transaction_data.property_value, md_upload_trans.transaction_seq_no, md_upload_trans.object_type, md_upload_trans.object_id, md_upload_trans.action, md_upload_trans.priority, md_upload_trans.time_stamp, md_upload_trans.test_id, md_upload_trans.status_id, md_upload_trans.system_id, md_upload_trans.local_source_id, cs_ius_transaction.time_stamp_captured, md_proc_step_instance.process_step_id, (pkgiusmetadatastatus.getinitialstatus('T'::text, '23001'::double precision)), (pkgiusmetadatastatus.getinprocessstatus('T'::text, '3001'::double precision)), ((SubPlan 1)), (pkgiusmetadatastatus.getsuccessstatus('R'::text, '3001'::double precision)), (pkgiusmetadatastatus.getfailurestatus('R'::text, '3001'::double precision)), (pkgiusmetadatastatus.getsuccessstatus('E'::text, '3001'::double precision)), (pkgiusmetadatastatus.getfailurestatus('E'::text, '3001'::double precision))
3. 0.000 0.000 ↓ 0.0

Append (cost=4,860,059.70..6,629,198.34 rows=762 width=512) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Result (cost=4,860,059.70..6,629,190.70 rows=761 width=211) (actual rows= loops=)

  • One-Time Filter: (750000 > $2)
5.          

Initplan (for Result)

6. 0.000 0.000 ↓ 0.0

Aggregate (cost=12.97..12.98 rows=1 width=8) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=2.73..12.96 rows=6 width=0) (actual rows= loops=)

  • Hash Cond: (tmp_result_set.local_source_id = md_proc_step_l_source_config_1.local_source_id)
8. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_result_set (cost=0.00..10.10 rows=10 width=18) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash (cost=2.65..2.65 rows=6 width=4) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on proc_step_l_source_config md_proc_step_l_source_config_1 (cost=0.00..2.65 rows=6 width=4) (actual rows= loops=)

  • Filter: (proc_step_instance_id = 3001.000000000000000)
11. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=4,860,059.70..6,629,190.70 rows=761 width=211) (actual rows= loops=)

  • Hash Cond: (md_upload_trans.test_id = md_test_1.test_id)
  • Join Filter: ((md_test_1.status_id)::double precision <> ALL (ARRAY[(pkgiusmetadatastatus.getinitialstatus('T'::text, '23001'::double precision)), (pkgiusmetadatastatus.getinprocessstatus('T'::text, '3001'::double precision))]))
12. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=4,859,286.66..6,627,312.22 rows=761 width=164) (actual rows= loops=)

  • Join Filter: (alc.test_id = md_upload_trans.test_id)
13. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=4,859,286.66..6,546,768.74 rows=880 width=164) (actual rows= loops=)

  • Hash Cond: (md_upload_trans.test_id = wh_test.test_id)
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,856,712.65..6,544,183.61 rows=882 width=164) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,856,712.08..5,430,422.06 rows=347 width=158) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,856,711.51..5,425,495.60 rows=613 width=128) (actual rows= loops=)

  • Join Filter: (md_proc_step_l_source_config.local_source_id = md_upload_trans.local_source_id)
17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..18.40 rows=6 width=57) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using ix_proc_step_l_s_local_source_ on proc_step_l_source_config md_proc_step_l_source_config (cost=0.14..14.79 rows=6 width=9) (actual rows= loops=)

  • Filter: ((proc_step_instance_id = 3001.000000000000000) AND (status = '1'::numeric))
19. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..3.55 rows=1 width=59) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..3.54 rows=1 width=59) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on proc_step_instance md_proc_step_instance (cost=0.00..1.51 rows=1 width=11) (actual rows= loops=)

  • Filter: (proc_step_instance_id = 3001.000000000000000)
22. 0.000 0.000 ↓ 0.0

Limit (cost=0.00..2.01 rows=1 width=64) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Result (cost=0.00..2.01 rows=1 width=64) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Materialize (cost=4,856,711.36..5,425,294.14 rows=2,092 width=71) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=4,856,711.36..5,425,283.68 rows=2,092 width=71) (actual rows= loops=)

  • Hash Cond: (md_upload_trans.test_id = csi.test_id)
26. 0.000 0.000 ↓ 0.0

Index Scan using ix_upload_trans_status_id on upload_trans md_upload_trans (cost=0.57..367,922.04 rows=138,907 width=71) (actual rows= loops=)

  • Index Cond: (status_id = ANY ('{201,301}'::numeric[]))
27. 0.000 0.000 ↓ 0.0

Hash (cost=4,044,286.27..4,044,286.27 rows=49,519,162 width=5) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash Join (cost=760.05..4,044,286.27 rows=49,519,162 width=5) (actual rows= loops=)

  • Hash Cond: (csi.test_id = md_test.test_id)
  • Join Filter: (csi.time_stamp_captured > (aws_oracle_ext.sysdate() - (((CASE md_test.local_source_id WHEN '10'::numeric THEN 0.00833333333333333333 WHEN '13'::numeric THEN 0.00833333333333333333 WHEN '16'::numeric THEN 0.00833333333333333333 WHEN '21'::numeric THEN 0.00833333333333333333 WHEN '22'::numeric THEN 0.00833333333333333333 ELSE 0.00138888888888888889 END)::text || ' days'::text))::interval))
29. 0.000 0.000 ↓ 0.0

Seq Scan on ius_transaction csi (cost=0.00..3,647,438.08 rows=150,677,008 width=13) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=438.91..438.91 rows=25,691 width=11) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on test md_test (cost=0.00..438.91 rows=25,691 width=11) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using pk_ibis_upload_trans on ius_transaction cs_ius_transaction (cost=0.57..8.04 rows=1 width=30) (actual rows= loops=)

  • Index Cond: ((local_source_id = md_upload_trans.local_source_id) AND (transaction_seq_no = md_upload_trans.transaction_seq_no))
33. 0.000 0.000 ↓ 0.0

Index Scan using pk_ibis_upload_trans_data on ius_transaction_data cs_ius_transaction_data (cost=0.57..3,176.27 rows=3,342 width=32) (actual rows= loops=)

  • Index Cond: ((local_source_id = cs_ius_transaction.local_source_id) AND (transaction_seq_no = cs_ius_transaction.transaction_seq_no))
34. 0.000 0.000 ↓ 0.0

Hash (cost=2,573.91..2,573.91 rows=8 width=6) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash Join (cost=163.08..2,573.91 rows=8 width=6) (actual rows= loops=)

  • Hash Cond: (wh_test.lifecycle_stage_term_key = wh_term.term_key)
36. 0.000 0.000 ↓ 0.0

Seq Scan on test wh_test (cost=0.00..2,349.50 rows=23,350 width=11) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=163.05..163.05 rows=2 width=7) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on term wh_term (cost=0.00..163.05 rows=2 width=7) (actual rows= loops=)

  • Filter: ((def_name)::text = ANY ('{Development,Closed}'::text[]))
39. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..72,400.62 rows=617 width=6) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on audit_log_copy alc (cost=0.00..72,397.54 rows=617 width=6) (actual rows= loops=)

  • Filter: ((capture_status)::text = ANY ('{Wait,"To Process"}'::text[]))
41. 0.000 0.000 ↓ 0.0

Hash (cost=438.91..438.91 rows=25,691 width=11) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on test md_test_1 (cost=0.00..438.91 rows=25,691 width=11) (actual rows= loops=)

43.          

SubPlan (for Hash Anti Join)

44. 0.000 0.000 ↓ 0.0

Seq Scan on validation_attribute va (cost=0.00..1.44 rows=1 width=5) (actual rows= loops=)

  • Filter: (((property_id)::text = (cs_ius_transaction_data.property_id)::text) AND ((object_type)::text = (cs_ius_transaction.object_type)::text))
45. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=512) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=484) (actual rows= loops=)