explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q1hA

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.389 73,002.123 ↑ 1.0 1 1

Aggregate (cost=181,677.65..181,677.66 rows=1 width=32) (actual time=73,002.122..73,002.123 rows=1 loops=1)

2. 0.780 73,001.734 ↓ 174.0 174 1

Nested Loop (cost=140,742.55..181,677.64 rows=1 width=5) (actual time=8,819.711..73,001.734 rows=174 loops=1)

3. 0.512 72,999.910 ↓ 174.0 174 1

Hash Join (cost=140,742.27..181,671.96 rows=1 width=9) (actual time=8,819.687..72,999.910 rows=174 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)))
4. 0.872 72,999.383 ↓ 174.0 174 1

Nested Loop Anti Join (cost=140,740.53..181,670.21 rows=1 width=17) (actual time=8,819.661..72,999.383 rows=174 loops=1)

5. 463.805 72,985.635 ↓ 174.0 174 1

Nested Loop (cost=140,740.10..181,602.65 rows=1 width=25) (actual time=8,819.558..72,985.635 rows=174 loops=1)

  • Join Filter: ((os.created_at >= st.student_since) AND (ol.order_id = os.order_id) AND (os.created_at <= (st.student_since + '30 days'::interval)))
  • Rows Removed by Join Filter: 7469331
6. 4.649 1,049.142 ↓ 2,224.0 2,224 1

Nested Loop (cost=125,538.48..163,749.75 rows=1 width=37) (actual time=604.409..1,049.142 rows=2,224 loops=1)

7. 2.633 1,027.443 ↓ 682.0 682 1

Nested Loop (cost=125,538.06..163,743.55 rows=1 width=24) (actual time=604.393..1,027.443 rows=682 loops=1)

8. 2.065 1,019.640 ↓ 517.0 517 1

Nested Loop (cost=125,537.77..163,730.91 rows=1 width=28) (actual time=604.376..1,019.640 rows=517 loops=1)

9. 2.813 996.895 ↓ 517.0 517 1

Nested Loop (cost=125,537.35..163,722.46 rows=1 width=36) (actual time=604.349..996.895 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.482 987.542 ↓ 163.5 1,308 1

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

11. 5.588 892.800 ↓ 84.2 4,463 1

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

12. 8.882 847.027 ↓ 57.2 4,465 1

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

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

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

14. 54.164 767.021 ↓ 5.6 18,329 1

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

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

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

16. 488.800 611.016 ↓ 1.0 910,882 1

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

  • Sort Key: ta_1.ticket_id, ta_1.created_at DESC
  • Sort Method: external merge Disk: 30328kB
17. 122.216 122.216 ↓ 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.005..122.216 rows=910,882 loops=1)

18. 54.987 54.987 ↓ 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.003..0.003 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.863 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.011 0.011 ↑ 1.0 29 1

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

21. 31.309 40.185 ↑ 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.009..0.009 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. 89.260 89.260 ↓ 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.020..0.020 rows=0 loops=4,463)

  • Index Cond: (mail = zcu.mail)
26. 6.540 6.540 ↑ 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.004..0.005 rows=1 loops=1,308)

  • Index Cond: (customer_id = cu.id)
  • Filter: is_new
  • Rows Removed by Filter: 0
27. 20.680 20.680 ↑ 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.040 rows=1 loops=517)

  • Index Cond: (id = '381290504873'::bigint)
  • Heap Fetches: 16544
28. 5.170 5.170 ↑ 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.007..0.010 rows=1 loops=517)

  • Index Cond: (customer_id = st.customer_id)
  • Filter: (st.term_id = term_id)
  • Rows Removed by Filter: 1
29. 17.050 17.050 ↓ 3.0 3 682

Index Scan using orders_lines_order_id_idx on orders_lines ol (cost=0.42..6.18 rows=1 width=13) (actual time=0.007..0.025 rows=3 loops=682)

  • Index Cond: (order_id = od.id)
  • Filter: (quantity > refunded)
  • Rows Removed by Filter: 0
30. 15,745.920 71,472.688 ↓ 305.4 3,359 2,224

Subquery Scan on os (cost=15,201.61..17,852.68 rows=11 width=16) (actual time=25.002..32.137 rows=3,359 loops=2,224)

  • 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: 96981
31. 31,431.792 55,726.768 ↓ 1.1 100,340 2,224

Unique (cost=15,201.61..15,828.69 rows=89,955 width=16) (actual time=0.050..25.057 rows=100,340 loops=2,224)

32. 24,255.244 24,294.976 ↑ 1.0 124,262 2,224

Sort (cost=15,201.61..15,515.15 rows=125,416 width=16) (actual time=0.050..10.924 rows=124,262 loops=2,224)

  • Sort Key: oss.order_id, oss.created_at DESC
  • Sort Method: external sort Disk: 3688kB
33. 28.222 39.732 ↑ 1.0 125,293 1

Hash Join (cost=1.61..2,435.66 rows=125,416 width=16) (actual time=0.046..39.732 rows=125,293 loops=1)

  • Hash Cond: (oss.order_state_id = os_1.id)
34. 11.481 11.481 ↑ 1.0 125,293 1

Seq Scan on orders_states oss (cost=0.00..2,053.16 rows=125,416 width=16) (actual time=0.005..11.481 rows=125,293 loops=1)

35. 0.023 0.029 ↓ 1.0 28 1

Hash (cost=1.27..1.27 rows=27 width=8) (actual time=0.029..0.029 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.006 0.006 ↓ 1.0 28 1

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

37. 12.876 12.876 ↓ 0.0 0 174

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
39. 0.008 0.008 ↑ 1.0 33 1

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

40. 1.044 1.044 ↑ 1.0 1 174

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

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