explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R1hj

Settings
# exclusive inclusive rows x rows loops node
1. 10.097 1,137.498 ↑ 419.3 2,864 1

Sort (cost=15,059,198.95..15,062,201.39 rows=1,200,975 width=184) (actual time=1,131.017..1,137.498 rows=2,864 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day, (COALESCE((COALESCE((data."stats_event.created_at")::text, '-'::text)), '[TOTAL]'::text)))), (COALESCE(data_stats_session.group1, data_stats_page_view.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)))), (COALESCE(data_stats_session.group2, data_stats_page_view.group2, (COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text))))
  • Sort Method: quicksort Memory: 451kB
2.          

CTE data

3. 12.236 394.777 ↑ 119.7 97,000 1

Gather Merge (cost=1,680,842.26..3,035,172.68 rows=11,607,736 width=136) (actual time=358.889..394.777 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 207.332 382.541 ↑ 59.8 97,000 1

Sort (cost=1,679,842.24..1,694,351.91 rows=5,803,868 width=136) (actual time=358.047..382.541 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. 99.451 175.209 ↑ 59.8 97,000 1

Parallel Hash Join (cost=8,938.07..347,676.78 rows=5,803,868 width=136) (actual time=17.018..175.209 rows=97,000 loops=1)

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

Parallel Append (cost=0.00..73,847.64 rows=42,111 width=76) (actual time=0.019..59.279 rows=97,000 loops=1)

7. 49.961 49.961 ↓ 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,637.09 rows=42,110 width=76) (actual time=0.018..49.961 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))
8. 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))
9. 3.832 16.479 ↑ 2.1 9,700 1

Parallel Hash (cost=8,679.65..8,679.65 rows=20,674 width=80) (actual time=16.479..16.479 rows=9,700 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1600kB
10. 4.141 12.647 ↑ 2.1 9,700 1

Parallel Hash Join (cost=762.37..8,679.65 rows=20,674 width=80) (actual time=1.556..12.647 rows=9,700 loops=1)

  • Hash Cond: (_hyper_3_3_chunk.session_id = _hyper_2_2_chunk.id)
11. 0.959 6.985 ↓ 2.3 9,700 1

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

12. 6.026 6.026 ↓ 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.023..6.026 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))
13. 0.000 0.000 ↓ 0.0 0 1

Parallel Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual time=0.000..0.000 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.404 1.521 ↓ 2.4 970 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
15. 0.101 1.117 ↓ 2.4 970 1

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

16. 1.015 1.015 ↓ 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.015 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. 15.816 1,127.401 ↑ 419.3 2,864 1

Hash Full Join (cost=11,184,334.24..11,780,777.16 rows=1,200,975 width=184) (actual time=1,025.635..1,127.401 rows=2,864 loops=1)

  • Hash Cond: (((COALESCE((COALESCE((data."stats_event.created_at")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.day, data_stats_page_view.day)) AND ((COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group1, data_stats_page_view.group1)) AND ((COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group2, data_stats_page_view.group2)))
19. 40.283 407.033 ↑ 419.3 2,864 1

GroupAggregate (cost=3,090,550.31..3,395,755.90 rows=1,200,975 width=232) (actual time=321.072..407.033 rows=2,864 loops=1)

  • Group Key: (COALESCE((data."stats_event.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_event.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_event.created_at")::text, '-'::text))
  • Group Key: ()
20. 297.878 366.750 ↑ 119.7 97,000 1

Sort (cost=3,090,550.31..3,119,569.65 rows=11,607,736 width=148) (actual time=321.052..366.750 rows=97,000 loops=1)

  • Sort Key: (COALESCE((data."stats_event.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text))
  • Sort Method: external merge Disk: 6384kB
21. 68.872 68.872 ↑ 119.7 97,000 1

CTE Scan on data (cost=0.00..232,154.72 rows=11,607,736 width=148) (actual time=0.025..68.872 rows=97,000 loops=1)

22. 0.957 704.552 ↓ 4.3 2,576 1

Hash (cost=8,093,773.41..8,093,773.41 rows=601 width=240) (actual time=704.552..704.552 rows=2,576 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 279kB
23. 1.937 703.595 ↓ 4.3 2,576 1

Merge Full Join (cost=8,093,761.38..8,093,773.41 rows=601 width=240) (actual time=701.350..703.595 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))
24. 0.528 548.844 ↓ 1.4 850 1

Sort (cost=5,033,086.76..5,033,088.26 rows=601 width=112) (actual time=548.779..548.844 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
25. 0.173 548.316 ↓ 1.4 850 1

Subquery Scan on data_stats_session (cost=5,033,044.00..5,033,059.02 rows=601 width=112) (actual time=546.148..548.316 rows=850 loops=1)

26. 1.953 548.143 ↓ 1.4 850 1

GroupAggregate (cost=5,033,044.00..5,033,053.01 rows=601 width=208) (actual time=546.146..548.143 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: ()
27. 2.521 546.190 ↓ 4.8 970 1

Sort (cost=5,033,044.00..5,033,044.50 rows=200 width=128) (actual time=546.108..546.190 rows=970 loops=1)

  • Sort Key: agg_stats_session.day, agg_stats_session.group1, agg_stats_session.group2
  • Sort Method: quicksort Memory: 161kB
28. 0.193 543.669 ↓ 4.8 970 1

Subquery Scan on agg_stats_session (cost=4,974,995.67..5,033,036.35 rows=200 width=128) (actual time=517.885..543.669 rows=970 loops=1)

29. 8.019 543.476 ↓ 4.8 970 1

Unique (cost=4,974,995.67..5,033,034.35 rows=200 width=354) (actual time=517.883..543.476 rows=970 loops=1)

30. 58.846 535.457 ↑ 119.7 97,000 1

Sort (cost=4,974,995.67..5,004,015.01 rows=11,607,736 width=354) (actual time=517.882..535.457 rows=97,000 loops=1)

  • Sort Key: data_1."stats_session.id
  • Sort Method: external merge Disk: 7368kB
31. 476.611 476.611 ↑ 119.7 97,000 1

CTE Scan on data data_1 (cost=0.00..348,232.08 rows=11,607,736 width=354) (actual time=358.899..476.611 rows=97,000 loops=1)

32. 6.481 152.814 ↓ 4.2 2,540 1

Sort (cost=3,060,674.62..3,060,676.12 rows=601 width=128) (actual time=152.560..152.814 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
33. 0.396 146.333 ↓ 4.2 2,540 1

Subquery Scan on data_stats_page_view (cost=3,002,585.67..3,060,646.88 rows=601 width=128) (actual time=144.691..146.333 rows=2,540 loops=1)

34. 10.993 145.937 ↓ 4.2 2,540 1

MixedAggregate (cost=3,002,585.67..3,060,640.87 rows=601 width=224) (actual time=144.689..145.937 rows=2,540 loops=1)

  • Hash Key: (COALESCE((data_2."stats_page_view.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_page_view.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text))
  • Hash Key: (COALESCE((data_2."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
35. 8.390 134.944 ↓ 48.5 9,700 1

Unique (cost=3,002,585.67..3,060,624.35 rows=200 width=124) (actual time=109.437..134.944 rows=9,700 loops=1)

36. 68.347 126.554 ↑ 119.7 97,000 1

Sort (cost=3,002,585.67..3,031,605.01 rows=11,607,736 width=124) (actual time=109.436..126.554 rows=97,000 loops=1)

  • Sort Key: data_2."stats_page_view.id
  • Sort Method: external merge Disk: 6408kB
37. 58.207 58.207 ↑ 119.7 97,000 1

CTE Scan on data data_2 (cost=0.00..348,232.08 rows=11,607,736 width=124) (actual time=0.037..58.207 rows=97,000 loops=1)