explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xdgs

Settings
# exclusive inclusive rows x rows loops node
1. 0.358 949.544 ↑ 23.7 177 1

Sort (cost=96,394.02..96,404.49 rows=4,187 width=196) (actual time=949.533..949.544 rows=177 loops=1)

  • Sort Key: (CASE COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.day) WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.day)) DESC, (CASE COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.group1) WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.group1)), (CASE COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."event.name")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.group2) WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."event.name")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.group2))
  • Sort Method: quicksort Memory: 49kB
2.          

CTE data

3. 12.884 291.170 ↓ 4.9 97,000 1

Gather (cost=70,302.66..80,344.86 rows=19,933 width=660) (actual time=96.591..291.170 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 112.059 278.286 ↓ 11.7 97,000 1

Parallel Hash Join (cost=69,302.66..77,351.56 rows=8,305 width=660) (actual time=95.853..278.286 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))
5. 31.088 164.799 ↓ 31.8 97,000 1

Hash Left Join (cost=68,540.29..76,181.01 rows=3,051 width=628) (actual time=94.235..164.799 rows=97,000 loops=1)

  • Hash Cond: (_hyper_5_4_chunk.event_id = event.id)
6. 31.844 133.693 ↓ 31.8 97,000 1

Parallel Hash Join (cost=68,529.62..76,162.32 rows=3,051 width=104) (actual time=94.203..133.693 rows=97,000 loops=1)

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

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

8. 7.412 7.412 ↓ 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..7.412 rows=9,700 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-14 09:20:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 09:20: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-14 09:20:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 09:20:59+00'::timestamp with time zone) AND (property_id = 1))
10. 27.167 93.467 ↓ 2.6 97,000 1

Parallel Hash (cost=68,059.34..68,059.34 rows=37,622 width=76) (actual time=93.467..93.467 rows=97,000 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8640kB
11. 9.734 66.300 ↓ 2.6 97,000 1

Parallel Append (cost=0.00..68,059.34 rows=37,622 width=76) (actual time=0.018..66.300 rows=97,000 loops=1)

12. 56.566 56.566 ↓ 2.6 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..67,871.23 rows=37,621 width=76) (actual time=0.017..56.566 rows=97,000 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-14 09:20:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 09:20:59+00'::timestamp with time zone))
13. 0.000 0.000 ↓ 0.0 0 1

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

  • Filter: ((created_at >= '2019-03-14 09:20:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 09:20:59+00'::timestamp with time zone) AND (property_id = 1))
14. 0.002 0.018 ↑ 10.0 3 1

Hash (cost=10.30..10.30 rows=30 width=528) (actual time=0.018..0.018 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.016 0.016 ↑ 10.0 3 1

Seq Scan on event (cost=0.00..10.30 rows=30 width=528) (actual time=0.015..0.016 rows=3 loops=1)

16. 0.275 1.428 ↓ 2.4 970 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
17. 0.093 1.153 ↓ 2.4 970 1

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

18. 1.059 1.059 ↓ 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.028..1.059 rows=970 loops=1)

  • Index Cond: ((property_id = 1) AND (created_at >= '2019-03-14 09:20:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 09:20:59+00'::timestamp with time zone))
19. 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-14 09:20:00+00'::timestamp with time zone) AND (created_at <= '2019-05-13 09:20:59+00'::timestamp with time zone) AND (property_id = 1))
20. 0.210 949.186 ↑ 23.7 177 1

Merge Full Join (cost=15,717.98..15,797.27 rows=4,187 width=196) (actual time=948.952..949.186 rows=177 loops=1)

  • Merge Cond: (((COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)))) = data_stats_event.day) AND ((COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)))) = data_stats_event.group1) AND ((COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."event.name")::text, '-'::text)), '[TOTAL]'::text)))) = data_stats_event.group2))
21. 0.384 821.711 ↑ 3.4 177 1

Sort (cost=13,478.26..13,479.76 rows=601 width=240) (actual time=821.692..821.711 rows=177 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)))), (COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)))), (COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."event.name")::text, '-'::text)), '[TOTAL]'::text))))
  • Sort Method: quicksort Memory: 71kB
22. 0.125 821.327 ↑ 3.4 177 1

Hash Full Join (cost=12,958.40..13,450.52 rows=601 width=240) (actual time=821.136..821.327 rows=177 loops=1)

  • Hash Cond: (((COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.day)) AND ((COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group1)) AND ((COALESCE((COALESCE((data."event.name")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group2)))
23. 17.258 260.928 ↑ 3.4 177 1

MixedAggregate (cost=6,243.83..6,590.19 rows=601 width=224) (actual time=260.853..260.928 rows=177 loops=1)

  • Hash Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."event.name")::text, '-'::text))
  • Hash Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Hash Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
24. 22.224 243.670 ↓ 9.7 19,378 1

Unique (cost=6,243.83..6,492.99 rows=1,993 width=858) (actual time=200.415..243.670 rows=19,378 loops=1)

25. 162.249 221.446 ↓ 4.9 97,000 1

Sort (cost=6,243.83..6,293.66 rows=19,933 width=858) (actual time=200.413..221.446 rows=97,000 loops=1)

  • Sort Key: data."stats_page_view.id", data."stats_page_view.created_at", data."stats_session.user_agent_family", data."event.name
  • Sort Method: external merge Disk: 9536kB
26. 59.197 59.197 ↓ 4.9 97,000 1

CTE Scan on data (cost=0.00..498.33 rows=19,933 width=858) (actual time=0.052..59.197 rows=97,000 loops=1)

27. 0.090 560.274 ↑ 3.4 177 1

Hash (cost=6,704.06..6,704.06 rows=601 width=112) (actual time=560.274..560.274 rows=177 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
28. 0.036 560.184 ↑ 3.4 177 1

Subquery Scan on data_stats_session (cost=6,662.14..6,704.06 rows=601 width=112) (actual time=557.163..560.184 rows=177 loops=1)

29. 2.829 560.148 ↑ 3.4 177 1

GroupAggregate (cost=6,662.14..6,698.05 rows=601 width=208) (actual time=557.161..560.148 rows=177 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: ()
30. 2.122 557.319 ↑ 1.0 1,940 1

Sort (cost=6,662.14..6,667.13 rows=1,993 width=128) (actual time=557.132..557.319 rows=1,940 loops=1)

  • Sort Key: agg_stats_session.day, agg_stats_session.group1, agg_stats_session.group2
  • Sort Method: quicksort Memory: 321kB
31. 0.387 555.197 ↑ 1.0 1,940 1

Subquery Scan on agg_stats_session (cost=6,283.83..6,552.92 rows=1,993 width=128) (actual time=512.186..555.197 rows=1,940 loops=1)

32. 21.139 554.810 ↑ 1.0 1,940 1

Unique (cost=6,283.83..6,532.99 rows=1,993 width=870) (actual time=512.183..554.810 rows=1,940 loops=1)

33. 128.831 533.671 ↓ 4.9 97,000 1

Sort (cost=6,283.83..6,333.66 rows=19,933 width=870) (actual time=512.182..533.671 rows=97,000 loops=1)

  • Sort Key: data_1."stats_session.id", data_1."stats_session.created_at", data_1."stats_session.user_agent_family", data_1."event.name
  • Sort Method: external merge Disk: 10616kB
34. 404.840 404.840 ↓ 4.9 97,000 1

CTE Scan on data data_1 (cost=0.00..498.33 rows=19,933 width=870) (actual time=96.598..404.840 rows=97,000 loops=1)

35. 0.261 127.265 ↑ 23.7 177 1

Sort (cost=2,239.73..2,250.19 rows=4,187 width=136) (actual time=127.252..127.265 rows=177 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 41kB
36. 0.029 127.004 ↑ 23.7 177 1

Subquery Scan on data_stats_event (cost=0.00..1,987.84 rows=4,187 width=136) (actual time=126.895..127.004 rows=177 loops=1)

37. 70.770 126.975 ↑ 23.7 177 1

MixedAggregate (cost=0.00..1,945.97 rows=4,187 width=232) (actual time=126.894..126.975 rows=177 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."event.name")::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. 56.205 56.205 ↓ 4.9 97,000 1

CTE Scan on data data_2 (cost=0.00..398.66 rows=19,933 width=148) (actual time=0.015..56.205 rows=97,000 loops=1)