explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pfCV : Optimization for: 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; plan #LYKe

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.014 6.665 ↓ 14.0 14 1

Nested Loop (cost=1,546.66..1,556.59 rows=1 width=390) (actual time=6.050..6.665 rows=14 loops=1)

  • Join Filter: ((n.contact_id)::bpchar = (s.contact_id)::bpchar)
2. 0.011 6.567 ↓ 14.0 14 1

Nested Loop (cost=1,546.24..1,556.07 rows=1 width=424) (actual time=6.040..6.567 rows=14 loops=1)

  • Join Filter: ((n.contact_id)::bpchar = (q.contact_id)::bpchar)
3. 0.013 6.486 ↓ 14.0 14 1

Nested Loop (cost=1,545.82..1,555.56 rows=1 width=380) (actual time=6.030..6.486 rows=14 loops=1)

4. 0.012 6.403 ↓ 14.0 14 1

Nested Loop (cost=1,545.40..1,555.01 rows=1 width=315) (actual time=6.021..6.403 rows=14 loops=1)

  • Join Filter: ((k.account_id)::bpchar = (n.account_id)::bpchar)
5. 0.005 6.181 ↓ 14.0 14 1

Nested Loop (cost=1,544.97..1,554.24 rows=1 width=318) (actual time=6.009..6.181 rows=14 loops=1)

  • Join Filter: ((c.account_id)::bpchar = (k.account_id)::bpchar)
6. 0.012 6.092 ↓ 7.0 14 1

Nested Loop (cost=1,544.55..1,553.09 rows=2 width=293) (actual time=5.999..6.092 rows=14 loops=1)

7. 0.009 5.996 ↓ 7.0 14 1

Unique (cost=1,544.13..1,544.20 rows=2 width=284) (actual time=5.987..5.996 rows=14 loops=1)

8. 0.029 5.987 ↓ 7.0 14 1

Sort (cost=1,544.13..1,544.14 rows=2 width=284) (actual time=5.986..5.987 rows=14 loops=1)

  • Sort Key: c.cart_id, c.cart_name, c.cart_status, c.cart_status_ts, c.cart_type, c.account_id, c.approval_required_by_role, c.drop_date_country_code, c.drop_date, c.order_submit_date, c.email_delivery_time, (NULL::text)
  • Sort Method: quicksort Memory: 28kB
9. 0.004 5.958 ↓ 7.0 14 1

Append (cost=175.55..1,544.12 rows=2 width=284) (actual time=1.178..5.958 rows=14 loops=1)

10. 0.031 3.747 ↓ 11.0 11 1

Nested Loop Semi Join (cost=175.55..205.42 rows=1 width=164) (actual time=1.178..3.747 rows=11 loops=1)

11. 0.644 1.490 ↓ 14.0 14 1

Bitmap Heap Scan on sc_carts c (cost=144.52..172.37 rows=1 width=132) (actual time=0.930..1.490 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=283
12. 0.029 0.846 ↓ 0.0 0 1

BitmapAnd (cost=144.52..144.52 rows=14 width=0) (actual time=0.846..0.846 rows=0 loops=1)

13. 0.102 0.102 ↓ 1.0 498 1

Bitmap Index Scan on idx_sc_carts_status_session_contact_last_sv_pt (cost=0.00..28.13 rows=495 width=0) (actual time=0.102..0.102 rows=498 loops=1)

  • Index Cond: ((cart_status)::text = 'PENDING APPROVAL'::text)
14. 0.715 0.715 ↓ 1.0 2,306 1

Bitmap Index Scan on ix_sc_carts_lookup_cart_type (cost=0.00..116.14 rows=2,240 width=0) (actual time=0.715..0.715 rows=2,306 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[]))
15. 0.042 2.226 ↑ 1.0 1 14

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

  • Recheck Cond: (((cart_id)::bpchar = (c.cart_id)::bpchar) AND ((item_order_status)::text = 'PENDING APPROVAL'::text))
  • Filter: (is_active IS TRUE)
  • Heap Blocks: exact=12
16. 0.014 2.184 ↓ 0.0 0 14

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

17. 0.084 0.084 ↑ 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.006..0.006 rows=2 loops=14)

  • Index Cond: ((cart_id)::bpchar = (c.cart_id)::bpchar)
18. 2.086 2.086 ↓ 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.149..0.149 rows=1,131 loops=14)

  • Index Cond: ((item_order_status)::text = 'PENDING APPROVAL'::text)
19. 0.014 2.207 ↓ 3.0 3 1

Nested Loop Semi Join (cost=560.14..1,338.67 rows=1 width=164) (actual time=1.606..2.207 rows=3 loops=1)

20. 0.039 1.287 ↑ 3.7 6 1

Bitmap Heap Scan on sc_carts c_1 (cost=529.12..611.56 rows=22 width=124) (actual time=1.260..1.287 rows=6 loops=1)

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

BitmapAnd (cost=529.12..529.12 rows=42 width=0) (actual time=1.248..1.248 rows=0 loops=1)

22. 0.077 0.077 ↓ 1.0 498 1

Bitmap Index Scan on idx_sc_carts_status_session_contact_last_sv_pt (cost=0.00..28.13 rows=495 width=0) (actual time=0.077..0.077 rows=498 loops=1)

  • Index Cond: ((cart_status)::text = 'PENDING APPROVAL'::text)
23. 1.136 1.136 ↓ 1.0 6,762 1

Bitmap Index Scan on ix_sc_carts_master_account_id (cost=0.00..500.73 rows=6,708 width=0) (actual time=1.136..1.136 rows=6,762 loops=1)

  • Index Cond: ((master_account_id)::bpchar = 'AC10090000001200'::bpchar)
24. 0.006 0.906 ↓ 0.0 0 6

Bitmap Heap Scan on sc_items i_1 (cost=31.03..33.04 rows=1 width=17) (actual time=0.151..0.151 rows=0 loops=6)

  • Recheck Cond: (((cart_id)::bpchar = (c_1.cart_id)::bpchar) AND ((item_order_status)::text = 'PENDING APPROVAL'::text))
  • Filter: ((template_id IS NOT NULL) AND (assignment_id IS NOT NULL) AND (is_active IS TRUE))
  • Heap Blocks: exact=3
25. 0.006 0.900 ↓ 0.0 0 6

BitmapAnd (cost=31.03..31.03 rows=1 width=0) (actual time=0.150..0.150 rows=0 loops=6)

26. 0.030 0.030 ↑ 428.0 1 6

Bitmap Index Scan on ix_sc_items_cart_id (cost=0.00..9.64 rows=428 width=0) (actual time=0.005..0.005 rows=1 loops=6)

  • Index Cond: ((cart_id)::bpchar = (c_1.cart_id)::bpchar)
27. 0.864 0.864 ↓ 1.0 1,131 6

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

  • Index Cond: ((item_order_status)::text = 'PENDING APPROVAL'::text)
28. 0.084 0.084 ↑ 1.0 1 14

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

  • Index Cond: ((account_id)::bpchar = (c.account_id)::bpchar)
  • Filter: (is_active IS TRUE)
29. 0.084 0.084 ↑ 1.0 1 14

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.006..0.006 rows=1 loops=14)

  • 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))
30. 0.210 0.210 ↑ 2.0 1 14

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=14)

  • Index Cond: ((account_id)::bpchar = (m.account_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((contact_type)::text = 'Primary'::text))
  • Rows Removed by Filter: 7
31. 0.070 0.070 ↑ 1.0 1 14

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=14)

  • Index Cond: ((contact_id)::bpchar = (n.contact_id)::bpchar)
  • Filter: ((address_type)::text = 'Primary'::text)
32. 0.070 0.070 ↑ 1.0 1 14

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=14)

  • Index Cond: ((contact_id)::bpchar = (p.contact_id)::bpchar)
  • Filter: ((e_info_type)::text = 'Primary'::text)
33. 0.084 0.084 ↑ 1.0 1 14

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

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