explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Qf9

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 441.129 ↑ 1.0 1 1

Limit (cost=35,181.28..35,181.29 rows=1 width=20) (actual time=441.129..441.129 rows=1 loops=1)

2.          

Initplan (for Limit)

3. 0.005 0.005 ↑ 1.0 1 1

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

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

Result (cost=229.64..229.65 rows=1 width=0) (never executed)

5.          

Initplan (for Result)

6. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=229.63..229.64 rows=1 width=4) (never executed)

7. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=229.61..229.62 rows=1 width=8) (never executed)

  • Group Key: user_service_map.provider_id, user_service_map.ttl_leads
8. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on user_service_map (cost=5.13..229.60 rows=1 width=8) (never executed)

  • Recheck Cond: ((fsa_id = 778) AND (service_id = 6))
  • Filter: (max_date >= date_trunc('month'::text, now()))
9. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((fsa_id = 778) AND (service_id = 6))
10. 0.000 422.764 ↓ 8,101.9 72,917 1

Nested Loop (cost=249.55..34,322.18 rows=9 width=4) (actual time=27.662..422.764 rows=72,917 loops=1)

11. 41.570 185.197 ↓ 7,450.7 119,211 1

Hash Join (cost=249.27..34,316.59 rows=16 width=4) (actual time=27.651..185.197 rows=119,211 loops=1)

  • Hash Cond: (a_user_service_map_26.provider_group_id = core_groups_1.id)
12. 20.644 143.606 ↓ 1,529.4 119,290 1

Nested Loop (cost=247.42..34,314.44 rows=78 width=8) (actual time=27.603..143.606 rows=119,290 loops=1)

13. 0.007 27.568 ↑ 1.0 1 1

Nested Loop (cost=246.99..34,309.55 rows=1 width=8) (actual time=27.563..27.568 rows=1 loops=1)

  • Join Filter: (a_services_6.service_type_id = a_service_types_7.id)
  • Rows Removed by Join Filter: 8
14. 0.003 27.557 ↑ 1.0 1 1

Nested Loop Semi Join (cost=246.99..34,308.35 rows=1 width=12) (actual time=27.555..27.557 rows=1 loops=1)

15. 0.009 0.009 ↑ 1.0 1 1

Index Scan using services_pkey on services a_services_6 (cost=0.28..8.30 rows=1 width=16) (actual time=0.007..0.009 rows=1 loops=1)

  • Index Cond: (id = ANY ('{6}'::integer[]))
  • Filter: ((client_id = ANY ('{1}'::integer[])) AND ((active)::smallint = 1::smallint))
16. 0.686 27.545 ↑ 1,415.0 1 1

Nested Loop (cost=246.71..34,300.03 rows=1,415 width=4) (actual time=27.545..27.545 rows=1 loops=1)

17. 0.343 25.929 ↑ 2.8 930 1

Hash Semi Join (cost=246.43..33,478.74 rows=2,584 width=8) (actual time=25.431..25.929 rows=930 loops=1)

  • Hash Cond: (map.provider_group_id = core_groups.id)
18. 7.006 25.570 ↑ 13.9 930 1

Bitmap Heap Scan on user_service_map map (cost=244.58..33,414.23 rows=12,922 width=12) (actual time=25.404..25.570 rows=930 loops=1)

  • Recheck Cond: (service_id = a_services_6.id)
  • Heap Blocks: exact=9
19. 18.564 18.564 ↓ 9.2 119,290 1

Bitmap Index Scan on user_service_map_service_id_provider_id_idx (cost=0.00..241.35 rows=12,922 width=0) (actual time=18.564..18.564 rows=119,290 loops=1)

  • Index Cond: (service_id = a_services_6.id)
20. 0.000 0.016 ↑ 1.0 1 1

Hash (cost=1.83..1.83 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
21. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on core_groups (cost=0.00..1.83 rows=1 width=4) (actual time=0.007..0.016 rows=1 loops=1)

  • Filter: ((name)::text = ANY ('{SP}'::text[]))
  • Rows Removed by Filter: 73
22. 0.930 0.930 ↓ 0.0 0 930

Index Scan using users_pkey on users u (cost=0.29..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=930)

  • Index Cond: (id = map.provider_id)
  • Filter: ((active)::smallint = 1::smallint)
  • Rows Removed by Filter: 1
23. 0.004 0.004 ↑ 1.0 9 1

Seq Scan on service_types a_service_types_7 (cost=0.00..1.09 rows=9 width=4) (actual time=0.004..0.004 rows=9 loops=1)

24. 95.394 95.394 ↓ 877.1 119,290 1

Index Scan using user_service_map_service_id_idx on user_service_map a_user_service_map_26 (cost=0.43..3.53 rows=136 width=12) (actual time=0.035..95.394 rows=119,290 loops=1)

  • Index Cond: ((service_id = map.service_id) AND (service_id = ANY ('{6}'::integer[])))
25. 0.001 0.021 ↑ 1.0 1 1

Hash (cost=1.83..1.83 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
26. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on core_groups core_groups_1 (cost=0.00..1.83 rows=1 width=4) (actual time=0.011..0.020 rows=1 loops=1)

  • Filter: ((name)::text = ANY ('{SP}'::text[]))
  • Rows Removed by Filter: 73
27. 238.422 238.422 ↑ 1.0 1 119,211

Index Scan using users_pkey on users a_users_28 (cost=0.29..0.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=119,211)

  • Index Cond: (id = a_user_service_map_26.provider_id)
  • Filter: ((active)::smallint = 1::smallint)
  • Rows Removed by Filter: 0
28. 0.036 441.129 ↑ 1.0 1 1

Sort (cost=621.15..621.16 rows=1 width=20) (actual time=441.129..441.129 rows=1 loops=1)

  • Sort Key: (CASE WHEN (($0)::text = 'PRIORITIZED'::text) THEN ((COALESCE(m.priority, 0))::numeric - (((sum(CASE WHEN (m.max_date >= date_trunc('month'::text, now())) THEN m.ttl_leads ELSE 0 END))::numeric / ($2)::numeric) * 100::numeric)) ELSE 1::numeric END), (COALESCE(max(m.max_date), (now() + ((((random() * 10000::double precision))::text || ' seconds -10 years'::text))::interval)))
  • Sort Method: quicksort Memory: 25kB
29. 0.134 441.093 ↓ 2.0 2 1

HashAggregate (cost=621.09..621.14 rows=1 width=20) (actual time=441.093..441.093 rows=2 loops=1)

  • Group Key: m.provider_id, m.priority
30. 0.245 440.959 ↓ 158.0 158 1

Nested Loop (cost=584.68..621.07 rows=1 width=20) (actual time=439.590..440.959 rows=158 loops=1)

  • Join Filter: (a_users_client_assoc_2.client_id = a_clients_3.id)
  • Rows Removed by Join Filter: 1422
31. 0.000 440.398 ↓ 158.0 158 1

Nested Loop (cost=584.68..619.87 rows=1 width=24) (actual time=439.583..440.398 rows=158 loops=1)

  • Join Filter: (a_users_1.id = a_users_client_assoc_2.user_id)
32. 0.056 440.082 ↓ 158.0 158 1

Nested Loop (cost=584.39..613.07 rows=1 width=24) (actual time=439.580..440.082 rows=158 loops=1)

33. 0.104 439.690 ↓ 48.0 48 1

Bitmap Heap Scan on users a_users_1 (cost=46.66..67.30 rows=1 width=4) (actual time=439.352..439.690 rows=48 loops=1)

  • Recheck Cond: (((role)::text = ANY ('{SP}'::text[])) AND ((active)::smallint = 1::smallint) AND (id = ANY ($9)))
  • Filter: ((SubPlan 5) AND (id = (SubPlan 4)))
  • Heap Blocks: exact=45
34. 0.029 439.298 ↓ 0.0 0 1

BitmapAnd (cost=46.66..46.66 rows=1 width=0) (actual time=439.298..439.298 rows=0 loops=1)

35. 0.105 0.105 ↓ 1.1 358 1

Bitmap Index Scan on users_role_active_idx (cost=0.00..7.47 rows=319 width=0) (actual time=0.105..0.105 rows=358 loops=1)

  • Index Cond: (((role)::text = ANY ('{SP}'::text[])) AND ((active)::smallint = 1::smallint))
36. 439.164 439.164 ↓ 4.8 48 1

Bitmap Index Scan on users_pkey (cost=0.00..38.94 rows=10 width=0) (actual time=439.164..439.164 rows=48 loops=1)

  • Index Cond: (id = ANY ($9))
37.          

SubPlan (for Bitmap Heap Scan)

38. 0.048 0.192 ↑ 1.0 1 48

Limit (cost=0.29..8.30 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=48)

39. 0.144 0.144 ↑ 1.0 1 48

Index Only Scan using users_client_assoc_pkey on users_client_assoc c1 (cost=0.29..8.30 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=48)

  • Index Cond: (user_id = a_users_1.id)
  • Filter: (client_id = ANY ('{1}'::integer[]))
  • Heap Fetches: 48
40. 0.000 0.096 ↑ 1.0 1 48

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

41. 0.096 0.096 ↑ 1.0 1 48

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

  • Index Cond: (user_id = a_users_1.id)
  • Filter: (client_id = ANY ('{1}'::integer[]))
  • Heap Fetches: 48
42. 0.336 0.336 ↓ 3.0 3 48

Index Scan using user_service_map_pkey on user_service_map m (cost=537.73..545.76 rows=1 width=20) (actual time=0.003..0.007 rows=3 loops=48)

  • Index Cond: ((fsa_id = 778) AND (service_id = 6) AND (provider_id = a_users_1.id))
  • Filter: CASE WHEN ('D'::text = ANY ('{SP}'::text[])) THEN (hashed SubPlan 7) ELSE true END
43.          

SubPlan (for Index Scan)

44. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..537.17 rows=1 width=4) (never executed)

  • One-Time Filter: NULL::boolean
45. 0.000 0.000 ↓ 0.0 0

Seq Scan on store_assoc (cost=0.00..537.17 rows=1 width=4) (never executed)

46. 0.316 0.316 ↑ 1.0 1 158

Index Scan using users_client_assoc_pkey on users_client_assoc a_users_client_assoc_2 (cost=0.29..6.79 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=158)

  • Index Cond: (user_id = m.provider_id)
  • Filter: ((main)::smallint = 1)
47. 0.316 0.316 ↓ 1.1 10 158

Seq Scan on clients a_clients_3 (cost=0.00..1.09 rows=9 width=4) (actual time=0.001..0.002 rows=10 loops=158)

Planning time : 6.281 ms
Execution time : 441.811 ms