explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LYKe : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #U95f; plan #8FEw; plan #F3jU; plan #qLke; plan #EtFl; plan #tsTs; plan #eCXR

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.013 4.842 ↓ 11.0 11 1

Nested Loop (cost=619.12..629.04 rows=1 width=398) (actual time=4.389..4.842 rows=11 loops=1)

  • Join Filter: ((n.contact_id)::bpchar = (s.contact_id)::bpchar)
2. 0.006 4.774 ↓ 11.0 11 1

Nested Loop (cost=618.69..628.53 rows=1 width=432) (actual time=4.380..4.774 rows=11 loops=1)

  • Join Filter: ((n.contact_id)::bpchar = (q.contact_id)::bpchar)
3. 0.012 4.713 ↓ 11.0 11 1

Nested Loop (cost=618.27..628.01 rows=1 width=388) (actual time=4.371..4.713 rows=11 loops=1)

4. 0.012 4.646 ↓ 11.0 11 1

Nested Loop (cost=617.85..627.47 rows=1 width=323) (actual time=4.362..4.646 rows=11 loops=1)

  • Join Filter: ((k.account_id)::bpchar = (n.account_id)::bpchar)
5. 0.010 4.469 ↓ 11.0 11 1

Nested Loop (cost=617.43..626.70 rows=1 width=326) (actual time=4.351..4.469 rows=11 loops=1)

  • Join Filter: (("*SELECT* 1".account_id)::bpchar = (k.account_id)::bpchar)
6. 0.013 4.404 ↓ 5.5 11 1

Nested Loop (cost=617.01..625.55 rows=2 width=301) (actual time=4.339..4.404 rows=11 loops=1)

7. 0.007 4.336 ↓ 5.5 11 1

Unique (cost=616.59..616.66 rows=2 width=292) (actual time=4.329..4.336 rows=11 loops=1)

8. 0.030 4.329 ↓ 5.5 11 1

Sort (cost=616.59..616.59 rows=2 width=292) (actual time=4.328..4.329 rows=11 loops=1)

  • Sort Key: "*SELECT* 1".cart_id, "*SELECT* 1".cart_name, "*SELECT* 1".cart_status, "*SELECT* 1".cart_status_ts, "*SELECT* 1".cart_type, "*SELECT* 1".account_id, (("*SELECT* 1".approval_required_by_role)::character varying), "*SELECT* 1".drop_date_country_code, "*SELECT* 1".drop_date, "*SELECT* 1".order_submit_date, "*SELECT* 1".email_delivery_time, "*SELECT* 1".due_date, (NULL::character varying)
  • Sort Method: quicksort Memory: 27kB
9. 0.004 4.299 ↓ 5.5 11 1

Append (cost=173.95..616.58 rows=2 width=292) (actual time=1.191..4.299 rows=11 loops=1)

10. 0.006 3.802 ↓ 11.0 11 1

Subquery Scan on *SELECT* 1 (cost=173.95..203.83 rows=1 width=196) (actual time=1.191..3.802 rows=11 loops=1)

11. 0.042 3.796 ↓ 11.0 11 1

Nested Loop Semi Join (cost=173.95..203.82 rows=1 width=172) (actual time=1.189..3.796 rows=11 loops=1)

12. 0.560 1.402 ↓ 14.0 14 1

Bitmap Heap Scan on sc_carts i (cost=142.92..170.77 rows=1 width=132) (actual time=0.929..1.402 rows=14 loops=1)

  • Recheck Cond: (((cart_status)::text = 'PENDING APPROVAL'::text) AND ((cart_type)::text = ANY ('{"SHIP TO YOUR DOOR","LIST PURCHASE","OFFLINE ORDER",TELEVISION,RADIO,"LOCAL PR","COMMUNITY OUTREACH","CAMPAIGN PACKAGE"}'::text[])))
  • Filter: ((is_active IS TRUE) AND (auto_program_id IS NULL) AND ((master_account_id)::bpchar = 'AC10090000001200'::bpchar))
  • Rows Removed by Filter: 279
  • Heap Blocks: exact=285
13. 0.032 0.842 ↓ 0.0 0 1

BitmapAnd (cost=142.92..142.92 rows=14 width=0) (actual time=0.842..0.842 rows=0 loops=1)

14. 0.120 0.120 ↓ 1.1 515 1

Bitmap Index Scan on idx_sc_carts_status_session_contact_last_sv_pt (cost=0.00..26.05 rows=484 width=0) (actual time=0.120..0.120 rows=515 loops=1)

  • Index Cond: ((cart_status)::text = 'PENDING APPROVAL'::text)
15. 0.690 0.690 ↓ 1.1 2,522 1

Bitmap Index Scan on ix_sc_carts_lookup_cart_type (cost=0.00..116.62 rows=2,301 width=0) (actual time=0.690..0.690 rows=2,522 loops=1)

  • Index Cond: ((cart_type)::text = ANY ('{"SHIP TO YOUR DOOR","LIST PURCHASE","OFFLINE ORDER",TELEVISION,RADIO,"LOCAL PR","COMMUNITY OUTREACH","CAMPAIGN PACKAGE"}'::text[]))
16. 0.070 2.352 ↑ 1.0 1 14

Bitmap Heap Scan on sc_items j (cost=31.03..33.05 rows=1 width=17) (actual time=0.168..0.168 rows=1 loops=14)

  • Recheck Cond: (((cart_id)::bpchar = (i.cart_id)::bpchar) AND ((item_order_status)::text = 'PENDING APPROVAL'::text))
  • Filter: (is_active IS TRUE)
  • Heap Blocks: exact=12
17. 0.000 2.282 ↓ 0.0 0 14

BitmapAnd (cost=31.03..31.03 rows=1 width=0) (actual time=0.163..0.163 rows=0 loops=14)

18. 0.098 0.098 ↑ 214.0 2 14

Bitmap Index Scan on ix_sc_items_cart_id (cost=0.00..9.64 rows=428 width=0) (actual time=0.007..0.007 rows=2 loops=14)

  • Index Cond: ((cart_id)::bpchar = (i.cart_id)::bpchar)
19. 2.184 2.184 ↓ 1.0 1,131 14

Bitmap Index Scan on ix_sc_items_item_order_status (cost=0.00..20.86 rows=1,124 width=0) (actual time=0.156..0.156 rows=1,131 loops=14)

  • Index Cond: ((item_order_status)::text = 'PENDING APPROVAL'::text)
20. 0.000 0.493 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.97..412.73 rows=1 width=196) (actual time=0.493..0.493 rows=0 loops=1)

21. 0.000 0.493 ↓ 0.0 0 1

Nested Loop (cost=0.97..412.72 rows=1 width=171) (actual time=0.493..0.493 rows=0 loops=1)

22. 0.000 0.493 ↓ 0.0 0 1

Nested Loop (cost=0.42..403.55 rows=2 width=150) (actual time=0.493..0.493 rows=0 loops=1)

23. 0.069 0.069 ↑ 1.0 85 1

Seq Scan on sc_item_approvals ia (cost=0.00..12.60 rows=88 width=34) (actual time=0.009..0.069 rows=85 loops=1)

  • Filter: ((approval_required_by_role)::text = 'masters'::text)
  • Rows Removed by Filter: 113
24. 0.425 0.425 ↓ 0.0 0 85

Index Scan using pk_sc_carts on sc_carts i_1 (cost=0.42..4.44 rows=1 width=116) (actual time=0.005..0.005 rows=0 loops=85)

  • Index Cond: ((cart_id)::bpchar = (ia.cart_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND (auto_program_id IS NULL) AND ((master_account_id)::bpchar = 'AC10090000001200'::bpchar) AND ((cart_status)::text = ANY ('{RUNNING,PAUSED,OK,PROCESSING}'::text[])))
  • Rows Removed by Filter: 1
25. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_sc_items on sc_items sci (cost=0.56..4.58 rows=1 width=49) (never executed)

  • Index Cond: (((cart_id)::bpchar = (ia.cart_id)::bpchar) AND ((cart_item_id)::bpchar = (ia.cart_item_id)::bpchar))
  • Filter: ((item_order_status)::text = 'PENDING APPROVAL'::text)
26. 0.055 0.055 ↑ 1.0 1 11

Index Scan using pk_acct_accounts on acct_accounts m (cost=0.42..4.44 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=11)

  • Index Cond: ((account_id)::bpchar = ("*SELECT* 1".account_id)::bpchar)
  • Filter: (is_active IS TRUE)
27. 0.055 0.055 ↑ 1.0 1 11

Index Scan using ix_rel_masters_accounts_account_id on rel_masters_accounts k (cost=0.42..0.56 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=11)

  • Index Cond: ((account_id)::bpchar = (m.account_id)::bpchar)
  • Filter: ((is_approved IS TRUE) AND (is_active IS TRUE) AND (master_denetwork_ts IS NULL) AND ((master_account_id)::bpchar = 'AC10090000001200'::bpchar))
28. 0.165 0.165 ↑ 2.0 1 11

Index Scan using ix_acct_contacts_account_id on acct_contacts n (cost=0.42..0.75 rows=2 width=48) (actual time=0.009..0.015 rows=1 loops=11)

  • Index Cond: ((account_id)::bpchar = (m.account_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((contact_type)::text = 'Primary'::text))
  • Rows Removed by Filter: 8
29. 0.055 0.055 ↑ 1.0 1 11

Index Scan using ix_acct_addresses_contact_id on acct_addresses p (cost=0.42..0.54 rows=1 width=65) (actual time=0.005..0.005 rows=1 loops=11)

  • Index Cond: ((contact_id)::bpchar = (n.contact_id)::bpchar)
  • Filter: ((address_type)::text = 'Primary'::text)
30. 0.055 0.055 ↑ 1.0 1 11

Index Scan using ix_acct_e_info_contact_id on acct_e_info q (cost=0.42..0.50 rows=1 width=44) (actual time=0.005..0.005 rows=1 loops=11)

  • Index Cond: ((contact_id)::bpchar = (p.contact_id)::bpchar)
  • Filter: ((e_info_type)::text = 'Primary'::text)
31. 0.055 0.055 ↑ 1.0 1 11

Index Scan using pk_acct_phones on acct_phones s (cost=0.42..0.50 rows=1 width=30) (actual time=0.005..0.005 rows=1 loops=11)

  • Index Cond: (((contact_id)::bpchar = (p.contact_id)::bpchar) AND ((phone_type)::text = 'Primary'::text))
Planning time : 7.895 ms
Execution time : 5.030 ms