explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vCHU

Settings
# exclusive inclusive rows x rows loops node
1. 0.315 170.787 ↑ 1.0 1 1

Nested Loop (cost=132,328.93..132,328.99 rows=1 width=40) (actual time=170.786..170.787 rows=1 loops=1)

2.          

CTE raw

3. 0.008 169.563 ↓ 2.8 11 1

Nested Loop Left Join (cost=0.42..31,672.30 rows=4 width=9) (actual time=161.051..169.563 rows=11 loops=1)

  • Filter: ((interaction.sc_794 = 'active'::status) OR (interaction.sc_794 IS NULL))
4. 169.520 169.520 ↑ 1.0 1 1

Seq Scan on client (cost=0.00..31,660.42 rows=1 width=4) (actual time=161.027..169.520 rows=1 loops=1)

  • Filter: (((NOT sc_1705) OR (sc_1705 IS NULL)) AND (COALESCE(sc_1691, '{}'::integer[]) <> '{915}'::integer[]) AND (sc_758 = 38411) AND (sc_757 = 'active'::status))
  • Rows Removed by Filter: 185112
5. 0.035 0.035 ↓ 1.8 11 1

Index Scan using idx_search_client_sc_807 on interaction (cost=0.42..11.80 rows=6 width=12) (actual time=0.018..0.035 rows=11 loops=1)

  • Index Cond: (client.id = sc_807)
  • Filter: (sc_794 = 'active'::status)
6.          

CTE data

7. 0.330 170.440 ↑ 1.0 1 1

Aggregate (cost=100,656.51..100,656.53 rows=1 width=32) (actual time=170.440..170.440 rows=1 loops=1)

8. 0.043 170.110 ↓ 2.8 11 1

Subquery Scan on foo (cost=0.84..100,656.50 rows=4 width=159) (actual time=161.223..170.110 rows=11 loops=1)

9. 0.000 170.067 ↓ 2.8 11 1

Limit (cost=0.84..100,656.46 rows=4 width=296) (actual time=161.189..170.067 rows=11 loops=1)

10. 0.103 170.067 ↓ 2.8 11 1

Nested Loop Left Join (cost=0.84..100,656.46 rows=4 width=296) (actual time=161.189..170.067 rows=11 loops=1)

  • Join Filter: (raw.interaction_id = interaction_1.id)
  • Rows Removed by Join Filter: 110
11. 0.017 169.612 ↓ 2.8 11 1

Nested Loop (cost=0.42..21.87 rows=4 width=83) (actual time=161.063..169.612 rows=11 loops=1)

12. 169.573 169.573 ↓ 2.8 11 1

CTE Scan on raw (cost=0.00..0.08 rows=4 width=8) (actual time=161.054..169.573 rows=11 loops=1)

13. 0.022 0.022 ↑ 1.0 1 11

Index Scan using client_id_unique on client client_1 (cost=0.42..5.44 rows=1 width=79) (actual time=0.002..0.002 rows=1 loops=11)

  • Index Cond: (id = raw.client_id)
14. 0.077 0.077 ↓ 1.8 11 11

Index Scan using idx_search_client_sc_807 on interaction interaction_1 (cost=0.42..0.71 rows=6 width=73) (actual time=0.002..0.007 rows=11 loops=11)

  • Index Cond: (client_1.id = sc_807)
15.          

SubPlan (forNested Loop Left Join)

16. 0.099 0.099 ↑ 1.0 1 11

CTE Scan on referrals_json_agg (cost=25,136.57..25,136.59 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=11)

17.          

CTE referrals

18. 0.033 0.033 ↓ 0.0 0 11

Function Scan on jsonb_to_recordset x (cost=0.01..5.50 rows=100 width=250) (actual time=0.003..0.003 rows=0 loops=11)

19.          

CTE referrals_expanded

20. 0.044 0.044 ↓ 0.0 0 11

CTE Scan on referrals (cost=0.00..25,126.55 rows=100 width=442) (actual time=0.004..0.004 rows=0 loops=11)

21.          

SubPlan (forCTE Scan)

22. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=46.91..46.92 rows=1 width=32) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_main_agency_system_agency_id on agency_system (cost=0.29..46.86 rows=10 width=35) (never executed)

  • Index Cond: (agency_id = ANY ((referrals.agency_ids)::integer[]))
24. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=36.55..36.56 rows=1 width=32) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_main_program_system_program_id on program_system (cost=0.29..36.50 rows=10 width=30) (never executed)

  • Index Cond: (program_id = ANY ((referrals.program_ids)::integer[]))
26. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=31.59..31.60 rows=1 width=32) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_main_site_system_site_id on site_system (cost=0.29..31.54 rows=10 width=49) (never executed)

  • Index Cond: (site_id = ANY ((referrals.site_ids)::integer[]))
28. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=30.72..30.73 rows=1 width=32) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_main_service_system_service_id on service_system (cost=0.29..30.66 rows=10 width=28) (never executed)

  • Index Cond: (service_id = ANY ((referrals.service_ids)::integer[]))
30. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=48.12..48.13 rows=1 width=32) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using taxonomy_pkey on taxonomy (cost=0.29..48.07 rows=10 width=42) (never executed)

  • Index Cond: (id = ANY ((referrals.taxonomy_ids)::integer[]))
32. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=57.28..57.29 rows=1 width=32) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..57.24 rows=9 width=533) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Seq Scan on taxonomy_link tl (cost=0.00..11.93 rows=9 width=524) (never executed)

  • Filter: (id = ANY ((referrals.taxonomy_link_ids)::integer[]))
35. 0.000 0.000 ↓ 0.0 0

Index Scan using taxonomy_pkey on taxonomy t (cost=0.29..5.02 rows=1 width=17) (never executed)

  • Index Cond: (id = tl.taxonomy_id)
36.          

CTE referrals_json

37. 0.044 0.044 ↓ 0.0 0 11

CTE Scan on referrals_expanded (cost=0.00..2.25 rows=100 width=32) (actual time=0.004..0.004 rows=0 loops=11)

38.          

CTE referrals_json_agg

39. 0.011 0.066 ↑ 1.0 1 11

Aggregate (cost=2.25..2.26 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=11)

40. 0.055 0.055 ↓ 0.0 0 11

CTE Scan on referrals_json (cost=0.00..2.00 rows=100 width=32) (actual time=0.005..0.005 rows=0 loops=11)

41. 0.011 0.066 ↑ 1.0 1 11

Limit (cost=0.28..5.32 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=11)

42. 0.055 0.055 ↑ 1.0 1 11

Index Scan using idx_main_account_system_account_id on account_system (cost=0.28..5.32 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=11)

  • Index Cond: (account_id = interaction_1.sc_806)
43. 0.000 0.033 ↑ 1.0 1 11

Limit (cost=0.28..5.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=11)

44. 0.033 0.033 ↑ 1.0 1 11

Index Scan using idx_main_account_system_account_id on account_system account_system_1 (cost=0.28..5.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: (account_id = interaction_1.sc_801)
45. 0.011 0.044 ↑ 1.0 1 11

Limit (cost=0.28..5.32 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=11)

46. 0.033 0.033 ↑ 1.0 1 11

Index Scan using idx_main_account_system_account_id on account_system account_system_2 (cost=0.28..5.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: (account_id = client_1.sc_759)
47. 0.000 0.033 ↑ 1.0 1 11

Limit (cost=0.28..5.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=11)

48. 0.033 0.033 ↑ 1.0 1 11

Index Scan using idx_main_account_system_account_id on account_system account_system_3 (cost=0.28..5.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: (account_id = client_1.sc_760)
49.          

CTE rc

50. 0.002 0.014 ↑ 1.0 1 1

Aggregate (cost=0.09..0.10 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

51. 0.012 0.012 ↓ 2.8 11 1

CTE Scan on raw raw_1 (cost=0.00..0.08 rows=4 width=24) (actual time=0.008..0.012 rows=11 loops=1)

52. 170.455 170.455 ↑ 1.0 1 1

CTE Scan on data (cost=0.00..0.02 rows=1 width=32) (actual time=170.455..170.455 rows=1 loops=1)

53. 0.017 0.017 ↑ 1.0 1 1

CTE Scan on rc (cost=0.00..0.02 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1)

Planning time : 4.773 ms
Execution time : 171.557 ms