explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ku2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 56,401.535 ↓ 16.0 48 1

Sort (cost=5,107,162.70..5,107,162.71 rows=3 width=348) (actual time=56,401.525..56,401.535 rows=48 loops=1)

  • Sort Key: psdal.log_id
  • Sort Method: quicksort Memory: 37kB
2. 13,542.723 56,401.448 ↓ 16.0 48 1

Append (cost=83.10..5,107,162.64 rows=3 width=143) (actual time=0.891..56,401.448 rows=48 loops=1)

3. 0.062 1.542 ↓ 32.0 32 1

Nested Loop (cost=83.10..98.42 rows=1 width=79) (actual time=0.889..1.542 rows=32 loops=1)

  • Join Filter: (psdal.section_detail_id = psf.section_detail_id)
4. 0.103 1.072 ↓ 68.0 68 1

Nested Loop (cost=82.53..94.58 rows=1 width=87) (actual time=0.425..1.072 rows=68 loops=1)

5. 0.193 0.549 ↓ 70.0 70 1

Bitmap Heap Scan on patient_section_details_audit_log psdal (cost=81.96..85.98 rows=1 width=83) (actual time=0.394..0.549 rows=70 loops=1)

  • Recheck Cond: (((patient_id)::text = 'OP2450422'::text) AND ((mr_no)::text = 'LLM151481'::text))
  • Filter: ((field_name <> 'section_detail_id'::text) AND (section_id > 0))
  • Rows Removed by Filter: 59
6. 0.006 0.356 ↓ 0.0 0 1

BitmapAnd (cost=81.96..81.96 rows=1 width=0) (actual time=0.356..0.356 rows=0 loops=1)

7. 0.135 0.135 ↑ 9.2 129 1

Bitmap Index Scan on patient_section_details_audit_log_patient_id_idx (cost=0.00..37.42 rows=1,181 width=0) (actual time=0.135..0.135 rows=129 loops=1)

  • Index Cond: ((patient_id)::text = 'OP2450422'::text)
8. 0.215 0.215 ↑ 3.1 507 1

Bitmap Index Scan on patient_section_details_audit_log_mr_no_idx (cost=0.00..44.29 rows=1,563 width=0) (actual time=0.215..0.215 rows=507 loops=1)

  • Index Cond: ((mr_no)::text = 'LLM151481'::text)
9. 0.420 0.420 ↑ 1.0 1 70

Index Scan using patient_section_details_pkey on patient_section_details psd (cost=0.57..8.59 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=70)

  • Index Cond: (section_detail_id = psdal.section_detail_id)
  • Filter: (item_type = 'CONS'::text)
  • Rows Removed by Filter: 0
10. 0.408 0.408 ↓ 0.0 0 68

Index Scan using psf_section_detail_id_idx on patient_section_forms psf (cost=0.57..3.83 rows=1 width=4) (actual time=0.005..0.006 rows=0 loops=68)

  • Index Cond: (section_detail_id = psd.section_detail_id)
  • Filter: ((form_type)::text = 'Form_CONS'::text)
  • Rows Removed by Filter: 1
11. 0.016 42,857.183 ↓ 16.0 16 1

Subquery Scan on *SELECT* 2 (cost=2,623,620.59..2,623,620.61 rows=1 width=175) (actual time=42,857.156..42,857.183 rows=16 loops=1)

12. 0.315 42,857.167 ↓ 16.0 16 1

HashAggregate (cost=2,623,620.59..2,623,620.60 rows=1 width=175) (actual time=42,857.149..42,857.167 rows=16 loops=1)

13. 5.129 42,856.852 ↓ 95.0 95 1

Nested Loop (cost=31.68..2,623,620.56 rows=1 width=175) (actual time=8,246.641..42,856.852 rows=95 loops=1)

  • Join Filter: (psd_1.section_id = sm.section_id)
  • Rows Removed by Join Filter: 12540
14. 0.647 42,851.723 ↓ 95.0 95 1

Nested Loop (cost=31.68..2,623,615.56 rows=1 width=157) (actual time=8,246.588..42,851.723 rows=95 loops=1)

15. 8,430.900 42,851.076 ↓ 182.0 182 1

Hash Join (cost=31.11..2,623,611.32 rows=1 width=161) (actual time=8,246.146..42,851.076 rows=182 loops=1)

  • Hash Cond: (psval.section_detail_id = psd_1.section_detail_id)
16. 34,419.826 34,419.826 ↓ 1.0 37,838,666 1

Seq Scan on patient_section_fields_audit_log psval (cost=0.00..2,483,425.66 rows=37,374,545 width=129) (actual time=0.020..34,419.826 rows=37,838,666 loops=1)

  • Filter: (((operation)::text = 'INSERT'::text) AND (field_name = 'section_detail_id'::text))
  • Rows Removed by Filter: 49201239
17. 0.350 0.350 ↓ 42.0 42 1

Hash (cost=31.10..31.10 rows=1 width=32) (actual time=0.350..0.350 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
18. 0.000 0.325 ↓ 42.0 42 1

Bitmap Heap Scan on patient_section_details psd_1 (cost=27.08..31.10 rows=1 width=32) (actual time=0.295..0.325 rows=42 loops=1)

  • Recheck Cond: (((patient_id)::text = 'OP2450422'::text) AND ((mr_no)::text = 'LLM151481'::text))
  • Filter: (item_type = 'CONS'::text)
  • Rows Removed by Filter: 1
19. 0.004 0.285 ↓ 0.0 0 1

BitmapAnd (cost=27.08..27.08 rows=1 width=0) (actual time=0.285..0.285 rows=0 loops=1)

20. 0.101 0.101 ↑ 5.1 43 1

Bitmap Index Scan on psd_patient_id_idx (cost=0.00..10.23 rows=221 width=0) (actual time=0.101..0.101 rows=43 loops=1)

  • Index Cond: ((patient_id)::text = 'OP2450422'::text)
21. 0.180 0.180 ↑ 2.0 263 1

Bitmap Index Scan on psd_mr_no_idx (cost=0.00..16.60 rows=538 width=0) (actual time=0.180..0.180 rows=263 loops=1)

  • Index Cond: ((mr_no)::text = 'LLM151481'::text)
22. 0.546 0.546 ↑ 1.0 1 182

Index Scan using psf_section_detail_id_idx on patient_section_forms psf_1 (cost=0.57..4.23 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=182)

  • Index Cond: (section_detail_id = psval.section_detail_id)
  • Filter: ((form_type)::text = 'Form_CONS'::text)
  • Rows Removed by Filter: 0
23. 2.280 2.280 ↑ 1.0 133 95

Seq Scan on section_master sm (cost=0.00..3.33 rows=133 width=22) (actual time=0.001..0.024 rows=133 loops=95)

24. 0.006 13,542.692 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=2,483,443.58..2,483,443.60 rows=1 width=175) (actual time=13,542.692..13,542.692 rows=0 loops=1)

25. 0.007 13,542.686 ↓ 0.0 0 1

HashAggregate (cost=2,483,443.58..2,483,443.59 rows=1 width=175) (actual time=13,542.686..13,542.686 rows=0 loops=1)

26. 0.004 13,542.679 ↓ 0.0 0 1

Nested Loop (cost=1.14..2,483,443.54 rows=1 width=175) (actual time=13,542.679..13,542.679 rows=0 loops=1)

  • Join Filter: (psd_2.section_id = sm_1.section_id)
27. 0.005 13,542.675 ↓ 0.0 0 1

Nested Loop (cost=1.14..2,483,438.55 rows=1 width=157) (actual time=13,542.675..13,542.675 rows=0 loops=1)

  • Join Filter: (psval_1.section_detail_id = psd_2.section_detail_id)
28. 0.005 13,542.670 ↓ 0.0 0 1

Nested Loop (cost=0.57..2,483,434.26 rows=1 width=133) (actual time=13,542.670..13,542.670 rows=0 loops=1)

29. 13,542.665 13,542.665 ↓ 0.0 0 1

Seq Scan on patient_section_fields_audit_log psval_1 (cost=0.00..2,483,425.66 rows=1 width=129) (actual time=13,542.665..13,542.665 rows=0 loops=1)

  • Filter: (((operation)::text = 'ChangeLog'::text) AND (field_name = 'section_detail_id'::text))
  • Rows Removed by Filter: 87039905
30. 0.000 0.000 ↓ 0.0 0

Index Scan using psf_section_detail_id_idx on patient_section_forms psf_2 (cost=0.57..8.59 rows=1 width=4) (never executed)

  • Index Cond: (section_detail_id = psval_1.section_detail_id)
  • Filter: ((form_type)::text = 'Form_CONS'::text)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_section_details_pkey on patient_section_details psd_2 (cost=0.57..4.28 rows=1 width=32) (never executed)

  • Index Cond: (section_detail_id = psf_2.section_detail_id)
  • Filter: ((item_type = 'CONS'::text) AND ((patient_id)::text = 'OP2450422'::text) AND ((mr_no)::text = 'LLM151481'::text))
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on section_master sm_1 (cost=0.00..3.33 rows=133 width=22) (never executed)