explain.depesz.com

PostgreSQL's explain analyze made readable

Result: faOh

Settings
# exclusive inclusive rows x rows loops node
1. 0.246 13,932.948 ↓ 256.0 256 1

Sort (cost=797,485.32..797,485.32 rows=1 width=1,176) (actual time=13,932.936..13,932.948 rows=256 loops=1)

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

CTE place_timezone_info

3. 0.005 0.052 ↑ 62.5 16 1

Result (cost=0.09..14.60 rows=1,000 width=58) (actual time=0.043..0.052 rows=16 loops=1)

4. 0.017 0.047 ↑ 62.5 16 1

ProjectSet (cost=0.09..5.60 rows=1,000 width=266) (actual time=0.041..0.047 rows=16 loops=1)

5. 0.030 0.030 ↑ 1.0 1 1

Index Scan using index_places_on_company_id_status_null on places (cost=0.09..4.09 rows=1 width=258) (actual time=0.030..0.030 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.392 6.636 ↓ 51.2 256 1

Nested Loop (cost=292.07..386.01 rows=5 width=754) (actual time=5.134..6.636 rows=256 loops=1)

8. 0.044 6.148 ↓ 16.0 16 1

Nested Loop (cost=292.07..379.44 rows=1 width=170) (actual time=5.084..6.148 rows=16 loops=1)

9. 0.068 5.960 ↓ 36.0 72 1

Nested Loop (cost=291.96..375.62 rows=2 width=170) (actual time=5.072..5.960 rows=72 loops=1)

10. 0.539 5.542 ↓ 23.3 70 1

Bitmap Heap Scan on campaigns campaigns_1 (cost=291.87..351.37 rows=3 width=166) (actual time=5.048..5.542 rows=70 loops=1)

  • Recheck Cond: ((company_id = 3144) AND ((state)::text = 'published'::text))
  • Filter: ((metadata IS NOT NULL) AND exist(metadata, 'start_at'::text) AND exist(metadata, 'end_at'::text))
  • Rows Removed by Filter: 52
  • Heap Blocks: exact=110
11. 0.051 5.003 ↓ 0.0 0 1

BitmapAnd (cost=291.87..291.87 rows=30 width=0) (actual time=5.003..5.003 rows=0 loops=1)

12. 0.115 0.115 ↓ 1.0 787 1

Bitmap Index Scan on index_campaigns_on_company_id (cost=0.00..7.25 rows=774 width=0) (actual time=0.115..0.115 rows=787 loops=1)

  • Index Cond: (company_id = 3144)
13. 4.837 4.837 ↑ 1.0 42,811 1

Bitmap Index Scan on index_campaigns_on_id_state_published (cost=0.00..284.58 rows=44,520 width=0) (actual time=4.837..4.837 rows=42,811 loops=1)

14. 0.350 0.350 ↑ 4.0 1 70

Index Only Scan using index_campaign_place_groups_on_campaign_id_place_group_id on campaign_place_groups (cost=0.08..8.07 rows=4 width=8) (actual time=0.005..0.005 rows=1 loops=70)

  • Index Cond: (campaign_id = campaigns_1.id)
  • Heap Fetches: 72
15. 0.144 0.144 ↓ 0.0 0 72

Index Only Scan using index_place_group_places_on_place_id_and_place_group_id on place_group_places (cost=0.11..1.85 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=72)

  • Index Cond: ((place_id = 24106205) AND (place_group_id = campaign_place_groups.place_group_id))
  • Heap Fetches: 16
16. 0.096 0.096 ↓ 3.2 16 16

CTE Scan on place_timezone_info (cost=0.00..6.50 rows=5 width=556) (actual time=0.003..0.006 rows=16 loops=16)

  • Filter: (place_id = 24106205)
17. 244.524 13,932.702 ↓ 256.0 256 1

Merge Left Join (cost=796,700.41..797,084.72 rows=1 width=1,176) (actual time=10,423.842..13,932.702 rows=256 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: 5312
18. 0.136 7.437 ↓ 256.0 256 1

Sort (cost=0.04..0.04 rows=1 width=1,216) (actual time=7.406..7.437 rows=256 loops=1)

  • Sort Key: campaign_place_time_series.mission_version_id, campaign_place_time_series.place_id
  • Sort Method: quicksort Memory: 61kB
19. 7.301 7.301 ↓ 256.0 256 1

CTE Scan on campaign_place_time_series (cost=0.00..0.04 rows=1 width=1,216) (actual time=5.149..7.301 rows=256 loops=1)

  • Filter: (((mission_name)::text !~~* ALL ('{"%preventative maintenance%",%AOP%}'::text[])) AND (date >= '2019-08-01'::date) AND (date < '2019-12-02 00:00:00'::timestamp without time zone))
20. 587.598 13,680.741 ↓ 15,151.8 2,530,346 1

Materialize (cost=796,700.37..797,084.49 rows=167 width=48) (actual time=10,335.864..13,680.741 rows=2,530,346 loops=1)

21. 219.538 13,093.143 ↓ 15,122.0 2,525,366 1

Subquery Scan on company_mission_responses (cost=796,700.37..797,084.40 rows=167 width=48) (actual time=10,335.861..13,093.143 rows=2,525,366 loops=1)

  • Filter: (company_mission_responses.response_rank = 1)
  • Rows Removed by Filter: 161374
22. 2,057.662 12,873.605 ↓ 80.5 2,686,740 1

WindowAgg (cost=796,700.37..796,967.53 rows=33,394 width=1,801) (actual time=10,335.859..12,873.605 rows=2,686,740 loops=1)

23. 3,693.884 10,815.943 ↓ 80.5 2,686,741 1

Sort (cost=796,700.37..796,717.07 rows=33,394 width=96) (actual time=10,335.844..10,815.943 rows=2,686,741 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: 295208kB
24. 424.983 7,122.059 ↓ 82.3 2,746,668 1

Gather (cost=1,000.23..796,198.55 rows=33,394 width=96) (actual time=0.683..7,122.059 rows=2,746,668 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
25. 2,932.720 6,697.076 ↓ 63.7 686,667 4

Nested Loop Left Join (cost=0.23..791,859.15 rows=10,772 width=96) (actual time=1.219..6,697.076 rows=686,667 loops=4)

26. 3,610.229 3,764.353 ↓ 63.7 686,667 4

Nested Loop (cost=0.11..790,384.09 rows=10,772 width=20) (actual time=0.862..3,764.353 rows=686,667 loops=4)

27. 136.445 136.445 ↑ 1.3 197 4

Parallel Seq Scan on campaigns (cost=0.00..64,678.24 rows=250 width=8) (actual time=0.774..136.445 rows=197 loops=4)

  • Filter: (company_id = 3144)
  • Rows Removed by Filter: 290360
28. 17.679 17.679 ↓ 2.0 3,490 787

Index Scan using index_mission_responses_on_campaign_id_updated_at on mission_responses (cost=0.11..2,897.54 rows=1,761 width=20) (actual time=0.015..17.679 rows=3,490 loops=787)

  • Index Cond: (campaign_id = campaigns.id)
29. 0.003 0.003 ↑ 1.0 1 2,746,668

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,746,668)

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