explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q2oc

Settings
# exclusive inclusive rows x rows loops node
1. 1.543 936.655 ↑ 1.4 2,864 1

Sort (cost=94,194.23..94,203.98 rows=3,897 width=184) (actual time=936.500..936.655 rows=2,864 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day, data_stats_event.day)), (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: 451kB
2.          

CTE data

3. 25.016 461.912 ↓ 5.2 97,000 1

Gather Merge (cost=85,627.89..87,784.51 rows=18,484 width=160) (actual time=391.673..461.912 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 199.649 436.896 ↓ 10.5 97,000 1

Sort (cost=84,627.87..84,650.97 rows=9,242 width=160) (actual time=390.694..436.896 rows=97,000 loops=1)

  • Sort Key: _hyper_2_2_chunk.id, _hyper_3_3_chunk.id, _hyper_5_4_chunk.id
  • Sort Method: external merge Disk: 12576kB
5. 91.741 237.247 ↓ 10.5 97,000 1

Parallel Hash Join (cost=75,345.85..84,019.10 rows=9,242 width=160) (actual time=103.007..237.247 rows=97,000 loops=1)

  • Hash Cond: (_hyper_3_3_chunk.session_id = _hyper_2_2_chunk.id)
  • Join Filter: ((_hyper_2_2_chunk.campaign_id >= 1) OR (((_hyper_5_4_chunk."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
6. 46.028 143.873 ↓ 28.6 97,000 1

Parallel Hash Join (cost=74,583.48..82,825.61 rows=3,395 width=104) (actual time=101.051..143.873 rows=97,000 loops=1)

  • Hash Cond: (_hyper_3_3_chunk.id = _hyper_5_4_chunk.page_view_id)
7. 0.970 6.778 ↓ 2.3 9,700 1

Parallel Append (cost=0.00..7,604.15 rows=4,220 width=44) (actual time=0.042..6.778 rows=9,700 loops=1)

8. 5.807 5.807 ↓ 1.6 9,700 1

Parallel Index Scan using _hyper_3_3_chunk_idx_pageview_property_id_and_created_at on _hyper_3_3_chunk (cost=0.42..7,583.05 rows=5,956 width=44) (actual time=0.041..5.807 rows=9,700 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone))
9. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone) AND (property_id = 1))
10. 28.800 91.067 ↓ 2.3 97,000 1

Parallel Hash (cost=73,528.20..73,528.20 rows=41,862 width=76) (actual time=91.066..91.067 rows=97,000 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2496kB
11. 9.580 62.267 ↓ 2.3 97,000 1

Parallel Append (cost=0.00..73,528.20 rows=41,862 width=76) (actual time=0.018..62.267 rows=97,000 loops=1)

12. 52.686 52.686 ↓ 2.3 97,000 1

Parallel Index Scan using _hyper_5_4_chunk_idx_event_property_id_and_created_at on _hyper_5_4_chunk (cost=0.43..73,318.89 rows=41,861 width=76) (actual time=0.016..52.686 rows=97,000 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone))
13. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone) AND (property_id = 1))
14. 0.325 1.633 ↓ 2.4 970 1

Parallel Hash (cost=757.26..757.26 rows=409 width=52) (actual time=1.633..1.633 rows=970 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
15. 0.095 1.308 ↓ 2.4 970 1

Parallel Append (cost=0.00..757.26 rows=409 width=52) (actual time=0.032..1.308 rows=970 loops=1)

16. 1.212 1.212 ↓ 1.7 970 1

Parallel Index Scan using _hyper_2_2_chunk_idx_session_property_id_and_created_at on _hyper_2_2_chunk (cost=0.29..755.21 rows=576 width=52) (actual time=0.030..1.212 rows=970 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone))
17. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((created_at >= '2019-03-11 12:06:00+00'::timestamp with time zone) AND (created_at <= '2019-05-10 12:06:59+00'::timestamp with time zone) AND (property_id = 1))
18. 2.911 935.112 ↑ 1.4 2,864 1

Merge Full Join (cost=6,132.32..6,177.31 rows=3,897 width=184) (actual time=931.821..935.112 rows=2,864 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))::text) = (data_stats_event.group1)::text) AND ((COALESCE(data_stats_session.group2, data_stats_page_view.group2)) = data_stats_event.group2))
19. 1.975 785.711 ↓ 4.3 2,576 1

Sort (cost=4,056.24..4,057.74 rows=601 width=240) (actual time=785.546..785.711 rows=2,576 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day)), ((COALESCE(data_stats_session.group1, data_stats_page_view.group1))::text), (COALESCE(data_stats_session.group2, data_stats_page_view.group2))
  • Sort Method: quicksort Memory: 561kB
20. 1.706 783.736 ↓ 4.3 2,576 1

Merge Full Join (cost=4,016.47..4,028.50 rows=601 width=240) (actual time=781.774..783.736 rows=2,576 loops=1)

  • Merge Cond: (((COALESCE(data_stats_session.day)) = data_stats_page_view.day) AND (((COALESCE(data_stats_session.group1))::text) = (data_stats_page_view.group1)::text) AND ((COALESCE(data_stats_session.group2)) = data_stats_page_view.group2))
21. 0.524 624.339 ↓ 1.4 850 1

Sort (cost=2,009.30..2,010.81 rows=601 width=112) (actual time=624.281..624.339 rows=850 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day)), ((COALESCE(data_stats_session.group1))::text), (COALESCE(data_stats_session.group2))
  • Sort Method: quicksort Memory: 145kB
22. 0.169 623.815 ↓ 1.4 850 1

Subquery Scan on data_stats_session (cost=1,966.54..1,981.56 rows=601 width=112) (actual time=621.662..623.815 rows=850 loops=1)

23. 1.926 623.646 ↓ 1.4 850 1

GroupAggregate (cost=1,966.54..1,975.55 rows=601 width=208) (actual time=621.660..623.646 rows=850 loops=1)

  • Group Key: agg_stats_session.day, agg_stats_session.group1, agg_stats_session.group2
  • Group Key: agg_stats_session.day, agg_stats_session.group1
  • Group Key: agg_stats_session.day
  • Group Key: ()
24. 2.187 621.720 ↓ 4.8 970 1

Sort (cost=1,966.54..1,967.04 rows=200 width=128) (actual time=621.635..621.720 rows=970 loops=1)

  • Sort Key: agg_stats_session.day, agg_stats_session.group1, agg_stats_session.group2
  • Sort Method: quicksort Memory: 161kB
25. 0.197 619.533 ↓ 4.8 970 1

Subquery Scan on agg_stats_session (cost=1,864.48..1,958.90 rows=200 width=128) (actual time=594.543..619.533 rows=970 loops=1)

26. 7.790 619.336 ↓ 4.8 970 1

Unique (cost=1,864.48..1,956.90 rows=200 width=168) (actual time=594.540..619.336 rows=970 loops=1)

27. 67.627 611.546 ↓ 5.2 97,000 1

Sort (cost=1,864.48..1,910.69 rows=18,484 width=168) (actual time=594.538..611.546 rows=97,000 loops=1)

  • Sort Key: data."stats_session.id
  • Sort Method: external merge Disk: 7368kB
28. 543.919 543.919 ↓ 5.2 97,000 1

CTE Scan on data (cost=0.00..554.52 rows=18,484 width=168) (actual time=391.682..543.919 rows=97,000 loops=1)

29. 7.170 157.691 ↓ 4.2 2,540 1

Sort (cost=2,007.16..2,008.66 rows=601 width=128) (actual time=157.481..157.691 rows=2,540 loops=1)

  • Sort Key: data_stats_page_view.day, data_stats_page_view.group1, data_stats_page_view.group2
  • Sort Method: quicksort Memory: 318kB
30. 0.410 150.521 ↓ 4.2 2,540 1

Subquery Scan on data_stats_page_view (cost=1,864.48..1,979.42 rows=601 width=128) (actual time=148.722..150.521 rows=2,540 loops=1)

31. 12.171 150.111 ↓ 4.2 2,540 1

MixedAggregate (cost=1,864.48..1,973.41 rows=601 width=224) (actual time=148.721..150.111 rows=2,540 loops=1)

  • Hash Key: ((data_1."stats_page_view.created_at")::text), data_1."stats_session.user_agent_family", (COALESCE((data_1."stats_event.event_id")::text, '-'::text))
  • Hash Key: ((data_1."stats_page_view.created_at")::text), data_1."stats_session.user_agent_family
  • Hash Key: ((data_1."stats_page_view.created_at")::text)
  • Group Key: ()
32. 9.361 137.940 ↓ 48.5 9,700 1

Unique (cost=1,864.48..1,956.90 rows=200 width=124) (actual time=110.724..137.940 rows=9,700 loops=1)

33. 69.223 128.579 ↓ 5.2 97,000 1

Sort (cost=1,864.48..1,910.69 rows=18,484 width=124) (actual time=110.722..128.579 rows=97,000 loops=1)

  • Sort Key: data_1."stats_page_view.id
  • Sort Method: external merge Disk: 6408kB
34. 59.356 59.356 ↓ 5.2 97,000 1

CTE Scan on data data_1 (cost=0.00..554.52 rows=18,484 width=124) (actual time=0.050..59.356 rows=97,000 loops=1)

35. 7.439 146.490 ↑ 1.4 2,864 1

Sort (cost=2,076.08..2,085.82 rows=3,897 width=136) (actual time=146.267..146.490 rows=2,864 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 346kB
36. 0.442 139.051 ↑ 1.4 2,864 1

Subquery Scan on data_stats_event (cost=0.00..1,843.66 rows=3,897 width=136) (actual time=137.702..139.051 rows=2,864 loops=1)

37. 74.741 138.609 ↑ 1.4 2,864 1

MixedAggregate (cost=0.00..1,804.69 rows=3,897 width=232) (actual time=137.701..138.609 rows=2,864 loops=1)

  • Hash Key: (data_2."stats_event.created_at")::text, data_2."stats_session.user_agent_family", COALESCE((data_2."stats_event.event_id")::text, '-'::text)
  • Hash Key: (data_2."stats_event.created_at")::text, data_2."stats_session.user_agent_family
  • Hash Key: (data_2."stats_event.created_at")::text
  • Group Key: ()
38. 63.868 63.868 ↓ 5.2 97,000 1

CTE Scan on data data_2 (cost=0.00..369.68 rows=18,484 width=148) (actual time=0.026..63.868 rows=97,000 loops=1)