explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KdJf

Settings
# exclusive inclusive rows x rows loops node
1. 1.386 921.009 ↑ 1.4 2,864 1

Sort (cost=95,928.23..95,937.98 rows=3,897 width=184) (actual time=920.865..921.009 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. 48.683 473.368 ↓ 5.2 97,000 1

Gather Merge (cost=85,627.89..87,784.51 rows=18,484 width=136) (actual time=377.742..473.368 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 196.831 424.685 ↓ 10.5 97,000 1

Sort (cost=84,627.87..84,650.97 rows=9,242 width=136) (actual time=377.076..424.685 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. 86.540 227.854 ↓ 10.5 97,000 1

Parallel Hash Join (cost=75,345.85..84,019.10 rows=9,242 width=136) (actual time=100.733..227.854 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. 43.799 139.930 ↓ 28.6 97,000 1

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

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

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

8. 5.599 5.599 ↓ 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.024..5.599 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.724 89.593 ↓ 2.3 97,000 1

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

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

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

12. 51.094 51.094 ↓ 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.015..51.094 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.287 1.384 ↓ 2.4 970 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
15. 0.150 1.097 ↓ 2.4 970 1

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

16. 0.946 0.946 ↓ 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.026..0.946 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.000..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.805 919.623 ↑ 1.4 2,864 1

Merge Full Join (cost=7,866.32..7,911.31 rows=3,897 width=184) (actual time=916.439..919.623 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)) = data_stats_event.group1) AND ((COALESCE(data_stats_session.group2, data_stats_page_view.group2)) = data_stats_event.group2))
19. 1.872 764.705 ↓ 4.3 2,576 1

Sort (cost=5,790.24..5,791.74 rows=601 width=240) (actual time=764.543..764.705 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)), (COALESCE(data_stats_session.group2, data_stats_page_view.group2))
  • Sort Method: quicksort Memory: 561kB
20. 1.681 762.833 ↓ 4.3 2,576 1

Merge Full Join (cost=5,750.47..5,762.50 rows=601 width=240) (actual time=760.908..762.833 rows=2,576 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.555 606.172 ↓ 1.4 850 1

Sort (cost=3,743.30..3,744.81 rows=601 width=112) (actual time=606.113..606.172 rows=850 loops=1)

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

Subquery Scan on data_stats_session (cost=3,700.54..3,715.56 rows=601 width=112) (actual time=603.774..605.617 rows=850 loops=1)

23. 1.648 605.454 ↓ 1.4 850 1

GroupAggregate (cost=3,700.54..3,709.55 rows=601 width=208) (actual time=603.772..605.454 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. 1.841 603.806 ↓ 4.8 970 1

Sort (cost=3,700.54..3,701.04 rows=200 width=128) (actual time=603.739..603.806 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.189 601.965 ↓ 4.8 970 1

Subquery Scan on agg_stats_session (cost=3,598.48..3,692.90 rows=200 width=128) (actual time=578.406..601.965 rows=970 loops=1)

26. 7.093 601.776 ↓ 4.8 970 1

Unique (cost=3,598.48..3,690.90 rows=200 width=354) (actual time=578.404..601.776 rows=970 loops=1)

27. 65.934 594.683 ↓ 5.2 97,000 1

Sort (cost=3,598.48..3,644.69 rows=18,484 width=354) (actual time=578.403..594.683 rows=97,000 loops=1)

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

CTE Scan on data (cost=0.00..554.52 rows=18,484 width=354) (actual time=377.753..528.749 rows=97,000 loops=1)

29. 6.914 154.980 ↓ 4.2 2,540 1

Sort (cost=2,007.16..2,008.66 rows=601 width=128) (actual time=154.784..154.980 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.404 148.066 ↓ 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=146.213..148.066 rows=2,540 loops=1)

31. 11.853 147.662 ↓ 4.2 2,540 1

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

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

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

33. 67.614 126.936 ↓ 5.2 97,000 1

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

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

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

35. 6.939 152.113 ↑ 1.4 2,864 1

Sort (cost=2,076.08..2,085.82 rows=3,897 width=136) (actual time=151.888..152.113 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.451 145.174 ↑ 1.4 2,864 1

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

37. 79.153 144.723 ↑ 1.4 2,864 1

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

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

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

Planning time : 1.376 ms
Execution time : 983.076 ms