explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LdnR

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 142.133 ↑ 2.5 70 1

Nested Loop Anti Join (cost=647.87..155,051.48 rows=176 width=8) (actual time=119.211..142.133 rows=70 loops=1)

2. 0.064 141.719 ↑ 2.1 92 1

Nested Loop Anti Join (cost=647.82..155,027.39 rows=193 width=12) (actual time=115.330..141.719 rows=92 loops=1)

3. 0.549 141.135 ↑ 1.6 130 1

Nested Loop Anti Join (cost=647.76..155,006.86 rows=212 width=12) (actual time=115.313..141.135 rows=130 loops=1)

4. 0.270 139.910 ↓ 2.9 676 1

Hash Semi Join (cost=647.70..154,985.22 rows=232 width=12) (actual time=114.364..139.910 rows=676 loops=1)

  • Hash Cond: ((addresses.state)::text = (s.abbrev)::text)
5. 0.376 139.544 ↓ 2.7 676 1

Nested Loop (cost=644.90..154,981.54 rows=246 width=15) (actual time=114.243..139.544 rows=676 loops=1)

6. 0.154 137.140 ↓ 2.7 676 1

Nested Loop (cost=644.84..154,951.47 rows=246 width=16) (actual time=114.234..137.140 rows=676 loops=1)

  • Join Filter: ((pa.prescribing_provider_id IS NULL) OR pr.is_lab_test OR (pa.prescribing_provider_id = dr.id))
  • Rows Removed by Join Filter: 19
7. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on providers dr (cost=0.00..1.05 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

  • Filter: (is_active AND (id = 79))
  • Rows Removed by Filter: 18
8. 0.545 136.974 ↓ 1.2 695 1

Nested Loop (cost=644.84..154,948.31 rows=602 width=17) (actual time=114.220..136.974 rows=695 loops=1)

9. 0.043 132.985 ↓ 1.2 861 1

Nested Loop (cost=644.78..153,765.14 rows=705 width=9) (actual time=114.199..132.985 rows=861 loops=1)

10. 13.357 131.196 ↓ 1.2 873 1

Hash Join (cost=644.73..153,686.24 rows=713 width=12) (actual time=114.188..131.196 rows=873 loops=1)

  • Hash Cond: (fr.pharmacy_id = ph.id)
  • Join Filter: ((ph.type = 'local'::pharmacy_type) OR (NOT (alternatives: SubPlan 4 or hashed SubPlan 6)))
  • Rows Removed by Join Filter: 5
11. 0.856 16.482 ↓ 1.2 878 1

Nested Loop (cost=0.06..153,041.19 rows=713 width=16) (actual time=0.077..16.482 rows=878 loops=1)

12. 1.838 13.446 ↓ 1.1 1,090 1

Seq Scan on fill_request fr (cost=0.00..152,289.28 rows=1,015 width=20) (actual time=0.058..13.446 rows=1,090 loops=1)

  • Filter: ((local_rx_id IS NULL) AND (status = 'active'::fill_request_status) AND (NOT (SubPlan 2)) AND (NOT (SubPlan 1)))
  • Rows Removed by Filter: 7069
13.          

SubPlan (for Seq Scan)

14. 4.576 10.518 ↑ 1.0 1 3,506

Nested Loop (cost=0.11..8.13 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=3,506)

15. 3.506 3.506 ↑ 1.0 1 3,506

Index Scan using fill_request_pharmacy_order_fill_request_id_idx on fill_request_pharmacy_order frpo_1 (cost=0.06..4.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=3,506)

  • Index Cond: (fill_request_id = fr.id)
16. 2.436 2.436 ↑ 1.0 1 2,436

Index Scan using pharmacy_order_pkey on pharmacy_order po_1 (cost=0.06..4.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,436)

  • Index Cond: (id = frpo_1.pharmacy_order_id)
  • Filter: (status <> ALL ('{admin_fixed_needs_fill,canceled}'::pharmacy_order_status[]))
  • Rows Removed by Filter: 0
17. 0.000 1.090 ↓ 0.0 0 1,090

Nested Loop (cost=0.11..10.79 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1,090)

18. 1.090 1.090 ↓ 0.0 0 1,090

Index Scan using fill_request_pharmacy_order_fill_request_id_idx on fill_request_pharmacy_order frpo (cost=0.06..4.06 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1,090)

  • Index Cond: (fill_request_id = fr.id)
19. 0.000 0.000 ↓ 0.0 0

Index Scan using pharmacy_order_pkey on pharmacy_order po (cost=0.06..4.06 rows=1 width=4) (never executed)

  • Index Cond: (id = frpo.pharmacy_order_id)
  • Filter: (status = 'delivered'::pharmacy_order_status)
20. 2.180 2.180 ↑ 1.0 1 1,090

Index Scan using order2_pkey on order2 o (cost=0.06..0.74 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,090)

  • Index Cond: (id = fr.order_id)
  • Filter: captured
  • Rows Removed by Filter: 0
21. 3.825 10.932 ↓ 1.0 23,685 1

Hash (cost=561.93..561.93 rows=23,642 width=8) (actual time=10.932..10.932 rows=23,685 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1182kB
22. 7.107 7.107 ↓ 1.0 23,685 1

Seq Scan on pharmacies ph (cost=0.00..561.93 rows=23,642 width=8) (actual time=0.005..7.107 rows=23,685 loops=1)

23.          

SubPlan (for Hash Join)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on rx (cost=0.00..239,909.17 rows=1 width=0) (never executed)

  • Filter: ((NOT admin_expired) AND (product_id = fr.product_id) AND (pharmacy_id = fr.pharmacy_id) AND (patient_id = fr.patient_id) AND (now() < (created + '360 days'::interval)) AND (fill_count > (SubPlan 3)))
25.          

SubPlan (for Seq Scan)

26. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.13..8.13 rows=1 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.11..8.13 rows=1 width=4) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using pharmacy_order_rx_rx_id_idx on pharmacy_order_rx porx (cost=0.06..4.06 rows=1 width=4) (never executed)

  • Index Cond: (rx_id = rx.id)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using pharmacy_order_pkey on pharmacy_order po_2 (cost=0.06..4.06 rows=1 width=4) (never executed)

  • Index Cond: (id = porx.pharmacy_order_id)
  • Filter: (status <> ALL ('{admin_fixed_needs_fill,canceled}'::pharmacy_order_status[]))
30. 32.995 90.425 ↓ 8.9 28,698 1

Seq Scan on rx rx_1 (cost=0.00..239,865.83 rows=3,208 width=12) (actual time=0.086..90.425 rows=28,698 loops=1)

  • Filter: ((NOT admin_expired) AND (now() < (created + '360 days'::interval)) AND (fill_count > (SubPlan 5)))
  • Rows Removed by Filter: 58
31.          

SubPlan (for Seq Scan)

32. 28.715 57.430 ↑ 1.0 1 28,715

Aggregate (cost=8.13..8.13 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=28,715)

33. 0.000 28.715 ↓ 0.0 0 28,715

Nested Loop (cost=0.11..8.13 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=28,715)

34. 28.715 28.715 ↓ 0.0 0 28,715

Index Scan using pharmacy_order_rx_rx_id_idx on pharmacy_order_rx porx_1 (cost=0.06..4.06 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=28,715)

  • Index Cond: (rx_id = rx_1.id)
35. 3.964 3.964 ↑ 1.0 1 1,982

Index Scan using pharmacy_order_pkey on pharmacy_order po_3 (cost=0.06..4.06 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,982)

  • Index Cond: (id = porx_1.pharmacy_order_id)
  • Filter: (status <> ALL ('{admin_fixed_needs_fill,canceled}'::pharmacy_order_status[]))
  • Rows Removed by Filter: 0
36. 1.746 1.746 ↑ 1.0 1 873

Index Scan using products_pkey on products pr (cost=0.06..0.11 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=873)

  • Index Cond: (id = fr.product_id)
  • Filter: rx_required
  • Rows Removed by Filter: 0
37. 3.444 3.444 ↑ 1.0 1 861

Index Scan using patients_pkey on patients pa (cost=0.06..1.68 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=861)

  • Index Cond: (id = fr.patient_id)
  • Filter: (NOT conversation_hidden)
  • Rows Removed by Filter: 0
38. 2.028 2.028 ↑ 1.0 1 676

Index Scan using addresses_pkey on addresses (cost=0.06..0.12 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=676)

  • Index Cond: (id = pa.primary_address_id)
39. 0.012 0.096 ↓ 1.0 51 1

Hash (cost=2.63..2.63 rows=50 width=7) (actual time=0.096..0.096 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
40. 0.030 0.084 ↓ 1.0 51 1

Hash Join (cost=1.32..2.63 rows=50 width=7) (actual time=0.061..0.084 rows=51 loops=1)

  • Hash Cond: (ps.state_id = s.id)
41. 0.028 0.028 ↓ 1.0 51 1

Seq Scan on providers_states ps (cost=0.00..1.27 rows=50 width=8) (actual time=0.016..0.028 rows=51 loops=1)

  • Filter: (provider_id = 79)
  • Rows Removed by Filter: 118
42. 0.017 0.026 ↓ 1.0 51 1

Hash (cost=1.15..1.15 rows=50 width=7) (actual time=0.026..0.026 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
43. 0.009 0.009 ↓ 1.0 51 1

Seq Scan on states s (cost=0.00..1.15 rows=50 width=7) (actual time=0.003..0.009 rows=51 loops=1)

44. 0.676 0.676 ↑ 1.0 1 676

Index Only Scan using lab_test_fill_request_id_key on lab_test lt (cost=0.06..0.09 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=676)

  • Index Cond: (fill_request_id = fr.id)
  • Heap Fetches: 546
45. 0.520 0.520 ↓ 0.0 0 130

Index Scan using ix_patient_forms_patient_id on patient_forms pf (cost=0.06..0.10 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=130)

  • Index Cond: (patient_id = pa.id)
  • Filter: (submission_dt IS NULL)
  • Rows Removed by Filter: 1
46. 0.368 0.368 ↓ 0.0 0 92

Index Scan using ix_images_patient_id on images pi (cost=0.06..0.12 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=92)

  • Index Cond: (patient_id = pa.id)
  • Filter: (filename IS NULL)
  • Rows Removed by Filter: 1
Planning time : 6.925 ms
Execution time : 142.894 ms