explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j35j : Shivangi

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=827,639,650.85..827,639,650.86 rows=1 width=56) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16.00..827,395,864.78 rows=49,854 width=12) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=11.11..827,150,583.10 rows=49,854 width=4) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Index Scan using idx_created_on_customer_profiles on customer_profiles cp (cost=0.57..825,956,118.61 rows=96,135 width=4) (actual rows= loops=)

  • Index Cond: (created_on >= '2018-01-01 00:00:00'::timestamp without time zone)
  • Filter: (((customer_type)::text = 'Red'::text) AND (customer_profile_id = (SubPlan 6)))
5.          

SubPlan (for Index Scan)

6. 0.000 0.000 ↓ 0.0

Aggregate (cost=9.97..9.98 rows=1 width=8) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_2 (cost=0.57..9.94 rows=12 width=8) (actual rows= loops=)

  • Index Cond: (user_id = cp.user_id)
8. 0.000 0.000 ↓ 0.0

Index Scan using customer_profiles_pkey on customer_profiles (cost=10.55..12.42 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (customer_profile_id = (SubPlan 5))
  • Filter: ((customer_type)::text = 'Green'::text)
9.          

SubPlan (for Index Scan)

10. 0.000 0.000 ↓ 0.0

Aggregate (cost=9.97..9.98 rows=1 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_1 (cost=0.57..9.94 rows=12 width=8) (actual rows= loops=)

  • Index Cond: (user_id = cp.user_id)
12. 0.000 0.000 ↓ 0.0

Aggregate (cost=4.89..4.90 rows=1 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..4.89 rows=1 width=4) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using cis_subscriptions__index_on_user_id on cis_subscriptions cis (cost=0.42..2.44 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (payment_date <= '2019-01-01 00:00:00'::timestamp without time zone)
15. 0.000 0.000 ↓ 0.0

Index Scan using vas_subscriptions__index_on_user_id on vas_subscriptions vas (cost=0.42..2.44 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (payment_date <= '2019-01-01 00:00:00'::timestamp without time zone)
16.          

SubPlan (for Aggregate)

17. 0.000 0.000 ↓ 0.0

Index Scan using cis_subscriptions__index_on_user_id on cis_subscriptions (cost=0.42..2.44 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (payment_date <= '2019-01-01 00:00:00'::timestamp without time zone)
18. 0.000 0.000 ↓ 0.0

Index Scan using cis_subscriptions__index_on_payment_date on cis_subscriptions cis_subscriptions_1 (cost=0.42..25,683.57 rows=130,551 width=4) (actual rows= loops=)

  • Index Cond: (payment_date <= '2019-01-01 00:00:00'::timestamp without time zone)
19. 0.000 0.000 ↓ 0.0

Index Scan using vas_subscriptions__index_on_user_id on vas_subscriptions (cost=0.42..2.44 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (((service_type)::text ~* '(CHR|CHRA)'::text) AND (payment_date <= '2019-01-01 00:00:00'::timestamp without time zone))
20. 0.000 0.000 ↓ 0.0

Index Scan using vas_subscriptions__index_on_payment_date on vas_subscriptions vas_subscriptions_1 (cost=0.42..13,920.47 rows=51,455 width=4) (actual rows= loops=)

  • Index Cond: (payment_date <= '2019-01-01 00:00:00'::timestamp without time zone)
  • Filter: ((service_type)::text ~* '(CHR|CHRA)'::text)