explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rJPg

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 1,692,158.093 ↓ 1.1 12 1

Sort (cost=6,621,034.94..6,621,034.96 rows=11 width=512) (actual time=1,692,158.092..1,692,158.093 rows=12 loops=1)

  • Sort Key: md_upload_trans.test_id DESC, md_upload_trans.local_source_id, md_upload_trans.transaction_seq_no
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=1085419 read=799289, temp read=347 written=299
2. 0.036 1,692,158.056 ↓ 1.1 12 1

Aggregate (cost=6,621,034.64..6,621,034.75 rows=11 width=512) (actual time=1,692,158.05..1,692,158.056 rows=12 loops=1)

  • Buffers: shared hit=1085416 read=799289, temp read=347 written=299
3. 0.004 1,692,158.020 ↓ 1.1 12 1

Append (cost=4,930,517.55..6,621,034.03 rows=11 width=512) (actual time=1,692,154.103..1,692,158.02 rows=12 loops=1)

  • Buffers: shared hit=1085416 read=799289, temp read=347 written=299
4. 0.005 1,692,158.011 ↓ 1.1 11 1

Result (cost=4,930,517.55..6,621,033.91 rows=10 width=211) (actual time=1,692,154.103..1,692,158.011 rows=11 loops=1)

  • Buffers: shared hit=1085416 read=799289, temp read=347 written=299
5.          

Initplan (for Result)

6. 0.001 0.003 ↑ 1.0 1 1

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

7. 0.001 0.002 ↓ 0.0 0 1

Hash Join (cost=2.73..12.96 rows=6 width=0) (actual time=0.002..0.002 rows=0 loops=1)

8. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on tmp_result_set tmp_result_set (cost=0..10.1 rows=10 width=18) (actual time=0.001..0.001 rows=0 loops=1)

9. 0.000 0.000 ↓ 0.0 0 0

Hash (cost=2.65..2.65 rows=6 width=4) (never executed)

10. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on proc_step_l_source_config md_proc_step_l_source_config_1 (cost=0..2.65 rows=6 width=4) (never executed)

  • Filter: (md_proc_step_l_source_config_1.proc_step_instance_id = 3001.000000000000000)
11. 0.037 1,692,158.003 ↓ 1.1 11 1

Nested Loop (cost=4,930,517.55..6,621,033.91 rows=10 width=211) (actual time=1,692,154.097..1,692,158.003 rows=11 loops=1)

  • Buffers: shared hit=1085416 read=799289, temp read=347 written=299
12. 0.010 1,692,157.900 ↓ 1.1 11 1

Nested Loop (cost=4,930,504.28..6,620,970.22 rows=10 width=164) (actual time=1,692,154.066..1,692,157.9 rows=11 loops=1)

  • Buffers: shared hit=1085372 read=799289, temp read=347 written=299
13. 0.489 1,692,157.780 ↓ 1.1 11 1

Hash Join (cost=4,930,503.71..6,620,874.85 rows=10 width=164) (actual time=1,692,154.015..1,692,157.78 rows=11 loops=1)

  • Buffers: shared hit=1085306 read=799289, temp read=347 written=299
14. 0.008 1,691,703.009 ↑ 74.5 11 1

Nested Loop (cost=4,856,712.65..6,544,112.92 rows=820 width=164) (actual time=1,691,699.249..1,691,703.009 rows=11 loops=1)

  • Buffers: shared hit=1042464 read=799289, temp read=347 written=299
15. 0.007 1,691,702.968 ↑ 347.0 1 1

Nested Loop (cost=4,856,712.08..5,430,416.52 rows=347 width=154) (actual time=1,691,699.216..1,691,702.968 rows=1 loops=1)

  • Buffers: shared hit=1042459 read=799289, temp read=347 written=299
16. 24.943 1,691,702.931 ↑ 613.0 1 1

Nested Loop (cost=4,856,711.51..5,425,490.04 rows=613 width=128) (actual time=1,691,699.179..1,691,702.931 rows=1 loops=1)

  • Buffers: shared hit=1042454 read=799289, temp read=347 written=299
17. 0.010 5.744 ↑ 1.0 6 1

Nested Loop (cost=0.14..18.4 rows=6 width=57) (actual time=5.681..5.744 rows=6 loops=1)

  • Buffers: shared hit=345
18. 0.070 0.070 ↑ 1.0 6 1

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 time=0.027..0.07 rows=6 loops=1)

  • Filter: ((md_proc_step_l_source_config.proc_step_instance_id = 3001.000000000000000) AND (md_proc_step_l_source_config.status = '1'::numeric))
  • Buffers: shared hit=2
19. 0.005 5.664 ↑ 1.0 1 6

Materialize (cost=0..3.55 rows=1 width=59) (actual time=0.942..0.944 rows=1 loops=6)

  • Buffers: shared hit=343
20. 0.003 5.659 ↑ 1.0 1 1

Nested Loop (cost=0..3.54 rows=1 width=59) (actual time=5.648..5.659 rows=1 loops=1)

  • Buffers: shared hit=343
21. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on proc_step_instance md_proc_step_instance (cost=0..1.51 rows=1 width=11) (actual time=0.006..0.014 rows=1 loops=1)

  • Filter: (md_proc_step_instance.proc_step_instance_id = 3001.000000000000000)
  • Buffers: shared hit=1
22. 0.002 5.642 ↑ 1.0 1 1

Limit (cost=0..2.01 rows=1 width=64) (actual time=5.641..5.642 rows=1 loops=1)

  • Buffers: shared hit=342
23. 5.640 5.640 ↑ 1.0 1 1

Result (cost=0..2.01 rows=1 width=64) (actual time=5.64..5.64 rows=1 loops=1)

  • Buffers: shared hit=342
24. 11.677 1,691,672.244 ↓ 11.4 23,865 6

Materialize (cost=4,856,711.36..5,425,288.59 rows=2,092 width=71) (actual time=281,941.529..281,945.374 rows=23,865 loops=6)

  • Buffers: shared hit=1042109 read=799289, temp read=347 written=299
25. 22.880 1,691,660.567 ↓ 11.4 23,865 1

Hash Join (cost=4,856,711.36..5,425,278.13 rows=2,092 width=71) (actual time=1,691,649.168..1,691,660.567 rows=23,865 loops=1)

  • Buffers: shared hit=1042109 read=799289, temp read=347 written=299
26. 11.619 11.619 ↑ 5.8 23,865 1

Index Scan using ix_upload_trans_status_id on upload_trans md_upload_trans (cost=0.57..367,916.55 rows=138,905 width=71) (actual time=0.059..11.619 rows=23,865 loops=1)

  • Index Cond: (md_upload_trans.status_id = ANY ('{201,301}'::numeric[]))
  • Buffers: shared hit=92
27. 0.000 1,691,626.068 ↓ 0.0 0 1

Hash (cost=4,044,286.27..4,044,286.27 rows=49,519,162 width=5) (actual time=1,691,626.068..1,691,626.068 rows=0 loops=1)

  • Buffers: shared hit=1042017 read=799289
28. 1,664,926.818 1,691,626.068 ↓ 0.0 0 1

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

  • Buffers: shared hit=1042017 read=799289
29. 26,690.112 26,690.112 ↓ 1.0 150,677,015 1

Seq Scan on ius_transaction csi (cost=0..3,647,438.08 rows=150,677,008 width=13) (actual time=0.019..26,690.112 rows=150,677,015 loops=1)

  • Buffers: shared hit=1041835 read=799289
30. 5.472 9.138 ↓ 1.0 25,693 1

Hash (cost=438.91..438.91 rows=25,691 width=11) (actual time=9.138..9.138 rows=25,693 loops=1)

  • Buffers: shared hit=182
31. 3.666 3.666 ↓ 1.0 25,693 1

Seq Scan on test md_test (cost=0..438.91 rows=25,691 width=11) (actual time=0.004..3.666 rows=25,693 loops=1)

  • Buffers: shared hit=182
32. 0.030 0.030 ↑ 1.0 1 1

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

  • Index Cond: ((cs_ius_transaction.local_source_id = md_upload_trans.local_source_id) AND (cs_ius_transaction.transaction_seq_no = md_upload_trans.transaction_seq_no))
  • Buffers: shared hit=5
33. 0.033 0.033 ↑ 303.8 11 1

Index Scan using pk_ibis_upload_trans_data on ius_transaction_data cs_ius_transaction_data (cost=0.57..3,176.08 rows=3,342 width=32) (actual time=0.029..0.033 rows=11 loops=1)

  • Index Cond: ((cs_ius_transaction_data.local_source_id = cs_ius_transaction.local_source_id) AND (cs_ius_transaction_data.transaction_seq_no = cs_ius_transaction.transaction_seq_no))
  • Buffers: shared hit=5
34. 0.056 454.282 ↓ 0.0 0 1

Hash (cost=71,940.55..71,940.55 rows=112,761 width=6) (actual time=454.282..454.282 rows=0 loops=1)

  • Buffers: shared hit=42842
35. 454.226 454.226 ↑ 113.3 995 1

Seq Scan on audit_log_copy alc (cost=0..71,940.55 rows=112,761 width=6) (actual time=453.8..454.226 rows=995 loops=1)

  • Filter: ((alc.capture_status)::text = ANY ('{Wait,"To Process"}'::text[]))
  • Buffers: shared hit=42842
36. 0.022 0.110 ↓ 0.0 0 11

Nested Loop (cost=0.57..9.53 rows=1 width=6) (actual time=0.01..0.01 rows=0 loops=11)

  • Buffers: shared hit=66
37. 0.044 0.044 ↑ 1.0 1 11

Index Scan using test_uk1 on test wh_test (cost=0.29..3.63 rows=1 width=11) (actual time=0.004..0.004 rows=1 loops=11)

  • Index Cond: (wh_test.test_id = md_upload_trans.test_id)
  • Buffers: shared hit=33
38. 0.044 0.044 ↓ 0.0 0 11

Index Scan using term_pk on term wh_term (cost=0.28..3.65 rows=1 width=7) (actual time=0.004..0.004 rows=0 loops=11)

  • Index Cond: (wh_term.term_key = wh_test.lifecycle_stage_term_key)
  • Filter: ((wh_term.def_name)::text = ANY ('{Development,Closed}'::text[]))
  • Buffers: shared hit=33
39. 0.033 0.033 ↑ 1.0 1 11

Index Scan using pk_test on test md_test_1 (cost=0.29..3.62 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: (md_test_1.test_id = md_upload_trans.test_id)
  • Buffers: shared hit=33
40.          

SubPlan (for Nested Loop)

41. 0.033 0.033 ↑ 1.0 1 11

Seq Scan on validation_attribute va (cost=0..1.44 rows=1 width=5) (actual time=0.002..0.003 rows=1 loops=11)

  • Filter: (((va.property_id)::text = (cs_ius_transaction_data.property_id)::text) AND ((va.object_type)::text = (cs_ius_transaction.object_type)::text))
  • Buffers: shared hit=11
42. 0.004 0.005 ↑ 1.0 1 1

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

43. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0..0.01 rows=1 width=484) (actual time=0.001..0.001 rows=1 loops=1)

Planning time : 8.336 ms
Execution time : 1,692,160.181 ms