explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WYYl

Settings
# exclusive inclusive rows x rows loops node
1. 0.246 13,014.313 ↓ 288.0 288 1

Sort (cost=911,692.98..911,692.98 rows=1 width=1,176) (actual time=13,014.300..13,014.313 rows=288 loops=1)

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

CTE place_timezone_info

3. 0.011 0.094 ↑ 62.5 16 1

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

4. 0.024 0.083 ↑ 62.5 16 1

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

5. 0.059 0.059 ↑ 1.0 1 1

Index Scan using index_places_on_company_id_status_null on places (cost=0.11..4.12 rows=1 width=255) (actual time=0.058..0.059 rows=1 loops=1)

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

CTE campaign_place_time_series

7. 0.879 5.153 ↓ 57.6 288 1

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

8. 0.103 4.094 ↓ 18.0 18 1

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

9. 0.102 2.076 ↑ 3.1 383 1

Nested Loop (cost=0.20..117.98 rows=1,186 width=8) (actual time=0.108..2.076 rows=383 loops=1)

10. 0.086 0.086 ↓ 1.3 16 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.069..0.086 rows=16 loops=1)

  • Index Cond: (place_id = 24106205)
  • Heap Fetches: 0
11. 1.888 1.888 ↓ 4.8 24 16

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.013..0.118 rows=24 loops=16)

  • Index Cond: (place_group_id = place_group_places.place_group_id)
12. 1.915 1.915 ↓ 0.0 0 383

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.005..0.005 rows=0 loops=383)

  • 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 = 24106205)
14. 250.788 13,014.067 ↓ 288.0 288 1

Merge Left Join (cost=910,224.09..910,591.02 rows=1 width=1,176) (actual time=9,426.304..13,014.067 rows=288 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: 5328
15. 0.244 6.784 ↓ 288.0 288 1

Sort (cost=0.04..0.04 rows=1 width=1,216) (actual time=6.756..6.784 rows=288 loops=1)

  • Sort Key: campaign_place_time_series.mission_version_id, campaign_place_time_series.place_id
  • Sort Method: quicksort Memory: 65kB
16. 6.540 6.540 ↓ 288.0 288 1

CTE Scan on campaign_place_time_series (cost=0.00..0.04 rows=1 width=1,216) (actual time=0.322..6.540 rows=288 loops=1)

  • Filter: (((mission_name)::text !~~* ALL ('{"%preventative maintenance%",%AOP%}'::text[])) AND (date >= '2019-01-01'::date) AND (date < '2019-09-16 00:00:00'::timestamp without time zone))
17. 627.864 12,756.495 ↓ 16,655.7 2,648,250 1

Materialize (cost=910,224.05..910,590.80 rows=159 width=48) (actual time=9,338.192..12,756.495 rows=2,648,250 loops=1)

18. 239.229 12,128.631 ↓ 16,624.2 2,643,254 1

Subquery Scan on company_mission_responses (cost=910,224.05..910,590.72 rows=159 width=48) (actual time=9,338.189..12,128.631 rows=2,643,254 loops=1)

  • Filter: (company_mission_responses.response_rank = 1)
  • Rows Removed by Filter: 165338
19. 2,199.237 11,889.402 ↓ 88.1 2,808,592 1

WindowAgg (cost=910,224.05..910,479.13 rows=31,885 width=1,801) (actual time=9,338.188..11,889.402 rows=2,808,592 loops=1)

20. 3,946.214 9,690.165 ↓ 88.1 2,808,593 1

Sort (cost=910,224.05..910,239.99 rows=31,885 width=96) (actual time=9,338.174..9,690.165 rows=2,808,593 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: 309032kB
21. 235.432 5,743.951 ↓ 90.2 2,875,511 1

Gather (cost=1,000.23..909,747.03 rows=31,885 width=96) (actual time=0.801..5,743.951 rows=2,875,511 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
22. 2,388.636 5,508.519 ↓ 72.1 575,102 5

Nested Loop Left Join (cost=0.23..905,558.53 rows=7,971 width=96) (actual time=2.672..5,508.519 rows=575,102 loops=5)

23. 2,779.429 3,119.880 ↓ 72.1 575,102 5

Nested Loop (cost=0.11..904,456.13 rows=7,971 width=20) (actual time=2.212..3,119.880 rows=575,102 loops=5)

24. 323.921 323.921 ↑ 1.1 161 5

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

  • Filter: (company_id = 3144)
  • Rows Removed by Filter: 234944
25. 16.530 16.530 ↓ 2.0 3,577 804

Index Scan using index_mission_responses_on_campaign_id_updated_at on mission_responses (cost=0.11..3,041.89 rows=1,802 width=20) (actual time=0.016..16.530 rows=3,577 loops=804)

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

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,875,511)

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