explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GH3nh : Shrinithi

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

GroupAggregate (cost=995,470,838.99..995,475,621.51 rows=127,408 width=64) (actual rows= loops=)

  • Group Key: (to_char(cp.created_on, 'yyyy-mm'::text))
2. 0.000 0.000 ↓ 0.0

Sort (cost=995,470,838.99..995,471,157.98 rows=127,597 width=44) (actual rows= loops=)

  • Sort Key: (to_char(cp.created_on, 'yyyy-mm'::text))
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.47..995,460,017.98 rows=127,597 width=44) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.02..995,143,896.41 rows=127,597 width=16) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Index Scan using idx_created_on_customer_profiles on customer_profiles cp (cost=0.57..994,828,093.83 rows=127,597 width=12) (actual rows= loops=)

  • Index Cond: ((created_on >= '2019-01-01 00:00:00'::timestamp without time zone) AND (created_on < '2019-11-01 00:00:00'::timestamp without time zone))
  • Filter: (((customer_type)::text = 'Green'::text) AND (created_on <> (SubPlan 1)) AND (((SubPlan 2))::text = 'Red'::text))
6.          

SubPlan (for Index Scan)

7. 0.000 0.000 ↓ 0.0

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

8. 0.000 0.000 ↓ 0.0

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

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

Limit (cost=10.07..10.07 rows=1 width=13) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=10.07..10.07 rows=1 width=13) (actual rows= loops=)

  • Sort Key: customer_profiles_1.created_on DESC
11. 0.000 0.000 ↓ 0.0

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_1 (cost=0.57..10.06 rows=1 width=13) (actual rows= loops=)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (to_char(created_on, 'yyyy-mm'::text) = to_char((cp.created_on - '1 mon'::interval), 'yyyy-mm'::text))
12. 0.000 0.000 ↓ 0.0

Limit (cost=2.45..2.46 rows=1 width=12) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=2.45..2.46 rows=1 width=12) (actual rows= loops=)

  • Sort Key: cis_subscriptions.payment_date
14. 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=12) (actual rows= loops=)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (payment_date < cp.created_on)
15. 0.000 0.000 ↓ 0.0

Limit (cost=2.45..2.46 rows=1 width=12) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Sort (cost=2.45..2.46 rows=1 width=12) (actual rows= loops=)

  • Sort Key: vas_subscriptions.payment_date
17. 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=12) (actual rows= loops=)

  • Index Cond: (user_id = cp.user_id)
  • Filter: ((payment_date < cp.created_on) AND ((service_type)::text ~* 'chr'::text))