explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JsAK : Optimization for: plan #Q1hA

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.058 1,145.241 ↑ 1.0 1 1

Aggregate (cost=163,834.64..163,834.65 rows=1 width=32) (actual time=1,145.241..1,145.241 rows=1 loops=1)

2. 0.199 1,145.183 ↓ 174.0 174 1

Nested Loop (cost=125,550.77..163,834.64 rows=1 width=5) (actual time=714.538..1,145.183 rows=174 loops=1)

3. 0.094 1,144.636 ↓ 174.0 174 1

Nested Loop (cost=125,550.49..163,828.95 rows=1 width=9) (actual time=714.515..1,144.636 rows=174 loops=1)

4. 0.051 1,144.236 ↓ 51.0 51 1

Hash Join (cost=125,550.07..163,822.75 rows=1 width=4) (actual time=714.488..1,144.236 rows=51 loops=1)

  • Hash Cond: (od.shop_id = sh.id)
  • Join Filter: ((os.order_state_fid = ANY ('{2,4,16,17}'::integer[])) OR ((os.order_state_fid = 10) AND (sh.shop_fid <> 24)))
5. 0.098 1,144.169 ↓ 51.0 51 1

Nested Loop Anti Join (cost=125,548.33..163,821.01 rows=1 width=12) (actual time=714.465..1,144.169 rows=51 loops=1)

6. 0.316 1,141.164 ↓ 51.0 51 1

Nested Loop (cost=125,547.90..163,753.45 rows=1 width=20) (actual time=714.375..1,141.164 rows=51 loops=1)

  • Join Filter: ((os.created_at >= st.student_since) AND (os.created_at <= (st.student_since + '30 days'::interval)))
  • Rows Removed by Join Filter: 2
7. 0.573 941.704 ↓ 682.0 682 1

Nested Loop (cost=125,538.06..163,743.56 rows=1 width=24) (actual time=562.656..941.704 rows=682 loops=1)

8. 0.676 938.546 ↓ 517.0 517 1

Nested Loop (cost=125,537.77..163,730.93 rows=1 width=28) (actual time=562.635..938.546 rows=517 loops=1)

9. 0.800 927.013 ↓ 517.0 517 1

Nested Loop (cost=125,537.35..163,722.48 rows=1 width=36) (actual time=562.606..927.013 rows=517 loops=1)

  • Join Filter: ((st.student_since >= tk.created_at) AND (zcr.id = st.course_id))
  • Rows Removed by Join Filter: 552
10. 5.705 922.289 ↓ 163.5 1,308 1

Nested Loop (cost=125,537.06..163,647.26 rows=8 width=32) (actual time=562.587..922.289 rows=1,308 loops=1)

11. 5.893 831.787 ↓ 84.2 4,463 1

Nested Loop (cost=125,536.65..163,332.10 rows=53 width=52) (actual time=562.546..831.787 rows=4,463 loops=1)

12. 6.843 799.104 ↓ 57.2 4,465 1

Nested Loop (cost=125,536.23..162,533.29 rows=78 width=36) (actual time=562.492..799.104 rows=4,465 loops=1)

  • Join Filter: (tk.cf_course_of_interest = zcr.zd_fid)
  • Rows Removed by Join Filter: 95208
13. 19.078 785.387 ↓ 10.2 3,437 1

Nested Loop (cost=125,536.23..162,384.90 rows=338 width=70) (actual time=562.480..785.387 rows=3,437 loops=1)

14. 50.665 729.651 ↓ 5.6 18,329 1

Subquery Scan on ta (cost=125,535.80..138,238.35 rows=3,259 width=16) (actual time=562.432..729.651 rows=18,329 loops=1)

  • Filter: (ta.assignee_id = '381290504873'::bigint)
  • Rows Removed by Filter: 761504
15. 102.286 678.986 ↓ 1.2 779,833 1

Unique (cost=125,535.80..130,089.92 rows=651,874 width=24) (actual time=473.657..678.986 rows=779,833 loops=1)

16. 456.547 576.700 ↓ 1.0 910,882 1

Sort (cost=125,535.80..127,812.86 rows=910,824 width=24) (actual time=473.656..576.700 rows=910,882 loops=1)

  • Sort Key: ta_1.ticket_id, ta_1.created_at DESC
  • Sort Method: external merge Disk: 30328kB
17. 120.153 120.153 ↓ 1.0 910,882 1

Seq Scan on tickets_assignees ta_1 (cost=0.00..16,699.24 rows=910,824 width=24) (actual time=0.004..120.153 rows=910,882 loops=1)

18. 36.658 36.658 ↓ 0.0 0 18,329

Index Scan using tickets_pkey on tickets tk (cost=0.42..7.41 rows=1 width=62) (actual time=0.002..0.002 rows=0 loops=18,329)

  • Index Cond: (id = ta.ticket_id)
  • Filter: ((status <> 'deleted'::text) AND (type = 'problem'::text) AND (brand_fid = '114095191753'::bigint))
  • Rows Removed by Filter: 1
19. 6.865 6.874 ↑ 1.0 29 3,437

Materialize (cost=0.00..1.44 rows=29 width=51) (actual time=0.000..0.002 rows=29 loops=3,437)

20. 0.009 0.009 ↑ 1.0 29 1

Seq Scan on courses zcr (cost=0.00..1.29 rows=29 width=51) (actual time=0.005..0.009 rows=29 loops=1)

21. 17.914 26.790 ↑ 1.0 1 4,465

Index Scan using raw_users_pkey on users zcu (cost=0.42..10.24 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=4,465)

  • Index Cond: (id = tk.requester_id)
  • Filter: ((mail IS NULL) OR ((mail !~~ '%@ilernaonline.com'::citext) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))))
  • Rows Removed by Filter: 0
22.          

SubPlan (for Index Scan)

23. 8.876 8.876 ↓ 0.0 0 4,438

Index Only Scan using blacklisted_mails_pkey on blacklisted_mails be (cost=0.14..4.16 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=4,438)

  • Index Cond: (mail = (zcu.mail)::text)
  • Heap Fetches: 0
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on blacklisted_mails be_1 (cost=0.00..3.87 rows=187 width=21) (never executed)

25. 84.797 84.797 ↓ 0.0 0 4,463

Index Scan using customers_mail_idx on customers cu (cost=0.41..5.94 rows=1 width=28) (actual time=0.019..0.019 rows=0 loops=4,463)

  • Index Cond: (mail = zcu.mail)
26. 3.924 3.924 ↑ 1.0 1 1,308

Index Scan using students_customer_id_idx on students st (cost=0.29..9.39 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=1,308)

  • Index Cond: (customer_id = cu.id)
  • Filter: is_new
  • Rows Removed by Filter: 0
27. 10.857 10.857 ↑ 1.0 1 517

Index Only Scan using raw_users_pkey on users zag (cost=0.42..8.44 rows=1 width=8) (actual time=0.013..0.021 rows=1 loops=517)

  • Index Cond: (id = '381290504873'::bigint)
  • Heap Fetches: 16544
28. 2.585 2.585 ↑ 1.0 1 517

Index Scan using orders_customer_id_idx on orders od (cost=0.29..12.63 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=517)

  • Index Cond: (customer_id = st.customer_id)
  • Filter: (st.term_id = term_id)
  • Rows Removed by Filter: 1
29. 0.682 199.144 ↓ 0.0 0 682

Subquery Scan on os (cost=9.84..9.87 rows=1 width=12) (actual time=0.292..0.292 rows=0 loops=682)

  • Filter: ((os.created_at >= '2019-11-01 00:00:00+01'::timestamp with time zone) AND (os.created_at < '2019-12-01 00:00:00+01'::timestamp with time zone) AND ((os.order_state_fid = ANY ('{2,4,16,17}'::integer[])) OR (os.order_state_fid = 10)))
  • Rows Removed by Filter: 1
30. 0.000 198.462 ↑ 1.0 1 682

Unique (cost=9.84..9.84 rows=1 width=16) (actual time=0.291..0.291 rows=1 loops=682)

31. 2.046 198.462 ↑ 1.0 1 682

Sort (cost=9.84..9.84 rows=1 width=16) (actual time=0.291..0.291 rows=1 loops=682)

  • Sort Key: oss.created_at DESC
  • Sort Method: quicksort Memory: 25kB
32. 4.092 196.416 ↑ 1.0 1 682

Hash Join (cost=8.45..9.83 rows=1 width=16) (actual time=0.285..0.288 rows=1 loops=682)

  • Hash Cond: (os_1.id = oss.order_state_id)
33. 2.046 2.046 ↓ 1.0 28 682

Seq Scan on order_states os_1 (cost=0.00..1.27 rows=27 width=8) (actual time=0.001..0.003 rows=28 loops=682)

34. 2.046 190.278 ↑ 1.0 1 682

Hash (cost=8.44..8.44 rows=1 width=16) (actual time=0.279..0.279 rows=1 loops=682)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 188.232 188.232 ↑ 1.0 1 682

Index Scan using orders_states_order_id_idx on orders_states oss (cost=0.42..8.44 rows=1 width=16) (actual time=0.270..0.276 rows=1 loops=682)

  • Index Cond: (order_id = od.id)
36. 2.907 2.907 ↓ 0.0 0 51

Index Scan using tickets_events_ticket_id_idx on tickets_events te (cost=0.43..34.00 rows=1 width=8) (actual time=0.057..0.057 rows=0 loops=51)

  • Index Cond: (ticket_id = tk.id)
  • Filter: (child_events @> '[{"via": "Merge", "status": "closed"}]'::jsonb)
  • Rows Removed by Filter: 22
37. 0.007 0.016 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=8) (actual time=0.016..0.016 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
38. 0.009 0.009 ↑ 1.0 33 1

Seq Scan on shops sh (cost=0.00..1.33 rows=33 width=8) (actual time=0.005..0.009 rows=33 loops=1)

39. 0.306 0.306 ↓ 3.0 3 51

Index Scan using orders_lines_order_id_idx on orders_lines ol (cost=0.42..6.18 rows=1 width=13) (actual time=0.004..0.006 rows=3 loops=51)

  • Index Cond: (order_id = od.id)
  • Filter: (quantity > refunded)
40. 0.348 0.348 ↑ 1.0 1 174

Index Scan using products_pkey on products pr (cost=0.28..5.67 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=174)

  • Index Cond: (id = ol.product_id)
  • Filter: ((revenue_category = ANY ('{Matriculas,Libros}'::text[])) OR (shop_fid = 970))
Planning time : 7.501 ms
Execution time : 1,152.029 ms