explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vbwi

Settings
# exclusive inclusive rows x rows loops node
1. 9.906 784.633 ↑ 15.2 1,433 1

Sort (cost=1,002,238.01..1,002,292.32 rows=21,725 width=184) (actual time=784.541..784.633 rows=1,433 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day, data_stats_event.day)) DESC, (COALESCE(data_stats_session.group1, data_stats_page_view.group1, data_stats_event.group1)), (COALESCE(data_stats_session.group2, data_stats_page_view.group2, data_stats_event.group2))
  • Sort Method: quicksort Memory: 250kB
2.          

CTE data

3. 39.566 106.742 ↑ 27.2 39,600 1

Merge Join (cost=47,877.78..116,911.06 rows=1,076,253 width=137) (actual time=53.190..106.742 rows=39,600 loops=1)

  • Merge Cond: (stats_page_view.id = stats_event.page_view_id)
  • Join Filter: (((stats_session.campaign_id)::numeric >= '1'::numeric) OR (((stats_event."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
4. 2.288 7.307 ↑ 2.3 3,960 1

Sort (cost=5,149.94..5,172.28 rows=8,933 width=81) (actual time=6.636..7.307 rows=3,960 loops=1)

  • Sort Key: stats_page_view.id
  • Sort Method: quicksort Memory: 653kB
5. 1.572 5.019 ↑ 2.3 3,960 1

Hash Join (cost=379.75..4,563.72 rows=8,933 width=81) (actual time=0.590..5.019 rows=3,960 loops=1)

  • Hash Cond: (stats_page_view.session_id = stats_session.id)
6. 0.387 2.889 ↑ 1.1 3,995 1

Append (cost=0.00..3,862.12 rows=4,326 width=44) (actual time=0.020..2.889 rows=3,995 loops=1)

7. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((created_at >= '2019-04-05 09:37:00+00'::timestamp with time zone) AND (created_at <= '2019-04-10 09:37:59+00'::timestamp with time zone) AND (property_id = 1))
8. 2.496 2.496 ↑ 1.1 3,995 1

Index Scan using _hyper_3_3_chunk_idx_pageview_property_id_and_created_at on _hyper_3_3_chunk (cost=0.42..3,840.49 rows=4,325 width=44) (actual time=0.013..2.496 rows=3,995 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-04-05 09:37:00+00'::timestamp with time zone) AND (created_at <= '2019-04-10 09:37:59+00'::timestamp with time zone))
9. 0.089 0.558 ↑ 1.0 396 1

Hash (cost=374.59..374.59 rows=413 width=53) (actual time=0.558..0.558 rows=396 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
10. 0.038 0.469 ↑ 1.0 396 1

Append (cost=0.00..374.59 rows=413 width=53) (actual time=0.021..0.469 rows=396 loops=1)

11. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=262) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((created_at >= '2019-04-05 09:37:00+00'::timestamp with time zone) AND (created_at <= '2019-04-10 09:37:59+00'::timestamp with time zone) AND (property_id = 1))
12. 0.429 0.429 ↑ 1.0 396 1

Index Scan using _hyper_2_2_chunk_idx_session_property_id_and_created_at on _hyper_2_2_chunk (cost=0.29..372.52 rows=412 width=52) (actual time=0.018..0.429 rows=396 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-04-05 09:37:00+00'::timestamp with time zone) AND (created_at <= '2019-04-10 09:37:59+00'::timestamp with time zone))
13. 5.507 59.869 ↑ 1.1 40,012 1

Materialize (cost=42,727.84..42,944.70 rows=43,373 width=76) (actual time=46.538..59.869 rows=40,012 loops=1)

14. 31.098 54.362 ↑ 1.1 40,012 1

Sort (cost=42,727.84..42,836.27 rows=43,373 width=76) (actual time=46.534..54.362 rows=40,012 loops=1)

  • Sort Key: stats_event.page_view_id
  • Sort Method: external merge Disk: 2272kB
15. 3.866 23.264 ↑ 1.1 40,012 1

Append (cost=0.00..38,285.14 rows=43,373 width=76) (actual time=0.024..23.264 rows=40,012 loops=1)

16. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on stats_event (cost=0.00..0.00 rows=1 width=76) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: ((created_at >= '2019-04-05 09:37:00+00'::timestamp with time zone) AND (created_at <= '2019-04-10 09:37:59+00'::timestamp with time zone) AND (property_id = 1))
17. 19.389 19.389 ↑ 1.1 40,012 1

Index Scan using _hyper_5_4_chunk_idx_event_property_id_and_created_at on _hyper_5_4_chunk (cost=0.43..38,068.27 rows=43,372 width=76) (actual time=0.015..19.389 rows=40,012 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-04-05 09:37:00+00'::timestamp with time zone) AND (created_at <= '2019-04-10 09:37:59+00'::timestamp with time zone))
18. 3.987 774.727 ↑ 15.2 1,433 1

Merge Full Join (cost=882,222.89..882,657.98 rows=21,725 width=184) (actual time=770.554..774.727 rows=1,433 loops=1)

  • Merge Cond: (((COALESCE(data_stats_session.day, data_stats_page_view.day)) = data_stats_event.day) AND ((COALESCE(data_stats_session.group1, data_stats_page_view.group1)) = data_stats_event.group1) AND ((COALESCE(data_stats_session.group2, data_stats_page_view.group2)) = data_stats_event.group2))
19. 1.096 535.433 ↑ 15.2 1,433 1

Sort (cost=616,720.72..616,775.03 rows=21,725 width=240) (actual time=535.340..535.433 rows=1,433 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day)), (COALESCE(data_stats_session.group1, data_stats_page_view.group1)), (COALESCE(data_stats_session.group2, data_stats_page_view.group2))
  • Sort Method: quicksort Memory: 429kB
20. 1.463 534.337 ↑ 15.2 1,433 1

Merge Full Join (cost=613,318.66..613,753.75 rows=21,725 width=240) (actual time=532.684..534.337 rows=1,433 loops=1)

  • Merge Cond: (((COALESCE(data_stats_session.day)) = data_stats_page_view.day) AND ((COALESCE(data_stats_session.group1)) = data_stats_page_view.group1) AND ((COALESCE(data_stats_session.group2)) = data_stats_page_view.group2))
21. 0.927 338.574 ↑ 15.2 1,433 1

Sort (cost=368,937.49..368,991.80 rows=21,725 width=112) (actual time=338.473..338.574 rows=1,433 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day)), (COALESCE(data_stats_session.group1)), (COALESCE(data_stats_session.group2))
  • Sort Method: quicksort Memory: 251kB
22. 0.310 337.647 ↑ 15.2 1,433 1

Subquery Scan on data_stats_session (cost=341,915.15..367,372.52 rows=21,725 width=112) (actual time=285.701..337.647 rows=1,433 loops=1)

23. 18.275 337.337 ↑ 15.2 1,433 1

GroupAggregate (cost=341,915.15..367,155.27 rows=21,725 width=208) (actual time=285.700..337.337 rows=1,433 loops=1)

  • Group Key: (COALESCE((data."stats_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_session.created_at")::text, '-'::text))
  • Group Key: ()
24. 13.279 319.062 ↑ 6.3 17,141 1

Group (cost=341,915.15..364,516.46 rows=107,625 width=354) (actual time=285.630..319.062 rows=17,141 loops=1)

  • Group Key: (COALESCE((data."stats_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_session.user_agent_family", data."stats_session.id", data."stats_session.user_id", data."stats_session.created_at
25. 154.009 305.783 ↑ 27.2 39,600 1

Sort (cost=341,915.15..344,605.78 rows=1,076,253 width=354) (actual time=285.626..305.783 rows=39,600 loops=1)

  • Sort Key: (COALESCE((data."stats_session.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_session.user_agent_family", data."stats_session.id", data."stats_session.user_id", data."stats_session.created_at
  • Sort Method: external merge Disk: 3792kB
26. 151.774 151.774 ↑ 27.2 39,600 1

CTE Scan on data (cost=0.00..32,287.59 rows=1,076,253 width=354) (actual time=53.198..151.774 rows=39,600 loops=1)

27. 0.717 194.300 ↑ 15.2 1,433 1

Sort (cost=244,381.17..244,435.48 rows=21,725 width=128) (actual time=194.202..194.300 rows=1,433 loops=1)

  • Sort Key: data_stats_page_view.day, data_stats_page_view.group1, data_stats_page_view.group2
  • Sort Method: quicksort Memory: 173kB
28. 0.286 193.583 ↑ 15.2 1,433 1

Subquery Scan on data_stats_page_view (cost=219,995.15..242,816.20 rows=21,725 width=128) (actual time=147.070..193.583 rows=1,433 loops=1)

29. 16.732 193.297 ↑ 15.2 1,433 1

GroupAggregate (cost=219,995.15..242,598.95 rows=21,725 width=224) (actual time=147.069..193.297 rows=1,433 loops=1)

  • Group Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_1."stats_event.event_id")::text, '-'::text))
  • Group Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
30. 14.733 176.565 ↑ 3.0 36,262 1

Group (cost=219,995.15..239,905.83 rows=107,625 width=124) (actual time=146.996..176.565 rows=36,262 loops=1)

  • Group Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_1."stats_event.event_id")::text, '-'::text)), data_1."stats_page_view.runtime_active", data_1."stats_page_view.id", data_1."stats_page_view.created_at
31. 137.536 161.832 ↑ 27.2 39,600 1

Sort (cost=219,995.15..222,685.78 rows=1,076,253 width=124) (actual time=146.993..161.832 rows=39,600 loops=1)

  • Sort Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_1."stats_event.event_id")::text, '-'::text)), data_1."stats_page_view.runtime_active", data_1."stats_page_view.id", data_1."stats_page_view.created_at
  • Sort Method: external merge Disk: 2952kB
32. 24.296 24.296 ↑ 27.2 39,600 1

CTE Scan on data data_1 (cost=0.00..32,287.59 rows=1,076,253 width=124) (actual time=0.065..24.296 rows=39,600 loops=1)

33. 0.727 235.307 ↑ 15.2 1,433 1

Sort (cost=265,502.18..265,556.49 rows=21,725 width=136) (actual time=235.205..235.307 rows=1,433 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 173kB
34. 0.240 234.580 ↑ 15.2 1,433 1

Subquery Scan on data_stats_event (cost=236,811.15..263,937.21 rows=21,725 width=136) (actual time=165.794..234.580 rows=1,433 loops=1)

35. 20.053 234.340 ↑ 15.2 1,433 1

GroupAggregate (cost=236,811.15..263,719.96 rows=21,725 width=232) (actual time=165.792..234.340 rows=1,433 loops=1)

  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_2."stats_event.event_id")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text))
  • Group Key: ()
36. 16.941 214.287 ↑ 2.7 39,600 1

Group (cost=236,811.15..259,412.46 rows=107,625 width=156) (actual time=165.737..214.287 rows=39,600 loops=1)

  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_2."stats_event.event_id")::text, '-'::text)), data_2."stats_event.event_id", data_2."stats_event.values->>'scrollTriggerValue'", data_2."stats_event.id", data_2."stats_event.created_at
37. 173.829 197.346 ↑ 27.2 39,600 1

Sort (cost=236,811.15..239,501.78 rows=1,076,253 width=156) (actual time=165.733..197.346 rows=39,600 loops=1)

  • Sort Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_2."stats_event.event_id")::text, '-'::text)), data_2."stats_event.event_id", data_2."stats_event.values->>'scrollTriggerValue'", data_2."stats_event.id", data_2."stats_event.created_at
  • Sort Method: external merge Disk: 2952kB
38. 23.517 23.517 ↑ 27.2 39,600 1

CTE Scan on data data_2 (cost=0.00..32,287.59 rows=1,076,253 width=156) (actual time=0.025..23.517 rows=39,600 loops=1)