explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xfN7

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 7,720.876 ↓ 0.0 0 1

GroupAggregate (cost=465,664.12..757,603.23 rows=1 width=37) (actual time=7,720.875..7,720.876 rows=0 loops=1)

  • Group Key: spd.medicine_id, ip.priority
2. 0.001 7,720.872 ↓ 0.0 0 1

Nested Loop (cost=465,664.12..757,603.19 rows=1 width=37) (actual time=7,720.872..7,720.872 rows=0 loops=1)

3. 0.005 7,720.871 ↓ 0.0 0 1

Nested Loop (cost=465,663.69..757,598.69 rows=1 width=41) (actual time=7,720.871..7,720.871 rows=0 loops=1)

4. 123.289 7,720.866 ↓ 0.0 0 1

Hash Join (cost=465,663.26..757,595.19 rows=1 width=50) (actual time=7,720.866..7,720.866 rows=0 loops=1)

  • Hash Cond: (sipd_1.indent_item_id = sipd.indent_item_id)
5. 1,040.265 7,597.526 ↓ 1.1 1,678,929 1

Finalize GroupAggregate (cost=465,645.27..707,779.32 rows=1,475,492 width=90) (actual time=4,555.922..7,597.526 rows=1,678,929 loops=1)

  • Group Key: sipd_1.patient_indent_no, sipd_1.status, sipd_1.indent_item_id, ip.priority, ip.verbal_order
6. 1,658.724 6,557.261 ↓ 1.4 1,712,661 1

Gather Merge (cost=465,645.27..638,308.25 rows=1,229,576 width=49) (actual time=4,555.911..6,557.261 rows=1,712,661 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 554.771 4,898.537 ↑ 1.1 570,887 3 / 3

Partial GroupAggregate (cost=464,645.25..495,384.65 rows=614,788 width=49) (actual time=4,264.769..4,898.537 rows=570,887 loops=3)

  • Group Key: sipd_1.patient_indent_no, sipd_1.status, sipd_1.indent_item_id, ip.priority, ip.verbal_order
8. 1,542.357 4,343.766 ↑ 1.1 584,396 3 / 3

Sort (cost=464,645.25..466,182.22 rows=614,788 width=21) (actual time=4,264.737..4,343.766 rows=584,396 loops=3)

  • Sort Key: sipd_1.patient_indent_no, sipd_1.status, sipd_1.indent_item_id, ip.verbal_order
  • Sort Method: quicksort Memory: 69609kB
9. 273.248 2,801.409 ↑ 1.1 584,396 3 / 3

Merge Join (cost=11,230.94..405,534.21 rows=614,788 width=21) (actual time=57.539..2,801.409 rows=584,396 loops=3)

  • Merge Cond: (ip.prescription_id = sipd_1.patient_presc_id)
10. 1,593.350 1,593.350 ↑ 1.3 1,247,000 3 / 3

Parallel Index Scan using ip_prescription_pkey on ip_prescription ip (cost=0.43..269,051.95 rows=1,561,966 width=6) (actual time=0.089..1,593.350 rows=1,247,000 loops=3)

  • Filter: ((priority)::text = 'R'::text)
  • Rows Removed by Filter: 523935
11. 934.811 934.811 ↓ 1.0 2,091,067 3 / 3

Index Scan using idx_store_indent_prescription_details_patient_presc_id on store_indent_prescription_details sipd_1 (cost=0.43..109,066.81 rows=2,089,603 width=23) (actual time=0.104..934.811 rows=2,091,067 loops=3)

  • Filter: ((patient_indent_no)::text <> 'PI1162238'::text)
  • Rows Removed by Filter: 10
12. 0.007 0.051 ↑ 1.0 1 1

Hash (cost=17.95..17.95 rows=1 width=25) (actual time=0.051..0.051 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.005 0.044 ↑ 1.0 1 1

Nested Loop (cost=0.86..17.95 rows=1 width=25) (actual time=0.043..0.044 rows=1 loops=1)

14. 0.015 0.015 ↑ 1.5 2 1

Index Scan using idx_store_indent_prescription_details_patient_presc_id on store_indent_prescription_details sipd (cost=0.43..4.54 rows=3 width=8) (actual time=0.013..0.015 rows=2 loops=1)

  • Index Cond: (patient_presc_id = 5439813)
15. 0.024 0.024 ↓ 0.0 0 2

Index Scan using store_patient_indent_details_pkey on store_patient_indent_details spd (cost=0.43..4.47 rows=1 width=17) (actual time=0.012..0.012 rows=0 loops=2)

  • Index Cond: (indent_item_id = sipd.indent_item_id)
  • Filter: ((ref_medicine_id IS NULL) AND (medicine_id = 67236))
  • Rows Removed by Filter: 0
16. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_indent_no_main_idx on store_patient_indent_main spm (cost=0.43..3.47 rows=1 width=9) (never executed)

  • Index Cond: ((patient_indent_no)::text = (spd.patient_indent_no)::text)
  • Filter: (((patient_indent_no)::text <> 'PI1162238'::text) AND ((visit_id)::text = 'A01I20020000061'::text) AND ("substring"((visit_id)::text, 5, 2) = '20'::text))
17. 0.000 0.000 ↓ 0.0 0

Index Scan using ip_prescription_pkey on ip_prescription ipp (cost=0.43..4.47 rows=1 width=4) (never executed)

  • Index Cond: (prescription_id = 5439813)
  • Filter: (presc_type = 'V'::bpchar)
Planning time : 2.876 ms
Execution time : 8,045.760 ms