explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4APR

Settings
# exclusive inclusive rows x rows loops node
1. 158.801 297,831.099 ↓ 1,320.0 1,320 1

Nested Loop Left Join (cost=33,315.81..36,268.38 rows=1 width=89) (actual time=213.576..297,831.099 rows=1,320 loops=1)

  • Join Filter: (((CASE WHEN ((u.role)::text = ANY ('{SAT,PC,LPC}'::text[])) THEN u.id ELSE a_lead_owner_13.owner_id END) = a_users_1.id) AND (c.reason_id = a_disposition_codes_16.id))
  • Rows Removed by Join Filter: 1,319
2. 3.645 16.258 ↓ 1,320.0 1,320 1

Nested Loop (cost=264.75..3,216.21 rows=1 width=65) (actual time=0.180..16.258 rows=1,320 loops=1)

3. 1.371 8.653 ↓ 1,320.0 1,320 1

Nested Loop (cost=264.62..3,216.05 rows=1 width=69) (actual time=0.176..8.653 rows=1,320 loops=1)

4. 0.772 6.127 ↓ 165.0 165 1

Nested Loop (cost=258.06..3,209.42 rows=1 width=40) (actual time=0.119..6.127 rows=165 loops=1)

5. 0.953 1.890 ↑ 1.0 165 1

Hash Join (cost=257.78..374.50 rows=165 width=12) (actual time=0.098..1.890 rows=165 loops=1)

  • Hash Cond: (a_users_client_assoc_2.user_id = a_user_map_full_9.child_id)
6. 0.855 0.855 ↓ 1.0 4,535 1

Seq Scan on users_client_assoc a_users_client_assoc_2 (cost=0.00..92.41 rows=4,533 width=8) (actual time=0.007..0.855 rows=4,535 loops=1)

  • Filter: ((main)::smallint = 1)
  • Rows Removed by Filter: 540
7. 0.018 0.082 ↑ 1.0 165 1

Hash (cost=255.68..255.68 rows=168 width=4) (actual time=0.081..0.082 rows=165 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
8. 0.012 0.064 ↑ 1.0 165 1

Append (cost=0.00..255.68 rows=168 width=4) (actual time=0.024..0.064 rows=165 loops=1)

9. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on user_map_full a_user_map_full_9 (cost=0.00..0.00 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (((edge_type)::text = 'MCSP'::text) AND (parent_id = 5,338))
10. 0.003 0.003 ↓ 0.0 0 1

Index Scan using user_map_full_11_parent_id_key on user_map_full_11 a_user_map_full_9_1 (cost=0.15..8.21 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((parent_id = 5,338) AND ((edge_type)::text = 'MCSP'::text))
11. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on user_map_full_m1 a_user_map_full_9_2 (cost=4.30..10.75 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (parent_id = 5,338)
  • Filter: ((edge_type)::text = 'MCSP'::text)
12. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on user_map_full_m1_parent_id_child_group_id_idx (cost=0.00..4.29 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (parent_id = 5,338)
13. 0.032 0.044 ↑ 1.0 165 1

Bitmap Heap Scan on user_map_full_mm a_user_map_full_9_3 (cost=5.57..235.88 rows=165 width=4) (actual time=0.015..0.044 rows=165 loops=1)

  • Recheck Cond: (parent_id = 5,338)
  • Filter: ((edge_type)::text = 'MCSP'::text)
  • Heap Blocks: exact=6
14. 0.012 0.012 ↓ 1.0 167 1

Bitmap Index Scan on user_map_full_mm_parent_id_child_group_id_idx (cost=0.00..5.52 rows=165 width=0) (actual time=0.012..0.012 rows=167 loops=1)

  • Index Cond: (parent_id = 5,338)
15. 1.485 3.465 ↑ 1.0 1 165

Index Scan using users_pkey on users a_users_1 (cost=0.28..17.16 rows=1 width=36) (actual time=0.021..0.021 rows=1 loops=165)

  • Index Cond: (id = a_users_client_assoc_2.user_id)
  • Filter: ((SubPlan 2) AND (id = (SubPlan 1)))
16.          

SubPlan (for Index Scan)

17. 0.495 1.320 ↑ 1.0 1 165

Limit (cost=0.28..8.30 rows=1 width=1) (actual time=0.007..0.008 rows=1 loops=165)

18. 0.825 0.825 ↑ 1.0 1 165

Index Only Scan using users_client_assoc_pkey on users_client_assoc c1 (cost=0.28..8.30 rows=1 width=1) (actual time=0.005..0.005 rows=1 loops=165)

  • Index Cond: (user_id = a_users_1.id)
  • Filter: (client_id = ANY ('{1,72}'::integer[]))
  • Heap Fetches: 156
19. 0.330 0.660 ↑ 1.0 1 165

Limit (cost=0.28..8.30 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=165)

20. 0.330 0.330 ↑ 1.0 1 165

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

  • Index Cond: (user_id = a_users_1.id)
  • Filter: (client_id = ANY ('{1,72}'::integer[]))
  • Heap Fetches: 156
21. 1.108 1.155 ↓ 2.7 8 165

Sort (cost=6.56..6.57 rows=3 width=29) (actual time=0.001..0.007 rows=8 loops=165)

  • Sort Key: a_disposition_codes_16.name
  • Sort Method: quicksort Memory: 25kB
22. 0.005 0.047 ↓ 2.7 8 1

Hash Semi Join (cost=3.89..6.54 rows=3 width=29) (actual time=0.039..0.047 rows=8 loops=1)

  • Hash Cond: (a_disposition_codes_16.id = disposition_codes_clients_assoc.dispcode_id)
23. 0.014 0.014 ↓ 2.8 11 1

Seq Scan on disposition_codes a_disposition_codes_16 (cost=0.00..2.59 rows=4 width=29) (actual time=0.007..0.014 rows=11 loops=1)

  • Filter: (((active)::smallint = '1'::smallint) AND ((type)::text = 'OT'::text) AND ((subtype)::text = 'COMPLAINT'::text))
  • Rows Removed by Filter: 80
24. 0.008 0.028 ↑ 1.0 77 1

Hash (cost=2.92..2.92 rows=77 width=4) (actual time=0.028..0.028 rows=77 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
25. 0.020 0.020 ↑ 1.0 77 1

Seq Scan on disposition_codes_clients_assoc (cost=0.00..2.92 rows=77 width=4) (actual time=0.005..0.020 rows=77 loops=1)

  • Filter: (client_id = ANY ('{1,72}'::integer[]))
  • Rows Removed by Filter: 77
26. 3.960 3.960 ↑ 1.0 1 1,320

Index Only Scan using clients_pkey on clients a_clients_3 (cost=0.13..0.15 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,320)

  • Index Cond: (id = a_users_client_assoc_2.client_id)
  • Heap Fetches: 0
27. 3.951 297,656.040 ↑ 7.0 1 1,320

Finalize GroupAggregate (cost=33,051.06..33,051.97 rows=7 width=16) (actual time=225.497..225.497 rows=1 loops=1,320)

  • Group Key: (CASE WHEN ((u.role)::text = ANY ('{SAT,PC,LPC}'::text[])) THEN u.id ELSE a_lead_owner_13.owner_id END), c.reason_id
28.          

Initplan (for Finalize GroupAggregate)

29. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on core_groups (cost=0.00..2.19 rows=1 width=4) (actual time=0.005..0.009 rows=1 loops=1)

  • Filter: ((name)::text = 'SP'::text)
  • Rows Removed by Filter: 94
30. 6,660.720 297,652.080 ↑ 3.0 2 1,320

Gather Merge (cost=33,048.88..33,049.64 rows=6 width=16) (actual time=225.491..225.494 rows=2 loops=1,320)

  • Workers Planned: 2
  • Params Evaluated: $3
  • Workers Launched: 2
31. 3.960 290,991.360 ↑ 3.0 1 3,960 / 3

Partial GroupAggregate (cost=32,048.85..32,048.92 rows=3 width=16) (actual time=220.448..220.448 rows=1 loops=3,960)

  • Group Key: (CASE WHEN ((u.role)::text = ANY ('{SAT,PC,LPC}'::text[])) THEN u.id ELSE a_lead_owner_13.owner_id END), c.reason_id
32. 30.360 290,987.400 ↑ 3.0 1 3,960 / 3

Sort (cost=32,048.85..32,048.86 rows=3 width=8) (actual time=220.444..220.445 rows=1 loops=3,960)

  • Sort Key: (CASE WHEN ((u.role)::text = ANY ('{SAT,PC,LPC}'::text[])) THEN u.id ELSE a_lead_owner_13.owner_id END), c.reason_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
33. 5.280 290,957.040 ↑ 3.0 1 3,960 / 3

Nested Loop (cost=211.45..32,048.83 rows=3 width=8) (actual time=196.452..220.422 rows=1 loops=3,960)

34. 0.000 290,943.840 ↑ 3.0 1 3,960 / 3

Nested Loop (cost=211.17..32,047.55 rows=3 width=12) (actual time=196.442..220.412 rows=1 loops=3,960)

35. 159.720 290,108.280 ↓ 1.0 336 3,960 / 3

Nested Loop (cost=210.88..31,941.05 rows=331 width=16) (actual time=1.666..219.779 rows=336 loops=3,960)

36. 126.720 288,618.000 ↓ 1.0 336 3,960 / 3

Nested Loop (cost=210.46..31,789.84 rows=331 width=20) (actual time=1.650..218.650 rows=336 loops=3,960)

  • Join Filter: (a_leads_12.id = a_lead_owner_13.lead_id)
37. 733.920 287,604.240 ↓ 1.0 336 3,960 / 3

Parallel Hash Semi Join (cost=210.03..31,626.80 rows=331 width=12) (actual time=1.643..217.882 rows=336 loops=3,960)

  • Hash Cond: (a_lead_owner_24.owner_id = map_3.child_id)
38. 3,062.400 286,830.720 ↑ 1.1 4,216 3,960 / 3

Nested Loop (cost=17.54..31,418.26 rows=4,710 width=16) (actual time=0.248..217.296 rows=4,216 loops=3,960)

39. 1,574.747 267,927.000 ↑ 1.0 4,000 3,960 / 3

Hash Join (cost=17.11..29,351.34 rows=4,181 width=8) (actual time=0.232..202.975 rows=4,000 loops=3,960)

  • Hash Cond: ((a_leads_12.status)::text = (a_lead_statuses_2.name)::text)
40. 92,511.281 266,317.920 ↑ 1.3 4,065 3,960 / 3

Hash Join (cost=14.71..29,334.13 rows=5,137 width=20) (actual time=0.185..201.756 rows=4,065 loops=3,960)

  • Hash Cond: (a_leads_12.store_id = a_stores_48.id)
  • Join Filter: ((a_leads_12.store_id = ANY ('{59}'::integer[])) OR (a_stores_48.district_id = ANY (NULL::integer[])) OR (a_stores_48.market_id = ANY (NULL::integer[])))
  • Rows Removed by Join Filter: 336,828
41. 173,702.760 173,702.760 ↑ 1.2 340,894 3,960 / 3

Parallel Seq Scan on leads a_leads_12 (cost=2.80..28,204.41 rows=425,720 width=24) (actual time=0.006..131.593 rows=340,894 loops=3,960)

  • Filter: ((client_id = ANY ('{1,72}'::integer[])) AND ((client_id = ANY ('{1,72}'::integer[])) OR (hashed SubPlan 3)))
  • Rows Removed by Filter: 966
42.          

SubPlan (for Parallel Seq Scan)

43. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on services (cost=0.00..2.79 rows=2 width=4) (never executed)

  • Filter: ((cat_id = 26) OR (client_id = 72))
44. 37.854 103.879 ↑ 1.0 218 2,641 / 3

Hash (cost=9.18..9.18 rows=218 width=12) (actual time=0.118..0.118 rows=218 loops=2,641)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
45. 66.025 66.025 ↑ 1.0 218 2,641 / 3

Seq Scan on stores a_stores_48 (cost=0.00..9.18 rows=218 width=12) (actual time=0.006..0.075 rows=218 loops=2,641)

46. 10.564 34.333 ↓ 1.1 39 2,641 / 3

Hash (cost=1.97..1.97 rows=35 width=13) (actual time=0.039..0.039 rows=39 loops=2,641)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
47. 23.769 23.769 ↓ 1.1 39 2,641 / 3

Seq Scan on lead_statuses a_lead_statuses_2 (cost=0.00..1.97 rows=35 width=13) (actual time=0.010..0.027 rows=39 loops=2,641)

  • Filter: (((name)::text <> 'DRAFT'::text) AND ((name)::text <> 'PENDING_PAYMENT'::text) AND ((name)::text <> ALL ('{CANCELED_BF_ACK,CONFIRMATION}'::text[])) AND ((name)::text <> ALL ('{CCCANCEL,CCABANDONED,UNKNOWN,STAGED}'::text[])))
  • Rows Removed by Filter: 4
48. 15,841.320 15,841.320 ↑ 1.0 1 15,841,320 / 3

Index Scan using lead_owner_idx on lead_owner a_lead_owner_24 (cost=0.43..0.48 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=15,841,320)

  • Index Cond: (lead_id = a_leads_12.id)
  • Filter: ((role)::text <> 'INSTALLER'::text)
49. 6.160 39.600 ↓ 4.8 24 3,960 / 3

Parallel Hash (cost=192.43..192.43 rows=5 width=4) (actual time=0.030..0.030 rows=24 loops=3,960)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
50. 7.475 33.440 ↓ 14.6 73 1,320 / 3

Hash Join (cost=1.90..192.43 rows=5 width=4) (actual time=0.037..0.076 rows=73 loops=1,320)

  • Hash Cond: (map_3.parent_group_id = access.parent_group_id)
51. 6.160 25.960 ↓ 2.0 73 1,320 / 3

Parallel Append (cost=0.28..190.65 rows=37 width=12) (actual time=0.035..0.059 rows=73 loops=1,320)

52. 10.560 16.720 ↓ 1.5 73 1,320 / 3

Parallel Bitmap Heap Scan on user_map_full_mm map_3 (cost=5.13..174.00 rows=48 width=12) (actual time=0.019..0.038 rows=73 loops=1,320)

  • Recheck Cond: ((parent_id = 5,338) AND (child_group_id = $3))
  • Heap Blocks: exact=5,280
53. 6.160 6.160 ↑ 1.1 75 1,320 / 3

Bitmap Index Scan on user_map_full_mm_parent_id_child_group_id_idx (cost=0.00..5.11 rows=82 width=0) (actual time=0.014..0.014 rows=75 loops=1,320)

  • Index Cond: ((parent_id = 5,338) AND (child_group_id = $3))
54. 1.760 1.760 ↓ 0.0 0 1,320 / 3

Parallel Index Scan using user_map_full_m1_parent_id_child_group_id_idx on user_map_full_m1 map_2 (cost=0.28..8.30 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1,320)

  • Index Cond: ((parent_id = 5,338) AND (child_group_id = $3))
55. 1.320 1.320 ↓ 0.0 0 1,320 / 3

Parallel Index Scan using user_map_full_11_parent_id_key on user_map_full_11 map_1 (cost=0.15..8.17 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1,320)

  • Index Cond: ((parent_id = 5,338) AND (child_group_id = $3))
56. 0.000 0.000 ↓ 0.0 0 1,320 / 3

Parallel Seq Scan on user_map_full map (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=1,320)

  • Filter: ((child_group_id = $3) AND (parent_id = 5,338))
57. 0.002 0.005 ↓ 6.0 6 1 / 3

Hash (cost=1.61..1.61 rows=1 width=8) (actual time=0.015..0.015 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
58. 0.003 0.003 ↓ 6.0 6 1 / 3

Seq Scan on user_map_object_access_cache access (cost=0.00..1.61 rows=1 width=8) (actual time=0.007..0.010 rows=6 loops=1)

  • Filter: ((child_group_id = $3) AND ((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: 21
59. 887.040 887.040 ↑ 1.0 1 1,330,560 / 3

Index Scan using lead_owner_idx on lead_owner a_lead_owner_13 (cost=0.43..0.48 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,330,560)

  • Index Cond: (lead_id = a_lead_owner_24.lead_id)
  • Filter: (main = 1)
60. 1,330.560 1,330.560 ↑ 1.0 1 1,330,560 / 3

Index Only Scan using customers_pkey on customers a_customers_14 (cost=0.42..0.46 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,330,560)

  • Index Cond: (id = a_leads_12.customer_id)
  • Heap Fetches: 34,320
61. 887.040 887.040 ↓ 0.0 0 1,330,560 / 3

Index Scan using complaints_lead_id_idx on complaints c (cost=0.29..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1,330,560)

  • Index Cond: (lead_id = a_lead_owner_13.lead_id)
  • Filter: ((reason_id IS NOT NULL) AND (created_on >= '2018-01-01 00:00:00'::timestamp without time zone) AND (created_on <= '2020-05-31 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 0
62. 7.920 7.920 ↑ 1.0 1 2,640 / 3

Index Scan using users_pkey on users u (cost=0.28..0.42 rows=1 width=7) (actual time=0.009..0.009 rows=1 loops=2,640)

  • Index Cond: (id = c.assigned_to)
Planning time : 5.443 ms
Execution time : 297,833.579 ms