explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DNOX

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 13,828.089 ↓ 18.0 18 1

Sort (cost=905,150.94..905,150.94 rows=1 width=1,176) (actual time=13,828.088..13,828.089 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.010 0.093 ↑ 62.5 16 1

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

4. 0.025 0.083 ↑ 62.5 16 1

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

5. 0.058 0.058 ↑ 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.057..0.058 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.795 4.295 ↓ 57.6 288 1

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

8. 0.358 3.320 ↓ 18.0 18 1

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

9. 0.104 1.816 ↑ 3.1 382 1

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

10. 0.092 0.092 ↓ 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.067..0.092 rows=18 loops=1)

  • Index Cond: (place_id = 24106444)
  • Heap Fetches: 0
11. 1.620 1.620 ↓ 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.010..0.090 rows=21 loops=18)

  • Index Cond: (place_group_id = place_group_places.place_group_id)
12. 1.146 1.146 ↓ 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.003..0.003 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.180 0.180 ↓ 3.2 16 18

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

  • Filter: (place_id = 24106444)
14. 260.848 13,828.055 ↓ 18.0 18 1

Merge Left Join (cost=903,681.51..904,048.98 rows=1 width=1,176) (actual time=10,257.706..13,828.055 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: 2843
15. 0.032 5.587 ↓ 18.0 18 1

Sort (cost=0.04..0.04 rows=1 width=1,216) (actual time=5.582..5.587 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. 5.555 5.555 ↓ 18.0 18 1

CTE Scan on campaign_place_time_series (cost=0.00..0.04 rows=1 width=1,216) (actual time=0.438..5.555 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. 609.516 13,561.620 ↓ 16,639.1 2,662,258 1

Materialize (cost=903,681.47..904,048.76 rows=160 width=48) (actual time=10,114.785..13,561.620 rows=2,662,258 loops=1)

18. 246.027 12,952.104 ↓ 16,638.5 2,662,162 1

Subquery Scan on company_mission_responses (cost=903,681.47..904,048.68 rows=160 width=48) (actual time=10,114.772..12,952.104 rows=2,662,162 loops=1)

  • Filter: (company_mission_responses.response_rank = 1)
  • Rows Removed by Filter: 165990
19. 2,237.531 12,706.077 ↓ 88.6 2,828,152 1

WindowAgg (cost=903,681.47..903,936.92 rows=31,932 width=1,801) (actual time=10,114.771..12,706.077 rows=2,828,152 loops=1)

20. 3,995.262 10,468.546 ↓ 88.6 2,828,153 1

Sort (cost=903,681.47..903,697.43 rows=31,932 width=96) (actual time=10,114.757..10,468.546 rows=2,828,153 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: external merge Disk: 309312kB
21. 548.136 6,473.284 ↓ 90.1 2,877,655 1

Gather (cost=1,000.23..903,203.68 rows=31,932 width=96) (actual time=0.762..6,473.284 rows=2,877,655 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
22. 2,437.837 5,925.148 ↓ 72.1 575,531 5

Nested Loop Left Join (cost=0.23..899,010.48 rows=7,983 width=96) (actual time=1.088..5,925.148 rows=575,531 loops=5)

23. 3,146.430 3,487.308 ↓ 72.1 575,531 5

Nested Loop (cost=0.11..897,906.64 rows=7,983 width=20) (actual time=0.792..3,487.308 rows=575,531 loops=5)

24. 322.076 322.076 ↑ 1.1 161 5

Parallel Seq Scan on campaigns (cost=0.00..340,705.83 rows=185 width=8) (actual time=0.592..322.076 rows=161 loops=5)

  • Filter: (company_id = 3144)
  • Rows Removed by Filter: 234961
25. 18.802 18.802 ↓ 2.0 3,575 805

Index Scan using index_mission_responses_on_campaign_id_updated_at on mission_responses (cost=0.11..3,006.57 rows=1,776 width=20) (actual time=0.017..18.802 rows=3,575 loops=805)

  • Index Cond: (campaign_id = campaigns.id)
26. 0.003 0.003 ↑ 1.0 1 2,877,655

Index Scan using index_share_tokens_on_shareable_id_and_shareable_type on share_tokens (cost=0.11..0.14 rows=1 width=80) (actual time=0.003..0.003 rows=1 loops=2,877,655)

  • Index Cond: ((shareable_id = mission_responses.id) AND ((shareable_type)::text = 'MissionResponse'::text))
Planning time : 33.349 ms
Execution time : 13,893.543 ms