explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uwd3K

Settings
# exclusive inclusive rows x rows loops node
1. 1.770 939.560 ↑ 1.4 2,864 1

Sort (cost=95,928.23..95,937.98 rows=3,897 width=184) (actual time=939.399..939.560 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. 22.684 475.677 ↓ 5.2 97,000 1

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

  • Workers Planned: 2
  • Workers Launched: 0
4. 184.705 452.993 ↓ 10.5 97,000 1

Sort (cost=84,627.87..84,650.97 rows=9,242 width=136) (actual time=415.795..452.993 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. 92.022 268.288 ↓ 10.5 97,000 1

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

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

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

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

8. 13.724 13.724 ↓ 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.033..13.724 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. 33.305 115.205 ↓ 2.3 97,000 1

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

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

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

12. 72.594 72.594 ↓ 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.019..72.594 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.353 1.681 ↓ 2.4 970 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
15. 0.081 1.328 ↓ 2.4 970 1

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

16. 1.246 1.246 ↓ 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..1.246 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. 3.038 937.790 ↑ 1.4 2,864 1

Merge Full Join (cost=7,866.32..7,911.31 rows=3,897 width=184) (actual time=934.328..937.790 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. 2.207 790.800 ↓ 4.3 2,576 1

Sort (cost=5,790.24..5,791.74 rows=601 width=240) (actual time=790.617..790.800 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. 2.108 788.593 ↓ 4.3 2,576 1

Merge Full Join (cost=5,750.47..5,762.50 rows=601 width=240) (actual time=785.304..788.593 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. 1.428 614.721 ↓ 1.4 850 1

Sort (cost=3,743.30..3,744.81 rows=601 width=112) (actual time=613.769..614.721 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.174 613.293 ↓ 1.4 850 1

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

23. 1.790 613.119 ↓ 1.4 850 1

GroupAggregate (cost=3,700.54..3,709.55 rows=601 width=208) (actual time=611.273..613.119 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.454 611.329 ↓ 4.8 970 1

Sort (cost=3,700.54..3,701.04 rows=200 width=128) (actual time=611.250..611.329 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.187 608.875 ↓ 4.8 970 1

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

26. 6.999 608.688 ↓ 4.8 970 1

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

27. 57.322 601.689 ↓ 5.2 97,000 1

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

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

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

29. 6.060 171.764 ↓ 4.2 2,540 1

Sort (cost=2,007.16..2,008.66 rows=601 width=128) (actual time=171.525..171.764 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.412 165.704 ↓ 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=163.678..165.704 rows=2,540 loops=1)

31. 19.847 165.292 ↓ 4.2 2,540 1

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

  • Hash 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))
  • Hash Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text))
  • Hash Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
32. 9.740 145.445 ↓ 48.5 9,700 1

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

33. 72.586 135.705 ↓ 5.2 97,000 1

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

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

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

35. 7.335 143.952 ↑ 1.4 2,864 1

Sort (cost=2,076.08..2,085.82 rows=3,897 width=136) (actual time=143.701..143.952 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.458 136.617 ↑ 1.4 2,864 1

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

37. 73.003 136.159 ↑ 1.4 2,864 1

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

  • Hash 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)
  • Hash Key: COALESCE((data_2."stats_event.created_at")::text, '-'::text), COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)
  • Hash Key: COALESCE((data_2."stats_event.created_at")::text, '-'::text)
  • Group Key: ()
38. 63.156 63.156 ↓ 5.2 97,000 1

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