explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NGuIB

Settings
# exclusive inclusive rows x rows loops node
1. 0.347 869.152 ↑ 23.7 177 1

Sort (cost=87,825.00..87,835.47 rows=4,187 width=196) (actual time=869.143..869.152 rows=177 loops=1)

  • Sort Key: (CASE COALESCE(data_stats_session.day, data_stats_page_view.day, data_stats_event.day) WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session.day, data_stats_page_view.day, data_stats_event.day)) DESC, (CASE COALESCE(data_stats_session.group1, data_stats_page_view.group1, data_stats_event.group1) WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session.group1, data_stats_page_view.group1, data_stats_event.group1)), (CASE COALESCE(data_stats_session.group2, data_stats_page_view.group2, data_stats_event.group2) WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session.group2, data_stats_page_view.group2, data_stats_event.group2))
  • Sort Method: quicksort Memory: 49kB
2.          

CTE data

3. 112.608 271.542 ↓ 4.9 97,000 1

Hash Join (cost=70,765.11..80,542.60 rows=19,933 width=660) (actual time=91.083..271.542 rows=97,000 loops=1)

  • Hash Cond: (stats_page_view.session_id = stats_session.id)
  • Join Filter: ((stats_session.campaign_id >= 1) OR (((stats_event."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
4. 29.775 157.544 ↓ 13.2 97,000 1

Hash Left Join (cost=69,988.72..77,794.54 rows=7,322 width=628) (actual time=89.679..157.544 rows=97,000 loops=1)

  • Hash Cond: (stats_event.event_id = event.id)
5. 29.985 127.751 ↓ 13.2 97,000 1

Hash Join (cost=69,978.04..77,764.61 rows=7,322 width=104) (actual time=89.649..127.751 rows=97,000 loops=1)

  • Hash Cond: (stats_page_view.id = stats_event.page_view_id)
6. 0.994 8.296 ↑ 1.0 9,700 1

Append (cost=0.00..7,675.38 rows=10,126 width=44) (actual time=0.028..8.296 rows=9,700 loops=1)

7. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual time=0.004..0.004 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))
8. 7.298 7.298 ↑ 1.0 9,700 1

Index Scan using _hyper_3_3_chunk_idx_pageview_property_id_and_created_at on _hyper_3_3_chunk (cost=0.42..7,624.75 rows=10,125 width=44) (actual time=0.023..7.298 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. 20.590 89.470 ↓ 1.1 97,000 1

Hash (cost=68,849.39..68,849.39 rows=90,292 width=76) (actual time=89.469..89.470 rows=97,000 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8603kB
10. 9.889 68.880 ↓ 1.1 97,000 1

Append (cost=0.00..68,849.39 rows=90,292 width=76) (actual time=0.017..68.880 rows=97,000 loops=1)

11. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on stats_event (cost=0.00..0.00 rows=1 width=76) (actual time=0.002..0.002 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))
12. 58.989 58.989 ↓ 1.1 97,000 1

Index Scan using _hyper_5_4_chunk_idx_event_property_id_and_created_at on _hyper_5_4_chunk (cost=0.43..68,397.93 rows=90,291 width=76) (actual time=0.014..58.989 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.003 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
14. 0.015 0.015 ↑ 10.0 3 1

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

15. 0.216 1.390 ↑ 1.0 970 1

Hash (cost=764.14..764.14 rows=980 width=52) (actual time=1.390..1.390 rows=970 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 90kB
16. 0.097 1.174 ↑ 1.0 970 1

Append (cost=0.00..764.14 rows=980 width=52) (actual time=0.033..1.174 rows=970 loops=1)

17. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=262) (actual time=0.006..0.006 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))
18. 1.071 1.071 ↑ 1.0 970 1

Index Scan using _hyper_2_2_chunk_idx_session_property_id_and_created_at on _hyper_2_2_chunk (cost=0.29..759.24 rows=979 width=52) (actual time=0.026..1.071 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.210 868.805 ↑ 23.7 177 1

Merge Full Join (cost=6,951.23..7,030.52 rows=4,187 width=196) (actual time=868.576..868.805 rows=177 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))
20. 0.191 737.462 ↑ 3.4 177 1

Sort (cost=4,711.51..4,713.01 rows=601 width=240) (actual time=737.448..737.462 rows=177 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: 71kB
21. 0.264 737.271 ↑ 3.4 177 1

Merge Full Join (cost=4,671.74..4,683.77 rows=601 width=240) (actual time=737.038..737.271 rows=177 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))
22. 0.156 521.790 ↑ 3.4 177 1

Sort (cost=2,369.80..2,371.30 rows=601 width=112) (actual time=521.772..521.790 rows=177 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day)), (COALESCE(data_stats_session.group1)), (COALESCE(data_stats_session.group2))
  • Sort Method: quicksort Memory: 52kB
23. 0.051 521.634 ↑ 3.4 177 1

Subquery Scan on data_stats_session (cost=2,300.14..2,342.06 rows=601 width=112) (actual time=518.264..521.634 rows=177 loops=1)

24. 3.172 521.583 ↑ 3.4 177 1

GroupAggregate (cost=2,300.14..2,336.05 rows=601 width=208) (actual time=518.263..521.583 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: ()
25. 2.634 518.411 ↑ 1.0 1,940 1

Sort (cost=2,300.14..2,305.13 rows=1,993 width=128) (actual time=518.231..518.411 rows=1,940 loops=1)

  • Sort Key: agg_stats_session.day, agg_stats_session.group1, agg_stats_session.group2
  • Sort Method: quicksort Memory: 321kB
26. 0.494 515.777 ↑ 1.0 1,940 1

Subquery Scan on agg_stats_session (cost=1,921.83..2,190.92 rows=1,993 width=128) (actual time=480.172..515.777 rows=1,940 loops=1)

27. 25.748 515.283 ↑ 1.0 1,940 1

Unique (cost=1,921.83..2,170.99 rows=1,993 width=870) (actual time=480.169..515.283 rows=1,940 loops=1)

28. 110.293 489.535 ↓ 4.9 97,000 1

Sort (cost=1,921.83..1,971.66 rows=19,933 width=870) (actual time=480.168..489.535 rows=97,000 loops=1)

  • Sort Key: data."stats_session.id", data."stats_session.created_at", data."stats_session.user_agent_family", data."event.name
  • Sort Method: quicksort Memory: 18513kB
29. 379.242 379.242 ↓ 4.9 97,000 1

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

30. 0.377 215.217 ↑ 3.4 177 1

Sort (cost=2,301.94..2,303.44 rows=601 width=128) (actual time=215.205..215.217 rows=177 loops=1)

  • Sort Key: data_stats_page_view.day, data_stats_page_view.group1, data_stats_page_view.group2
  • Sort Method: quicksort Memory: 40kB
31. 0.026 214.840 ↑ 3.4 177 1

Subquery Scan on data_stats_page_view (cost=1,921.83..2,274.20 rows=601 width=128) (actual time=214.718..214.840 rows=177 loops=1)

32. 21.686 214.814 ↑ 3.4 177 1

MixedAggregate (cost=1,921.83..2,268.19 rows=601 width=224) (actual time=214.717..214.814 rows=177 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."event.name")::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: ()
33. 28.053 193.128 ↓ 9.7 19,378 1

Unique (cost=1,921.83..2,170.99 rows=1,993 width=858) (actual time=154.027..193.128 rows=19,378 loops=1)

34. 120.121 165.075 ↓ 4.9 97,000 1

Sort (cost=1,921.83..1,971.66 rows=19,933 width=858) (actual time=154.025..165.075 rows=97,000 loops=1)

  • Sort Key: data_1."stats_page_view.id", data_1."stats_page_view.created_at", data_1."stats_session.user_agent_family", data_1."event.name
  • Sort Method: quicksort Memory: 16713kB
35. 44.954 44.954 ↓ 4.9 97,000 1

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

36. 0.260 131.133 ↑ 23.7 177 1

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

  • Sort Key: data_stats_event.day, data_stats_event.group1, data_stats_event.group2
  • Sort Method: quicksort Memory: 41kB
37. 0.028 130.873 ↑ 23.7 177 1

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

38. 75.725 130.845 ↑ 23.7 177 1

MixedAggregate (cost=0.00..1,945.97 rows=4,187 width=232) (actual time=130.768..130.845 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: ()
39. 55.120 55.120 ↓ 4.9 97,000 1

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

Planning time : 1.582 ms
Execution time : 875.140 ms