explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gMp3

Settings
# exclusive inclusive rows x rows loops node
1. 550.686 42,027.475 ↓ 10.0 10 1

Unique (cost=8,608.81..8,611.99 rows=1 width=36) (actual time=39,065.572..42,027.475 rows=10 loops=1)

2.          

Initplan (for Unique)

3. 0.004 0.004 ↑ 1.0 1 1

Index Scan using services_pkey on services s (cost=0.28..8.29 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (id = 1732)
4. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2,021.32..2,021.33 rows=1 width=4) (never executed)

5. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on user_service_map (cost=18.01..2,019.99 rows=532 width=4) (never executed)

  • Recheck Cond: ((fsa_id = 623) AND (service_id = 1732))
6. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on user_service_map_pkey (cost=0.00..17.88 rows=532 width=0) (never executed)

  • Index Cond: ((fsa_id = 623) AND (service_id = 1732))
7. 15,412.943 41,476.785 ↓ 3,555.7 2,261,454 1

Sort (cost=6,579.19..6,580.78 rows=636 width=36) (actual time=39,065.570..41,476.785 rows=2,261,454 loops=1)

  • Sort Key: a_appointments_6.app_start, (CASE WHEN (($0)::text = 'PRIORITIZED'::text) THEN ((COALESCE(a_user_service_map_33.priority, 0))::numeric - (((a_user_service_map_33.ttl_leads)::numeric / ($1)::numeric) * 100::numeric)) ELSE 1::numeric END), (COALESCE((a_user_service_map_33.max_date)::timestamp with time zone, (now() + ((((random() * 19000::double precision))::text || ' seconds - 10 years'::text))::interval)))
  • Sort Method: external merge Disk: 128200kB
8. 25,194.106 26,063.842 ↓ 3,555.7 2,261,454 1

Nested Loop (cost=3,888.71..6,549.58 rows=636 width=36) (actual time=24.498..26,063.842 rows=2,261,454 loops=1)

9. 0.085 19.406 ↓ 26.0 26 1

Nested Loop (cost=3,836.54..3,845.11 rows=1 width=248) (actual time=17.906..19.406 rows=26 loops=1)

10. 0.000 17.917 ↓ 26.0 26 1

HashAggregate (cost=3,836.12..3,836.13 rows=1 width=106) (actual time=17.863..17.917 rows=26 loops=1)

  • Group Key: a_appointments_6.id, a_appointments_6.app_start, a_appointments_6.app_end, a_appointments_6.travel, a_appointments_6.duration, a_appointments_6.owner_id, a_appointments_6.lead_id, a_appointments_6.state, a_appointments_6.reserved_on, a_appointments_6.emp_id, a_appointments_6.reserved_by, a_appointments_6.parked, a_appointments_6.app_type, a_appointments_6.blocker, a_appointments_6.original_app_end, a_appointments_6.twin_id
11.          

Initplan (for HashAggregate)

12. 0.038 0.509 ↑ 4.7 94 1

Append (cost=0.00..380.46 rows=440 width=4) (actual time=0.001..0.509 rows=94 loops=1)

13. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

14. 0.096 0.470 ↑ 4.7 93 1

Nested Loop (cost=12.70..376.05 rows=439 width=4) (actual time=0.046..0.470 rows=93 loops=1)

15. 0.038 0.188 ↑ 4.7 93 1

Nested Loop (cost=12.42..186.34 rows=439 width=4) (actual time=0.041..0.188 rows=93 loops=1)

16. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on core_groups c (cost=0.00..1.93 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

  • Filter: ((name)::text = 'b2_special'::text)
  • Rows Removed by Filter: 73
17. 0.120 0.138 ↑ 5.7 93 1

Bitmap Heap Scan on core_user_groups cu (cost=12.42..179.08 rows=533 width=8) (actual time=0.027..0.138 rows=93 loops=1)

  • Recheck Cond: (group_id = c.id)
  • Heap Blocks: exact=70
18. 0.018 0.018 ↑ 5.1 105 1

Bitmap Index Scan on core_user_groups_group_id_idx (cost=0.00..12.29 rows=533 width=0) (actual time=0.018..0.018 rows=105 loops=1)

  • Index Cond: (group_id = c.id)
19. 0.186 0.186 ↑ 1.0 1 93

Index Only Scan using users_pkey on users u (cost=0.29..0.42 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=93)

  • Index Cond: (id = cu.user_id)
  • Heap Fetches: 93
20. 0.002 0.884 ↑ 1,002.0 1 1

Append (cost=0.00..68.98 rows=1,002 width=4) (actual time=0.007..0.884 rows=1 loops=1)

21. 0.007 0.007 ↑ 1,000.0 1 1

Function Scan on generate_series idx (cost=0.00..10.00 rows=1,000 width=4) (actual time=0.007..0.007 rows=1 loops=1)

22. 0.001 0.010 ↓ 0.0 0 1

Nested Loop (cost=0.00..27.68 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)

23. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on user_map_object_access_cache access (cost=0.00..1.70 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: (((access_intent)::text = 'read'::text) AND ((object_type)::text = 'leads'::text) AND ((axis)::text = 'child'::text) AND ((user_disposition)::text = 'owner'::text))
  • Rows Removed by Filter: 35
24. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..25.94 rows=4 width=12) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_map_full map (cost=0.00..0.00 rows=1 width=12) (never executed)

  • Filter: ((child_id = ANY ('{52345}'::integer[])) AND (access.parent_group_id = parent_group_id) AND (access.child_group_id = child_group_id))
26. 0.000 0.000 ↓ 0.0 0

Index Scan using user_map_full_11_child_id_key on user_map_full_11 map_1 (cost=0.15..8.18 rows=1 width=12) (never executed)

  • Index Cond: ((child_id = ANY ('{52345}'::integer[])) AND (parent_group_id = access.parent_group_id))
  • Filter: (access.child_group_id = child_group_id)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using user_map_full_m1_pk on user_map_full_m1 map_2 (cost=0.27..8.30 rows=1 width=12) (never executed)

  • Index Cond: (child_id = ANY ('{52345}'::integer[]))
  • Filter: ((access.parent_group_id = parent_group_id) AND (access.child_group_id = child_group_id))
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_map_full_mm map_3 (cost=0.00..9.47 rows=1 width=12) (never executed)

  • Filter: ((child_id = ANY ('{52345}'::integer[])) AND (access.parent_group_id = parent_group_id) AND (access.child_group_id = child_group_id))
29. 0.008 0.865 ↓ 0.0 0 1

Nested Loop (cost=0.00..31.28 rows=1 width=4) (actual time=0.865..0.865 rows=0 loops=1)

30. 0.012 0.012 ↓ 13.0 13 1

Seq Scan on user_map_object_access_cache access_1 (cost=0.00..1.70 rows=1 width=8) (actual time=0.003..0.012 rows=13 loops=1)

  • Filter: (((access_intent)::text = 'read'::text) AND ((object_type)::text = 'leads'::text) AND ((axis)::text = 'parent'::text) AND ((user_disposition)::text = 'owner'::text))
  • Rows Removed by Filter: 22
31. 0.039 0.845 ↓ 0.0 0 13

Append (cost=0.00..29.54 rows=4 width=12) (actual time=0.065..0.065 rows=0 loops=13)

32. 0.000 0.000 ↓ 0.0 0 13

Seq Scan on user_map_full map_4 (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=13)

  • Filter: ((parent_id = ANY ('{52345}'::integer[])) AND (access_1.parent_group_id = parent_group_id) AND (access_1.child_group_id = child_group_id))
33. 0.013 0.013 ↓ 0.0 0 13

Index Scan using user_map_full_11_parent_id_key on user_map_full_11 map_5 (cost=0.15..8.18 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=13)

  • Index Cond: ((parent_id = ANY ('{52345}'::integer[])) AND (child_group_id = access_1.child_group_id))
  • Filter: (access_1.parent_group_id = parent_group_id)
34. 0.507 0.507 ↓ 0.0 0 13

Seq Scan on user_map_full_m1 map_6 (cost=0.00..11.90 rows=1 width=12) (actual time=0.039..0.039 rows=0 loops=13)

  • Filter: ((parent_id = ANY ('{52345}'::integer[])) AND (access_1.parent_group_id = parent_group_id) AND (access_1.child_group_id = child_group_id))
  • Rows Removed by Filter: 486
35. 0.286 0.286 ↓ 0.0 0 13

Seq Scan on user_map_full_mm map_7 (cost=0.00..9.47 rows=1 width=12) (actual time=0.022..0.022 rows=0 loops=13)

  • Filter: ((parent_id = ANY ('{52345}'::integer[])) AND (access_1.parent_group_id = parent_group_id) AND (access_1.child_group_id = child_group_id))
  • Rows Removed by Filter: 275
36. 0.628 17.834 ↓ 26.0 26 1

Nested Loop Left Join (cost=359.23..3,386.65 rows=1 width=106) (actual time=8.429..17.834 rows=26 loops=1)

  • Filter: (a_appointments_6.app_start >= CASE WHEN ('2019-11-27'::date <> ('now'::cstring)::date) THEN '2019-11-27 00:00:00-05'::timestamp with time zone WHEN (a_services_confirmation_defaults_11.appt_delay IS NOT NULL) THEN (now() + a_services_confirmation_defaults_11.appt_delay) ELSE (now() + CASE WHEN ('PC'::text <> ALL ('{SP,ESP,EMP,PC,D,SSD}'::text[])) THEN '2 days'::interval ELSE '00:00:00'::interval END) END)
  • Rows Removed by Filter: 922
37. 0.629 16.258 ↓ 948.0 948 1

Nested Loop (cost=359.08..3,384.94 rows=1 width=110) (actual time=1.721..16.258 rows=948 loops=1)

38. 1.405 13.733 ↓ 948.0 948 1

Nested Loop (cost=358.80..3,378.09 rows=1 width=110) (actual time=1.716..13.733 rows=948 loops=1)

39. 1.047 8.536 ↓ 948.0 948 1

Nested Loop (cost=358.52..3,365.56 rows=1 width=110) (actual time=1.701..8.536 rows=948 loops=1)

40. 2.132 3.799 ↓ 3.7 1,230 1

Bitmap Heap Scan on appointments a_appointments_6 (cost=358.10..920.69 rows=329 width=106) (actual time=1.689..3.799 rows=1,230 loops=1)

  • Recheck Cond: ((owner_id = ANY ($6)) AND (owner_id = ANY ($11)))
  • Filter: (((parked IS NULL) OR ((parked)::smallint = 0)) AND ((state)::smallint = 0::smallint) AND (app_start <= COALESCE(('2019-11-27 23:59:59'::timestamp without time zone)::timestamp with time zone, (now() + '40 days'::interval))) AND CASE WHEN ((app_start)::date = ('now'::cstring)::date) THEN (app_start > now()) ELSE true END)
  • Rows Removed by Filter: 2294
  • Heap Blocks: exact=114
41. 1.667 1.667 ↓ 4.0 3,616 1

Bitmap Index Scan on appointments_sp_id_idx (cost=0.00..358.02 rows=903 width=0) (actual time=1.667..1.667 rows=3,616 loops=1)

  • Index Cond: ((owner_id = ANY ($6)) AND (owner_id = ANY ($11)))
42. 3.690 3.690 ↑ 1.0 1 1,230

Index Scan using appointment_map_pkey on appointment_map a_appointment_map_9 (cost=0.42..7.42 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1,230)

  • Index Cond: ((app_id = a_appointments_6.id) AND (service_id = ANY ('{1732}'::integer[])))
  • Filter: ((block_id IS NULL) AND (fsas @> '{623}'::integer[]))
  • Rows Removed by Filter: 0
43. 1.896 3.792 ↑ 1.0 1 948

Index Scan using users_pkey on users a_users_7 (cost=0.29..12.52 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=948)

  • Index Cond: (id = a_appointments_6.owner_id)
  • Filter: (((active)::smallint = 1) AND (id = (SubPlan 4)))
44.          

SubPlan (for Index Scan)

45. 0.948 1.896 ↑ 1.0 1 948

Limit (cost=0.29..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=948)

46. 0.948 0.948 ↑ 1.0 1 948

Index Only Scan using users_client_assoc_pkey on users_client_assoc (cost=0.29..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=948)

  • Index Cond: (user_id = a_users_7.id)
  • Filter: (client_id = ANY ('{1}'::integer[]))
  • Heap Fetches: 948
47. 1.896 1.896 ↑ 1.0 1 948

Index Scan using services_pkey on services a_services_10 (cost=0.28..6.84 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=948)

  • Index Cond: (id = a_appointment_map_9.service_id)
  • Filter: (client_id = ANY ('{1}'::integer[]))
48. 0.948 0.948 ↓ 0.0 0 948

Index Scan using services_confirmation_defaults_service_id_confirmation_type_idx on services_confirmation_defaults a_services_confirmation_defaults_11 (cost=0.14..1.63 rows=2 width=20) (actual time=0.001..0.001 rows=0 loops=948)

  • Index Cond: (service_id = a_appointment_map_9.service_id)
49. 0.858 1.404 ↑ 1.0 1 26

Index Scan using appointment_map_pkey on appointment_map asm (cost=0.42..8.95 rows=1 width=232) (actual time=0.051..0.054 rows=1 loops=26)

  • Index Cond: ((app_id = a_appointments_6.id) AND (service_id = 1732))
  • Filter: (array_remove((SubPlan 3), NULL::integer) @> '{623}'::integer[])
50.          

SubPlan (for Index Scan)

51. 0.546 0.546 ↑ 1.6 61 26

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.007..0.021 rows=61 loops=26)

52. 682.240 850.330 ↓ 136.8 86,979 26

Bitmap Heap Scan on user_service_map a_user_service_map_33 (cost=52.17..2,337.18 rows=636 width=24) (actual time=6.540..32.705 rows=86,979 loops=26)

  • Recheck Cond: ((fsa_id = ANY (asm.fsas)) AND (service_id = 1732))
  • Heap Blocks: exact=15184
53. 168.090 168.090 ↓ 136.8 86,984 26

Bitmap Index Scan on user_service_map_pkey (cost=0.00..52.01 rows=636 width=0) (actual time=6.465..6.465 rows=86,984 loops=26)

  • Index Cond: ((fsa_id = ANY (asm.fsas)) AND (service_id = 1732))
Planning time : 2.650 ms
Execution time : 42,047.000 ms