explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u7LQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.244 13,447.958 ↓ 51.2 256 1

Sort (cost=828,460.14..828,460.14 rows=5 width=1,176) (actual time=13,447.946..13,447.958 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.006 0.057 ↑ 62.5 16 1

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

4. 0.017 0.051 ↑ 62.5 16 1

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

5. 0.034 0.034 ↑ 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.034..0.034 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.390 7.253 ↓ 51.2 256 1

Nested Loop (cost=288.11..382.04 rows=5 width=754) (actual time=5.631..7.253 rows=256 loops=1)

8. 0.046 6.767 ↓ 16.0 16 1

Nested Loop (cost=288.11..375.48 rows=1 width=170) (actual time=5.575..6.767 rows=16 loops=1)

9. 0.062 6.577 ↓ 36.0 72 1

Nested Loop (cost=288.00..371.66 rows=2 width=170) (actual time=5.562..6.577 rows=72 loops=1)

10. 0.601 6.095 ↓ 23.3 70 1

Bitmap Heap Scan on campaigns campaigns_1 (cost=287.91..347.41 rows=3 width=166) (actual time=5.539..6.095 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.052 5.494 ↓ 0.0 0 1

BitmapAnd (cost=287.91..287.91 rows=30 width=0) (actual time=5.494..5.494 rows=0 loops=1)

12. 0.122 0.122 ↑ 1.0 787 1

Bitmap Index Scan on index_campaigns_on_company_id (cost=0.00..7.31 rows=814 width=0) (actual time=0.122..0.122 rows=787 loops=1)

  • Index Cond: (company_id = 3144)
13. 5.320 5.320 ↓ 1.0 42,809 1

Bitmap Index Scan on index_campaigns_on_id_state_published (cost=0.00..280.56 rows=42,498 width=0) (actual time=5.320..5.320 rows=42,809 loops=1)

14. 0.420 0.420 ↑ 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.006..0.006 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. 242.983 13,447.714 ↓ 51.2 256 1

Merge Left Join (cost=827,659.54..828,063.48 rows=5 width=1,176) (actual time=9,954.672..13,447.714 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.135 7.574 ↓ 51.2 256 1

Sort (cost=0.04..0.04 rows=5 width=1,216) (actual time=7.543..7.574 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.439 7.439 ↓ 51.2 256 1

CTE Scan on campaign_place_time_series (cost=0.00..0.03 rows=5 width=1,216) (actual time=5.634..7.439 rows=256 loops=1)

20. 582.689 13,197.157 ↓ 14,459.1 2,530,346 1

Materialize (cost=827,659.50..828,063.18 rows=175 width=48) (actual time=9,866.438..13,197.157 rows=2,530,346 loops=1)

21. 230.408 12,614.468 ↓ 14,430.7 2,525,366 1

Subquery Scan on company_mission_responses (cost=827,659.50..828,063.10 rows=175 width=48) (actual time=9,866.435..12,614.468 rows=2,525,366 loops=1)

  • Filter: (company_mission_responses.response_rank = 1)
  • Rows Removed by Filter: 161374
22. 2,064.643 12,384.060 ↓ 76.6 2,686,740 1

WindowAgg (cost=827,659.50..827,940.26 rows=35,095 width=1,801) (actual time=9,866.434..12,384.060 rows=2,686,740 loops=1)

23. 3,591.797 10,319.417 ↓ 76.6 2,686,741 1

Sort (cost=827,659.50..827,677.05 rows=35,095 width=96) (actual time=9,866.417..10,319.417 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: 295192kB
24. 349.770 6,727.620 ↓ 78.3 2,746,668 1

Gather (cost=1,000.23..827,129.60 rows=35,095 width=96) (actual time=0.748..6,727.620 rows=2,746,668 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
25. 2,976.803 6,377.850 ↓ 60.7 686,667 4

Nested Loop Left Join (cost=0.23..822,620.10 rows=11,321 width=96) (actual time=1.532..6,377.850 rows=686,667 loops=4)

26. 3,247.668 3,401.044 ↓ 60.7 686,667 4

Nested Loop (cost=0.11..821,069.86 rows=11,321 width=20) (actual time=1.231..3,401.044 rows=686,667 loops=4)

27. 137.533 137.533 ↑ 1.3 197 4

Parallel Seq Scan on campaigns (cost=0.00..64,679.17 rows=263 width=8) (actual time=1.131..137.533 rows=197 loops=4)

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

Index Scan using index_mission_responses_on_campaign_id_updated_at on mission_responses (cost=0.11..2,870.73 rows=1,761 width=20) (actual time=0.015..15.843 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.086 ms
Execution time : 13,496.317 ms