explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BYR9

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 6,111.936 ↑ 1.0 1 1

Limit (cost=6,168.19..6,168.19 rows=1 width=398) (actual time=6,111.936..6,111.936 rows=1 loops=1)

2. 0.029 6,111.934 ↑ 10.0 1 1

LockRows (cost=6,168.19..6,168.22 rows=10 width=398) (actual time=6,111.934..6,111.934 rows=1 loops=1)

3. 44.742 6,111.905 ↑ 10.0 1 1

Sort (cost=6,168.19..6,168.19 rows=10 width=398) (actual time=6,111.905..6,111.905 rows=1 loops=1)

  • Sort Key: drug_specializations_drugs.weight DESC, pa_requests.priority DESC, pa_requests.changed_at
  • Sort Method: quicksort Memory: 3,974kB
4. 2.368 6,067.163 ↓ 733.4 7,334 1

Nested Loop (cost=5,371.48..6,168.18 rows=10 width=398) (actual time=2,157.144..6,067.163 rows=7,334 loops=1)

  • Join Filter: (drug_ddids.drug_id = drugs.id)
5. 2.376 6,057.461 ↓ 666.7 7,334 1

Merge Join (cost=5,371.43..6,147.32 rows=11 width=400) (actual time=2,157.134..6,057.461 rows=7,334 loops=1)

  • Merge Cond: (drug_specializations_drugs.drug_specialization_id = drug_specializations.id)
6. 10.288 6,054.452 ↓ 1,222.3 7,334 1

Nested Loop (cost=5,370.14..7,697.66 rows=6 width=402) (actual time=2,157.100..6,054.452 rows=7,334 loops=1)

7. 3.268 5,998.363 ↓ 401.8 15,267 1

Nested Loop (cost=5,370.06..7,612.51 rows=38 width=396) (actual time=2,157.071..5,998.363 rows=15,267 loops=1)

8. 0.170 1.989 ↓ 93.0 93 1

Nested Loop (cost=0.17..39.06 rows=1 width=46) (actual time=0.070..1.989 rows=93 loops=1)

9. 0.134 1.354 ↓ 93.0 93 1

Nested Loop (cost=0.11..34.99 rows=1 width=48) (actual time=0.065..1.354 rows=93 loops=1)

10. 0.077 0.274 ↓ 9.6 86 1

Nested Loop (cost=0.06..14.01 rows=9 width=34) (actual time=0.058..0.274 rows=86 loops=1)

  • Join Filter: (drug_specializations_drugs.drug_specialization_id = drug_specializations_users.drug_specialization_id)
  • Rows Removed by Join Filter: 520
11. 0.014 0.014 ↑ 1.0 1 1

Index Scan using index_drug_specializations_users_on_user_id_and_drug_specializa on drug_specializations_users (cost=0.06..4.06 rows=1 width=18) (actual time=0.012..0.014 rows=1 loops=1)

  • Index Cond: (user_id = 2,113)
12. 0.183 0.183 ↑ 1.0 606 1

Seq Scan on drug_specializations_drugs (cost=0.00..7.82 rows=608 width=16) (actual time=0.006..0.183 rows=606 loops=1)

13. 0.946 0.946 ↑ 1.0 1 86

Index Scan using index_drug_ddids_on_drug_id on drug_ddids (cost=0.06..2.33 rows=1 width=14) (actual time=0.007..0.011 rows=1 loops=86)

  • Index Cond: (drug_id = drug_specializations_drugs.drug_id)
  • Filter: ((ddid)::text = ANY ('{185286,178584,091157,208472,208474,194970,139962,185615,185614,205721,198788,201718,186539,182488,184280,184281,184277,184279,184278,204684,204685,204686,180089,180087,180086,179768,
  • Rows Removed by Filter: 1
14. 0.465 0.465 ↑ 1.0 1 93

Index Scan using users_pkey on users (cost=0.06..4.06 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=93)

  • Index Cond: (id = 2,113)
15. 66.588 5,993.106 ↑ 6.5 164 93

Bitmap Heap Scan on pa_requests (cost=5,369.89..7,570.27 rows=1,061 width=354) (actual time=63.796..64.442 rows=164 loops=93)

  • Recheck Cond: ((drug_ddid_id = drug_ddids.id) AND (hub_id = 5))
  • Filter: ((snooze_until IS NULL) AND (NOT is_archived) AND (NOT is_shredded) AND (requested_state_at IS NULL) AND (NOT requires_help) AND (NOT is_appeal) AND (user_id = 32))
  • Rows Removed by Filter: 155
  • Heap Blocks: exact=28,144
16. 176.607 5,926.518 ↓ 0.0 0 93

BitmapAnd (cost=5,369.89..5,369.89 rows=1,243 width=0) (actual time=63.726..63.726 rows=0 loops=93)

17. 149.730 149.730 ↑ 1.7 14,521 93

Bitmap Index Scan on index_pa_requests_on_drug_ddid_id (cost=0.00..181.78 rows=25,188 width=0) (actual time=1.610..1.610 rows=14,521 loops=93)

  • Index Cond: (drug_ddid_id = drug_ddids.id)
18. 5,600.181 5,600.181 ↑ 2.9 207,026 93

Bitmap Index Scan on pa_hub_reqs_arch_shred (cost=0.00..5,162.69 rows=593,043 width=0) (actual time=60.217..60.217 rows=207,026 loops=93)

  • Index Cond: ((hub_id = 5) AND (is_archived = false) AND (is_shredded = false))
19. 45.801 45.801 ↓ 0.0 0 15,267

Index Scan using index_outcomes_on_pa_request_id on outcomes (cost=0.09..2.24 rows=1 width=10) (actual time=0.003..0.003 rows=0 loops=15,267)

  • Index Cond: (pa_request_id = pa_requests.id)
  • Filter: (outcome_type_id = 2)
  • Rows Removed by Filter: 0
20. 0.622 0.633 ↓ 210.6 7,371 1

Sort (cost=1.28..1.30 rows=35 width=10) (actual time=0.027..0.633 rows=7,371 loops=1)

  • Sort Key: drug_specializations.id
  • Sort Method: quicksort Memory: 26kB
21. 0.011 0.011 ↓ 1.1 39 1

Seq Scan on drug_specializations (cost=0.00..1.10 rows=35 width=10) (actual time=0.008..0.011 rows=39 loops=1)

22. 7.334 7.334 ↑ 1.0 1 7,334

Index Scan using drugs_pkey on drugs (cost=0.06..1.89 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=7,334)

  • Index Cond: (id = drug_specializations_drugs.drug_id)
Planning time : 3.968 ms
Execution time : 6,112.896 ms