explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i62r

Settings
# exclusive inclusive rows x rows loops node
1. 0.120 5.681 ↓ 3.1 52 1

Sort (cost=388.69..388.73 rows=17 width=350) (actual time=5.678..5.681 rows=52 loops=1)

  • Sort Key: pc.patient_package_id, (CASE WHEN pac.is_customized_package THEN pc.display_order ELSE pmc.display_order END), pc.patient_package_content_id
  • Sort Method: quicksort Memory: 42kB
2. 1.476 5.561 ↓ 3.1 52 1

Hash Right Join (cost=189.67..388.34 rows=17 width=350) (actual time=3.857..5.561 rows=52 loops=1)

  • Hash Cond: (((sid.medicine_id)::character varying)::text = (pc.activity_id)::text)
  • Join Filter: ((pc.activity_type)::text = 'Inventory'::text)
3. 0.500 0.500 ↑ 1.0 3,818 1

Seq Scan on store_item_details sid (cost=0.00..128.18 rows=3,818 width=21) (actual time=0.004..0.500 rows=3,818 loops=1)

4. 0.069 3.585 ↓ 3.7 52 1

Hash (cost=189.50..189.50 rows=14 width=333) (actual time=3.585..3.585 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
5. 0.014 3.516 ↓ 3.7 52 1

Nested Loop Left Join (cost=109.54..189.50 rows=14 width=333) (actual time=1.289..3.516 rows=52 loops=1)

6. 0.055 3.450 ↓ 3.7 52 1

Nested Loop Left Join (cost=109.40..187.07 rows=14 width=309) (actual time=1.287..3.450 rows=52 loops=1)

7. 0.079 3.343 ↓ 3.7 52 1

Nested Loop Left Join (cost=109.25..184.49 rows=14 width=275) (actual time=1.280..3.343 rows=52 loops=1)

8. 0.042 3.160 ↓ 3.7 52 1

Nested Loop Left Join (cost=108.98..179.53 rows=14 width=248) (actual time=1.273..3.160 rows=52 loops=1)

9. 0.060 2.962 ↓ 3.7 52 1

Nested Loop Left Join (cost=108.70..173.82 rows=14 width=229) (actual time=1.266..2.962 rows=52 loops=1)

  • Join Filter: ((pc.activity_type)::text = 'Operation'::text)
10. 0.037 2.850 ↓ 3.7 52 1

Hash Join (cost=108.55..171.24 rows=14 width=205) (actual time=1.259..2.850 rows=52 loops=1)

  • Hash Cond: (pp.pat_package_id = pac.patient_package_id)
11. 0.039 2.745 ↓ 26.0 52 1

Nested Loop Left Join (cost=103.38..166.05 rows=2 width=190) (actual time=1.180..2.745 rows=52 loops=1)

12. 0.051 2.602 ↓ 26.0 52 1

Nested Loop Left Join (cost=103.24..165.69 rows=2 width=173) (actual time=1.172..2.602 rows=52 loops=1)

13. 0.018 2.551 ↓ 26.0 52 1

Nested Loop Left Join (cost=103.09..165.32 rows=2 width=159) (actual time=1.169..2.551 rows=52 loops=1)

14. 1.114 2.377 ↓ 26.0 52 1

Nested Loop (cost=102.82..164.64 rows=2 width=155) (actual time=1.159..2.377 rows=52 loops=1)

  • Join Filter: (pp.package_id = pm.package_id)
  • Rows Removed by Join Filter: 104
15. 0.226 1.159 ↓ 26.0 52 1

Hash Join (cost=102.82..156.48 rows=2 width=134) (actual time=1.112..1.159 rows=52 loops=1)

  • Hash Cond: (pcc.patient_package_content_id = pc.patient_package_content_id)
16. 0.188 0.188 ↑ 1.0 1,918 1

Seq Scan on patient_package_content_charges pcc (cost=0.00..46.38 rows=1,938 width=7) (actual time=0.003..0.188 rows=1,918 loops=1)

17. 0.036 0.745 ↓ 32.5 65 1

Hash (cost=102.79..102.79 rows=2 width=131) (actual time=0.745..0.745 rows=65 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
18. 0.290 0.709 ↓ 32.5 65 1

Hash Join (cost=31.01..102.79 rows=2 width=131) (actual time=0.672..0.709 rows=65 loops=1)

  • Hash Cond: (pc.patient_package_id = pp.pat_package_id)
19. 0.279 0.279 ↓ 1.0 2,108 1

Seq Scan on patient_package_contents pc (cost=0.00..63.92 rows=2,092 width=123) (actual time=0.003..0.279 rows=2,108 loops=1)

20. 0.001 0.140 ↓ 5.0 5 1

Hash (cost=31.00..31.00 rows=1 width=8) (actual time=0.140..0.140 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
21. 0.139 0.139 ↓ 5.0 5 1

Seq Scan on patient_packages pp (cost=0.00..31.00 rows=1 width=8) (actual time=0.047..0.139 rows=5 loops=1)

  • Filter: (((status)::text <> ALL ('{X,C}'::text[])) AND ((mr_no)::text = 'MR013746'::text))
  • Rows Removed by Filter: 1251
22. 0.051 0.104 ↓ 1.5 3 52

Materialize (cost=0.00..8.10 rows=2 width=25) (actual time=0.001..0.002 rows=3 loops=52)

23. 0.053 0.053 ↓ 1.5 3 1

Seq Scan on packages pm (cost=0.00..8.09 rows=2 width=25) (actual time=0.039..0.053 rows=3 loops=1)

  • Filter: ((status = 'A'::bpchar) AND (package_id = ANY ('{301,326,339}'::integer[])))
  • Rows Removed by Filter: 206
24. 0.156 0.156 ↑ 1.0 1 52

Index Scan using package_contents_pkey on package_contents pmc (cost=0.28..0.33 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=52)

  • Index Cond: (package_content_id = pc.package_content_id)
25. 0.000 0.000 ↓ 0.0 0 52

Index Scan using packages_pkey on packages pap (cost=0.14..0.18 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=52)

  • Index Cond: (package_id = pc.panel_id)
26. 0.104 0.104 ↑ 1.0 1 52

Index Scan using chargehead_constants_pkey on chargehead_constants cc (cost=0.14..0.17 rows=1 width=23) (actual time=0.001..0.002 rows=1 loops=52)

  • Index Cond: ((chargehead_id)::text = (pc.charge_head)::text)
27. 0.019 0.068 ↓ 1.0 145 1

Hash (cost=3.41..3.41 rows=141 width=23) (actual time=0.068..0.068 rows=145 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.049 0.049 ↓ 1.0 145 1

Seq Scan on patient_customised_package_details pac (cost=0.00..3.41 rows=141 width=23) (actual time=0.009..0.049 rows=145 loops=1)

29. 0.052 0.052 ↓ 0.0 0 52

Index Scan using operation_master_pkey on operation_master om (cost=0.14..0.17 rows=1 width=33) (actual time=0.001..0.001 rows=0 loops=52)

  • Index Cond: ((op_id)::text = (pc.activity_id)::text)
30. 0.156 0.156 ↑ 1.0 1 52

Index Scan using entity_id_orderable_item_index on orderable_item oi (cost=0.28..0.40 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=52)

  • Index Cond: ((entity_id)::text = (pc.activity_id)::text)
  • Filter: ((entity)::text = (pc.activity_type)::text)
31. 0.104 0.104 ↓ 0.0 0 52

Index Scan using diagnostics_pkey on diagnostics test (cost=0.28..0.34 rows=1 width=35) (actual time=0.002..0.002 rows=0 loops=52)

  • Index Cond: ((test_id)::text = (pc.activity_id)::text)
32. 0.052 0.052 ↓ 0.0 0 52

Index Scan using services_pkey on services s (cost=0.14..0.17 rows=1 width=43) (actual time=0.001..0.001 rows=0 loops=52)

  • Index Cond: ((pc.activity_id)::text = (service_id)::text)
33. 0.052 0.052 ↓ 0.0 0 52

Index Scan using consultation_types_pkey on consultation_types ct (cost=0.14..0.16 rows=1 width=28) (actual time=0.000..0.001 rows=0 loops=52)

  • Index Cond: (consultation_type_id = pc.consultation_type_id)
Total runtime : 10.979 ms