explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jC9s

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 1,759.951 ↓ 18.0 18 1

Sort (cost=89,557.63..89,557.63 rows=1 width=1,176) (actual time=1,759.950..1,759.951 rows=18 loops=1)

  • Sort Key: campaign_place_time_series.date DESC, campaign_place_time_series.start_at_hour NULLS FIRST
  • Sort Method: quicksort Memory: 29kB
2.          

CTE place_timezone_info

3. 0.006 0.039 ↑ 62.5 16 1

Result (cost=0.11..14.62 rows=1,000 width=58) (actual time=0.029..0.039 rows=16 loops=1)

4. 0.014 0.033 ↑ 62.5 16 1

ProjectSet (cost=0.11..5.62 rows=1,000 width=269) (actual time=0.028..0.033 rows=16 loops=1)

5. 0.019 0.019 ↑ 1.0 1 1

Index Scan using index_places_on_company_id_status_null on places (cost=0.11..4.12 rows=1 width=261) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: ((company_id = 3144) AND (id = 24106444))
  • Filter: ((name)::text <> 'NEW PLACE'::text)
6.          

CTE campaign_place_time_series

7. 0.457 1.643 ↓ 57.6 288 1

Nested Loop (cost=0.28..1,087.33 rows=5 width=754) (actual time=0.095..1.643 rows=288 loops=1)

8. 0.000 1.096 ↓ 18.0 18 1

Nested Loop (cost=0.28..1,080.77 rows=1 width=171) (actual time=0.058..1.096 rows=18 loops=1)

9. 0.054 0.379 ↑ 3.1 382 1

Nested Loop (cost=0.20..117.98 rows=1,186 width=8) (actual time=0.027..0.379 rows=382 loops=1)

10. 0.019 0.019 ↓ 1.5 18 1

Index Only Scan using index_place_group_places_on_place_id_and_place_group_id on place_group_places (cost=0.11..4.17 rows=12 width=8) (actual time=0.013..0.019 rows=18 loops=1)

  • Index Cond: (place_id = 24106444)
  • Heap Fetches: 0
11. 0.306 0.306 ↓ 4.2 21 18

Index Scan using index_campaign_place_groups_on_place_group_id on campaign_place_groups (cost=0.08..9.47 rows=5 width=8) (actual time=0.003..0.017 rows=21 loops=18)

  • Index Cond: (place_group_id = place_group_places.place_group_id)
12. 0.764 0.764 ↓ 0.0 0 382

Index Scan using index_campaigns_on_id_state_published on campaigns campaigns_1 (cost=0.08..0.81 rows=1 width=167) (actual time=0.002..0.002 rows=0 loops=382)

  • Index Cond: (id = campaign_place_groups.campaign_id)
  • Filter: ((metadata IS NOT NULL) AND exist(metadata, 'start_at'::text) AND exist(metadata, 'end_at'::text) AND (company_id = 3144))
  • Rows Removed by Filter: 0
13. 0.090 0.090 ↓ 3.2 16 18

CTE Scan on place_timezone_info (cost=0.00..6.50 rows=5 width=556) (actual time=0.002..0.005 rows=16 loops=18)

  • Filter: (place_id = 24106444)
14. 19.260 1,759.924 ↓ 18.0 18 1

Merge Left Join (cost=88,448.62..88,455.67 rows=1 width=1,176) (actual time=1,495.862..1,759.924 rows=18 loops=1)

  • Merge Cond: ((campaign_place_time_series.mission_version_id = company_mission_responses.campaign_version_id) AND (campaign_place_time_series.place_id = company_mission_responses.place_id))
  • Join Filter: ((timezone(campaign_place_time_series.place_timezone, timezone('UTC'::text, company_mission_responses.completed_at)))::date = campaign_place_time_series.date)
  • Rows Removed by Join Filter: 251
15. 0.020 2.374 ↓ 18.0 18 1

Sort (cost=0.04..0.04 rows=1 width=1,216) (actual time=2.370..2.374 rows=18 loops=1)

  • Sort Key: campaign_place_time_series.mission_version_id, campaign_place_time_series.place_id
  • Sort Method: quicksort Memory: 29kB
16. 2.354 2.354 ↓ 18.0 18 1

CTE Scan on campaign_place_time_series (cost=0.00..0.04 rows=1 width=1,216) (actual time=0.189..2.354 rows=18 loops=1)

  • Filter: (((mission_name)::text !~~* ALL ('{"%preventative maintenance%",%AOP%}'::text[])) AND (date >= '2019-09-09'::date) AND (date < '2019-09-10 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 270
17. 46.725 1,738.290 ↓ 67,467.0 202,401 1

Materialize (cost=88,448.58..88,455.61 rows=3 width=48) (actual time=1,483.775..1,738.290 rows=202,401 loops=1)

18. 20.793 1,691.565 ↓ 67,461.7 202,385 1

Subquery Scan on company_mission_responses (cost=88,448.58..88,455.61 rows=3 width=48) (actual time=1,483.768..1,691.565 rows=202,385 loops=1)

  • Filter: (company_mission_responses.response_rank = 1)
  • Rows Removed by Filter: 7022
19. 170.897 1,670.772 ↓ 342.7 209,407 1

WindowAgg (cost=88,448.58..88,453.47 rows=611 width=1,801) (actual time=1,483.767..1,670.772 rows=209,407 loops=1)

20. 190.810 1,499.875 ↓ 342.7 209,408 1

Sort (cost=88,448.58..88,448.89 rows=611 width=96) (actual time=1,483.754..1,499.875 rows=209,408 loops=1)

  • Sort Key: campaigns.version_identifier, mission_responses.place_id, ((timezone('America/New_York'::text, timezone('UTC'::text, mission_responses.completed_at)))::date), mission_responses.completed_at
  • Sort Method: quicksort Memory: 36850kB
21. 248.268 1,309.065 ↓ 357.4 218,375 1

Nested Loop Left Join (cost=0.31..88,442.93 rows=611 width=96) (actual time=0.074..1,309.065 rows=218,375 loops=1)

22. 51.460 624.047 ↓ 357.4 218,375 1

Nested Loop (cost=0.20..87,754.45 rows=611 width=20) (actual time=0.062..624.047 rows=218,375 loops=1)

23. 1.037 1.037 ↓ 1.1 805 1

Index Scan using index_campaigns_on_company_id on campaigns (cost=0.09..1,369.49 rows=739 width=8) (actual time=0.014..1.037 rows=805 loops=1)

  • Index Cond: (company_id = 3144)
24. 571.550 571.550 ↓ 8.0 271 805

Index Scan using index_mission_responses_on_campaign_id_state_and_completed_at on mission_responses (cost=0.11..116.79 rows=34 width=20) (actual time=0.151..0.710 rows=271 loops=805)

  • Index Cond: ((campaign_id = campaigns.id) AND (completed_at >= (now() - '16 days'::interval)))
25. 436.750 436.750 ↑ 1.0 1 218,375

Index Scan using index_share_tokens_on_shareable_id_and_shareable_type on share_tokens (cost=0.11..1.13 rows=1 width=80) (actual time=0.002..0.002 rows=1 loops=218,375)

  • Index Cond: ((shareable_id = mission_responses.id) AND ((shareable_type)::text = 'MissionResponse'::text))
Planning time : 16.657 ms
Execution time : 1,762.865 ms