explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sxY7

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 220.777 ↑ 1.0 1 1

Limit (cost=819.01..819.02 rows=1 width=398) (actual time=220.776..220.777 rows=1 loops=1)

2. 0.027 220.774 ↑ 10.0 1 1

LockRows (cost=819.01..819.05 rows=10 width=398) (actual time=220.774..220.774 rows=1 loops=1)

3. 43.907 220.747 ↑ 10.0 1 1

Sort (cost=819.01..819.02 rows=10 width=398) (actual time=220.747..220.747 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. 3.114 176.840 ↓ 733.4 7,334 1

Nested Loop (cost=16.19..819.00 rows=10 width=398) (actual time=0.134..176.840 rows=7,334 loops=1)

  • Join Filter: (drug_ddids.drug_id = drugs.id)
5. 2.273 166.392 ↓ 666.7 7,334 1

Merge Join (cost=16.13..798.15 rows=11 width=400) (actual time=0.116..166.392 rows=7,334 loops=1)

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

Nested Loop (cost=16.11..2,343.62 rows=6 width=402) (actual time=0.104..162.882 rows=7,334 loops=1)

7. 2.924 113.843 ↓ 401.8 15,267 1

Nested Loop (cost=16.02..2,258.48 rows=38 width=396) (actual time=0.086..113.843 rows=15,267 loops=1)

8. 0.035 1.086 ↓ 93.0 93 1

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

9. 0.084 0.865 ↓ 93.0 93 1

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

10. 0.060 0.179 ↓ 9.6 86 1

Nested Loop (cost=0.06..14.01 rows=9 width=34) (actual time=0.049..0.179 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.011 0.011 ↑ 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.011..0.011 rows=1 loops=1)

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

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

13. 0.602 0.602 ↑ 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.005..0.007 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.186 0.186 ↑ 1.0 1 93

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

  • Index Cond: (id = 2,113)
15. 92.349 109.833 ↑ 6.5 164 93

Bitmap Heap Scan on pa_requests (cost=15.85..2,216.24 rows=1,061 width=354) (actual time=0.280..1.181 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=69,767
16. 17.484 17.484 ↓ 1.5 1,854 93

Bitmap Index Scan on idx_hub_id_ddid_archived_shredded_on_pa_requests (cost=0.00..15.80 rows=1,243 width=0) (actual time=0.188..0.188 rows=1,854 loops=93)

  • Index Cond: ((drug_ddid_id = drug_ddids.id) AND (hub_id = 5) AND (is_archived = false) AND (is_shredded = false))
17. 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
18. 1.237 1.237 ↓ 210.6 7,371 1

Index Scan using drug_specializations_pkey on drug_specializations (cost=0.03..6.17 rows=35 width=10) (actual time=0.002..1.237 rows=7,371 loops=1)

19. 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.702 ms
Execution time : 220.948 ms