explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iLTu

Settings
# exclusive inclusive rows x rows loops node
1. 1.092 9,745.513 ↑ 1.0 1 1

Aggregate (cost=140,465.30..140,465.30 rows=1 width=8) (actual time=9,745.513..9,745.513 rows=1 loops=1)

2. 2.405 9,744.421 ↓ 2,804.0 2,804 1

Nested Loop Left Join (cost=1,125.67..140,465.30 rows=1 width=0) (actual time=162.700..9,744.421 rows=2,804 loops=1)

  • Filter: ((fpr.id IS NULL) OR ((ft.name)::text <> 'EHR'::text))
  • Rows Removed by Filter: 10
3. 1.916 9,742.016 ↓ 2,814.0 2,814 1

Nested Loop Left Join (cost=1,125.65..140,465.09 rows=1 width=8) (actual time=162.696..9,742.016 rows=2,814 loops=1)

4. 3.283 9,740.100 ↓ 2,814.0 2,814 1

Nested Loop Left Join (cost=1,125.62..140,465.05 rows=1 width=8) (actual time=162.694..9,740.100 rows=2,814 loops=1)

5. 0.731 9,722.747 ↓ 2,814.0 2,814 1

Nested Loop Left Join (cost=1,125.54..140,463.01 rows=1 width=4) (actual time=162.677..9,722.747 rows=2,814 loops=1)

6. 4.608 9,676.992 ↓ 2,814.0 2,814 1

Nested Loop Left Join (cost=1,125.45..140,460.90 rows=1 width=4) (actual time=162.647..9,676.992 rows=2,814 loops=1)

  • Filter: ((outcomes.outcome_type_id <> 2) OR (outcomes.pa_request_id IS NULL))
  • Rows Removed by Filter: 675
7. 0.000 9,620.049 ↓ 3,489.0 3,489 1

Nested Loop (cost=1,125.36..140,458.79 rows=1 width=4) (actual time=162.613..9,620.049 rows=3,489 loops=1)

8. 286.184 4,239.666 ↓ 432.8 793,755 1

Hash Left Join (cost=1,125.28..136,579.91 rows=1,834 width=4) (actual time=136.813..4,239.666 rows=793,755 loops=1)

  • Hash Cond: (pa_requests.id = pa_queues_pa_requests.pa_request_id)
  • Filter: ((pa_queues_pa_requests.id IS NULL) OR (pa_queues_pa_requests.pa_queue_id <> ALL ('{1,2}'::integer[])))
  • Rows Removed by Filter: 3504
9. 70.671 3,894.862 ↓ 99.1 797,259 1

Nested Loop (cost=168.07..135,260.76 rows=8,042 width=4) (actual time=78.146..3,894.862 rows=797,259 loops=1)

10. 0.091 0.091 ↑ 1.0 5 1

Index Scan using index_drug_ddids_on_drug_id on drug_ddids ddid_filter (cost=0.06..11.76 rows=5 width=4) (actual time=0.031..0.091 rows=5 loops=1)

  • Index Cond: (drug_id = ANY ('{207,2492,278}'::integer[]))
11. 3,670.595 3,824.100 ↓ 10.5 159,452 5

Bitmap Heap Scan on pa_requests (cost=168.01..27,004.41 rows=15,131 width=8) (actual time=46.311..764.820 rows=159,452 loops=5)

  • Recheck Cond: ((drug_ddid_id = ddid_filter.id) AND (hub_id = 5))
  • Rows Removed by Index Recheck: 454534
  • Filter: ((creating_api_consumer_id IS DISTINCT FROM 72) AND (user_id = 32))
  • Rows Removed by Filter: 2354
  • Heap Blocks: exact=410597 lossy=106013
12. 153.505 153.505 ↓ 9.9 166,451 5

Bitmap Index Scan on idx_hub_id_ddid_archived_shredded_on_pa_requests (cost=0.00..167.26 rows=16,785 width=0) (actual time=30.701..30.701 rows=166,451 loops=5)

  • Index Cond: ((drug_ddid_id = ddid_filter.id) AND (hub_id = 5))
13. 13.385 58.620 ↓ 4.5 70,002 1

Hash (cost=902.77..902.77 rows=15,554 width=12) (actual time=58.620..58.620 rows=70,002 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 4032kB
14. 45.235 45.235 ↓ 4.5 70,002 1

Index Scan using index_pa_queues_pa_requests_on_pa_request_id on pa_queues_pa_requests (cost=0.06..902.77 rows=15,554 width=12) (actual time=0.014..45.235 rows=70,002 loops=1)

15. 5,556.285 5,556.285 ↓ 0.0 0 793,755

Index Scan using index_patients_on_pa_request_id on patients query_patients (cost=0.09..2.11 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=793,755)

  • Index Cond: (pa_request_id = pa_requests.id)
  • Filter: (((lower((first_name)::text) ~~ 'barbara%'::text) OR (lower((last_name)::text) ~~ 'barbara%'::text)) AND ((lower((first_name)::text) ~~ 'barbara%'::text) OR (lower((last_name)::text) ~~ 'barbara%'::text)))
  • Rows Removed by Filter: 1
16. 52.335 52.335 ↑ 1.0 1 3,489

Index Scan using index_outcomes_on_pa_request_id on outcomes (cost=0.09..2.11 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=3,489)

  • Index Cond: (pa_requests.id = pa_request_id)
17. 45.024 45.024 ↑ 1.0 1 2,814

Index Only Scan using index_prescribers_on_pa_request_id on prescribers query_prescribers (cost=0.09..2.11 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=2,814)

  • Index Cond: (pa_request_id = pa_requests.id)
  • Heap Fetches: 2814
18. 14.070 14.070 ↓ 0.0 0 2,814

Index Scan using index_flags_pa_requests_on_pa_request_id on flags_pa_requests fpr (cost=0.08..2.04 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=2,814)

  • Index Cond: (pa_request_id = pa_requests.id)
19. 0.000 0.000 ↓ 0.0 0 2,814

Index Scan using flags_pkey on flags f (cost=0.03..0.03 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=2,814)

  • Index Cond: (id = fpr.flag_id)
20. 0.000 0.000 ↓ 0.0 0 2,814

Index Scan using flag_types_pkey on flag_types ft (cost=0.03..0.20 rows=1 width=86) (actual time=0.000..0.000 rows=0 loops=2,814)

  • Index Cond: (id = f.flag_type_id)
Planning time : 3.401 ms
Execution time : 9,745.675 ms