explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uccG

Settings
# exclusive inclusive rows x rows loops node
1. 7.118 1,505.156 ↑ 1.6 2,864 1

Sort (cost=266,194.69..266,206.06 rows=4,548 width=184) (actual time=1,505.003..1,505.156 rows=2,864 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, data_stats_page_view.day, (COALESCE(((data."stats_event.created_at")::text), '[TOTAL]'::text)))), (COALESCE(data_stats_session.group1, data_stats_page_view.group1, (COALESCE(data."stats_session.user_agent_family", '[TOTAL]'::character varying)))), (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. 1.995 1,498.038 ↑ 1.6 2,864 1

Hash Full Join (cost=261,030.87..265,918.38 rows=4,548 width=184) (actual time=1,494.950..1,498.038 rows=2,864 loops=1)

  • Hash Cond: (((COALESCE(((data."stats_event.created_at")::text), '[TOTAL]'::text)) = COALESCE(data_stats_session.day, data_stats_page_view.day)) AND (((COALESCE(data."stats_session.user_agent_family", '[TOTAL]'::character varying)))::text = (COALESCE(data_stats_session.group1, data_stats_page_view.group1))::text) AND ((COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group2, data_stats_page_view.group2)))
3. 72.719 504.427 ↑ 1.6 2,864 1

MixedAggregate (cost=85,563.68..89,348.29 rows=4,548 width=232) (actual time=503.324..504.427 rows=2,864 loops=1)

  • Hash Key: (data."stats_event.created_at")::text, data."stats_session.user_agent_family", COALESCE((data."stats_event.event_id")::text, '-'::text)
  • Hash Key: (data."stats_event.created_at")::text, data."stats_session.user_agent_family
  • Hash Key: (data."stats_event.created_at")::text
  • Group Key: ()
4. 32.979 431.708 ↓ 5.2 97,000 1

Subquery Scan on data (cost=85,563.68..87,905.14 rows=18,484 width=148) (actual time=320.736..431.708 rows=97,000 loops=1)

5. 45.985 398.729 ↓ 5.2 97,000 1

Gather Merge (cost=85,563.68..87,720.30 rows=18,484 width=160) (actual time=320.730..398.729 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
6. 157.298 352.744 ↓ 10.5 97,000 1

Sort (cost=84,563.66..84,586.76 rows=9,242 width=160) (actual time=319.606..352.744 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: 8656kB
7. 60.395 195.446 ↓ 10.5 97,000 1

Parallel Hash Join (cost=75,345.85..83,954.89 rows=9,242 width=160) (actual time=96.415..195.446 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))
8. 41.863 133.240 ↓ 28.6 97,000 1

Parallel Hash Join (cost=74,583.48..82,807.61 rows=3,395 width=92) (actual time=94.281..133.240 rows=97,000 loops=1)

  • Hash Cond: (_hyper_3_3_chunk.id = _hyper_5_4_chunk.page_view_id)
9. 1.017 6.220 ↓ 2.3 9,700 1

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

10. 5.202 5.202 ↓ 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=32) (actual time=0.024..5.202 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))
11. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=32) (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))
12. 26.588 85.157 ↓ 2.3 97,000 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2496kB
13. 9.407 58.569 ↓ 2.3 97,000 1

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

14. 49.162 49.162 ↓ 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.016..49.162 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))
15. 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-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))
16. 0.299 1.811 ↓ 2.4 970 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
17. 0.091 1.512 ↓ 2.4 970 1

Parallel Append (cost=0.00..757.26 rows=409 width=28) (actual time=0.036..1.512 rows=970 loops=1)

18. 1.420 1.420 ↓ 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=28) (actual time=0.034..1.420 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))
19. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=238) (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))
20. 1.115 991.616 ↓ 4.3 2,576 1

Hash (cost=175,456.67..175,456.67 rows=601 width=240) (actual time=991.616..991.616 rows=2,576 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 279kB
21. 2.051 990.501 ↓ 4.3 2,576 1

Merge Full Join (cost=175,444.64..175,456.67 rows=601 width=240) (actual time=988.195..990.501 rows=2,576 loops=1)

  • Merge Cond: (((COALESCE(data_stats_session.day)) = data_stats_page_view.day) AND (((COALESCE(data_stats_session.group1))::text) = (data_stats_page_view.group1)::text) AND ((COALESCE(data_stats_session.group2)) = data_stats_page_view.group2))
22. 0.714 440.957 ↓ 1.4 850 1

Sort (cost=87,043.48..87,044.99 rows=601 width=112) (actual time=440.883..440.957 rows=850 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day)), ((COALESCE(data_stats_session.group1))::text), (COALESCE(data_stats_session.group2))
  • Sort Method: quicksort Memory: 145kB
23. 0.172 440.243 ↓ 1.4 850 1

Subquery Scan on data_stats_session (cost=87,000.73..87,015.75 rows=601 width=112) (actual time=438.050..440.243 rows=850 loops=1)

24. 1.954 440.071 ↓ 1.4 850 1

GroupAggregate (cost=87,000.73..87,009.74 rows=601 width=208) (actual time=438.048..440.071 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: ()
25. 1.910 438.117 ↓ 4.8 970 1

Sort (cost=87,000.73..87,001.23 rows=200 width=128) (actual time=438.025..438.117 rows=970 loops=1)

  • Sort Key: agg_stats_session.day, agg_stats_session.group1, agg_stats_session.group2
  • Sort Method: quicksort Memory: 161kB
26. 0.189 436.207 ↓ 4.8 970 1

Subquery Scan on agg_stats_session (cost=84,418.57..86,993.08 rows=200 width=128) (actual time=329.589..436.207 rows=970 loops=1)

27. 8.399 436.018 ↓ 4.8 970 1

Unique (cost=84,418.57..86,991.08 rows=200 width=168) (actual time=329.588..436.018 rows=970 loops=1)

28. 47.889 427.619 ↓ 5.2 97,000 1

Subquery Scan on data_1 (cost=84,418.57..86,944.87 rows=18,484 width=168) (actual time=329.586..427.619 rows=97,000 loops=1)

29. 29.154 379.730 ↓ 5.2 97,000 1

Gather Merge (cost=84,418.57..86,575.19 rows=18,484 width=160) (actual time=329.579..379.730 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
30. 172.254 350.576 ↓ 10.5 97,000 1

Sort (cost=83,418.55..83,441.66 rows=9,242 width=160) (actual time=328.541..350.576 rows=97,000 loops=1)

  • Sort Key: _hyper_2_2_chunk_1.id, _hyper_3_3_chunk_1.id, _hyper_5_4_chunk_1.id
  • Sort Method: external merge Disk: 9912kB
31. 58.871 178.322 ↓ 10.5 97,000 1

Parallel Hash Join (cost=74,813.85..82,809.78 rows=9,242 width=160) (actual time=87.317..178.322 rows=97,000 loops=1)

  • Hash Cond: (_hyper_3_3_chunk_1.session_id = _hyper_2_2_chunk_1.id)
  • Join Filter: ((_hyper_2_2_chunk_1.campaign_id >= 1) OR (((_hyper_5_4_chunk_1."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
32. 26.462 118.137 ↓ 28.6 97,000 1

Parallel Hash Join (cost=74,051.48..81,685.61 rows=3,395 width=84) (actual time=85.563..118.137 rows=97,000 loops=1)

  • Hash Cond: (_hyper_3_3_chunk_1.id = _hyper_5_4_chunk_1.page_view_id)
33. 1.033 6.886 ↓ 2.3 9,700 1

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

34. 5.852 5.852 ↓ 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 _hyper_3_3_chunk_1 (cost=0.42..7,583.05 rows=5,956 width=32) (actual time=0.023..5.852 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))
35. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_page_view stats_page_view_1 (cost=0.00..0.00 rows=1 width=32) (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))
36. 25.064 84.789 ↓ 2.3 97,000 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 7872kB
37. 9.680 59.725 ↓ 2.3 97,000 1

Parallel Append (cost=0.00..73,528.20 rows=41,862 width=68) (actual time=0.017..59.725 rows=97,000 loops=1)

38. 50.044 50.044 ↓ 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 _hyper_5_4_chunk_1 (cost=0.43..73,318.89 rows=41,861 width=68) (actual time=0.016..50.044 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))
39. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_event stats_event_1 (cost=0.00..0.00 rows=1 width=68) (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))
40. 0.265 1.314 ↓ 2.4 970 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
41. 0.094 1.049 ↓ 2.4 970 1

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

42. 0.954 0.954 ↓ 1.7 970 1

Parallel Index Scan using _hyper_2_2_chunk_idx_session_property_id_and_created_at on _hyper_2_2_chunk _hyper_2_2_chunk_1 (cost=0.29..755.21 rows=576 width=52) (actual time=0.025..0.954 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))
43. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_session stats_session_1 (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))
44. 1.496 547.493 ↓ 4.2 2,540 1

Sort (cost=88,401.16..88,402.66 rows=601 width=128) (actual time=547.302..547.493 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
45. 0.439 545.997 ↓ 4.2 2,540 1

Subquery Scan on data_stats_page_view (cost=88,356.90..88,373.42 rows=601 width=128) (actual time=538.482..545.997 rows=2,540 loops=1)

46. 6.316 545.558 ↓ 4.2 2,540 1

GroupAggregate (cost=88,356.90..88,367.41 rows=601 width=224) (actual time=538.481..545.558 rows=2,540 loops=1)

  • Group Key: agg_stats_page_view.day, agg_stats_page_view.group1, agg_stats_page_view.group2
  • Group Key: agg_stats_page_view.day, agg_stats_page_view.group1
  • Group Key: agg_stats_page_view.day
  • Group Key: ()
47. 21.518 539.242 ↓ 48.5 9,700 1

Sort (cost=88,356.90..88,357.40 rows=200 width=100) (actual time=538.465..539.242 rows=9,700 loops=1)

  • Sort Key: agg_stats_page_view.day, agg_stats_page_view.group1, agg_stats_page_view.group2
  • Sort Method: quicksort Memory: 1230kB
48. 1.763 517.724 ↓ 48.5 9,700 1

Subquery Scan on agg_stats_page_view (cost=88,254.83..88,349.25 rows=200 width=100) (actual time=491.562..517.724 rows=9,700 loops=1)

49. 8.326 515.961 ↓ 48.5 9,700 1

Unique (cost=88,254.83..88,347.25 rows=200 width=124) (actual time=491.559..515.961 rows=9,700 loops=1)

50. 77.579 507.635 ↓ 5.2 97,000 1

Sort (cost=88,254.83..88,301.04 rows=18,484 width=124) (actual time=491.558..507.635 rows=97,000 loops=1)

  • Sort Key: data_2."stats_page_view.id
  • Sort Method: external merge Disk: 6408kB
51. 48.029 430.056 ↓ 5.2 97,000 1

Subquery Scan on data_2 (cost=84,418.57..86,944.87 rows=18,484 width=124) (actual time=332.241..430.056 rows=97,000 loops=1)

52. 26.390 382.027 ↓ 5.2 97,000 1

Gather Merge (cost=84,418.57..86,575.19 rows=18,484 width=160) (actual time=332.236..382.027 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
53. 171.025 355.637 ↓ 10.5 97,000 1

Sort (cost=83,418.55..83,441.66 rows=9,242 width=160) (actual time=331.271..355.637 rows=97,000 loops=1)

  • Sort Key: _hyper_2_2_chunk_2.id, _hyper_3_3_chunk_2.id, _hyper_5_4_chunk_2.id
  • Sort Method: external merge Disk: 9048kB
54. 63.125 184.612 ↓ 10.5 97,000 1

Parallel Hash Join (cost=74,813.85..82,809.78 rows=9,242 width=160) (actual time=86.276..184.612 rows=97,000 loops=1)

  • Hash Cond: (_hyper_3_3_chunk_2.session_id = _hyper_2_2_chunk_2.id)
  • Join Filter: ((_hyper_2_2_chunk_2.campaign_id >= 1) OR (((_hyper_5_4_chunk_2."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
55. 29.196 119.816 ↓ 28.6 97,000 1

Parallel Hash Join (cost=74,051.48..81,685.61 rows=3,395 width=96) (actual time=84.332..119.816 rows=97,000 loops=1)

  • Hash Cond: (_hyper_3_3_chunk_2.id = _hyper_5_4_chunk_2.page_view_id)
56. 0.924 7.252 ↓ 2.3 9,700 1

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

57. 6.327 6.327 ↓ 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 _hyper_3_3_chunk_2 (cost=0.42..7,583.05 rows=5,956 width=44) (actual time=0.023..6.327 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))
58. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_page_view stats_page_view_2 (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))
59. 25.071 83.368 ↓ 2.3 97,000 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 7872kB
60. 9.197 58.297 ↓ 2.3 97,000 1

Parallel Append (cost=0.00..73,528.20 rows=41,862 width=68) (actual time=0.020..58.297 rows=97,000 loops=1)

61. 49.099 49.099 ↓ 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 _hyper_5_4_chunk_2 (cost=0.43..73,318.89 rows=41,861 width=68) (actual time=0.019..49.099 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))
62. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_event stats_event_2 (cost=0.00..0.00 rows=1 width=68) (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))
63. 0.283 1.671 ↓ 2.4 970 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
64. 0.092 1.388 ↓ 2.4 970 1

Parallel Append (cost=0.00..757.26 rows=409 width=28) (actual time=0.035..1.388 rows=970 loops=1)

65. 1.295 1.295 ↓ 1.7 970 1

Parallel Index Scan using _hyper_2_2_chunk_idx_session_property_id_and_created_at on _hyper_2_2_chunk _hyper_2_2_chunk_2 (cost=0.29..755.21 rows=576 width=28) (actual time=0.033..1.295 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))
66. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on stats_session stats_session_2 (cost=0.00..0.00 rows=1 width=238) (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))
Planning time : 2.825 ms
Execution time : 1,537.266 ms