explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4hDS

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 1,145.086 ↑ 37.2 65 1

Sort (cost=94,707.18..94,713.23 rows=2,419 width=152) (actual time=1,145.082..1,145.086 rows=65 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, (COALESCE(((data."stats_page_view.created_at")::text), '[TOTAL]'::text)), data_stats_event.day)), (COALESCE(data_stats_session.group1, (COALESCE(data."stats_session.user_agent_family", '[TOTAL]'::character varying)), data_stats_event.group1))
  • Sort Method: quicksort Memory: 34kB
2.          

CTE data

3. 35.103 541.589 ↓ 4.4 97,000 1

Gather (cost=76,345.85..87,237.10 rows=22,180 width=160) (actual time=333.939..541.589 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 117.038 506.486 ↓ 10.5 97,000 1

Parallel Hash Join (cost=75,345.85..84,019.10 rows=9,242 width=160) (actual time=333.050..506.486 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. 60.636 387.788 ↓ 28.6 97,000 1

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

  • Hash Cond: (_hyper_3_3_chunk.id = _hyper_5_4_chunk.page_view_id)
6. 1.041 26.113 ↓ 2.3 9,700 1

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

7. 25.071 25.071 ↓ 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.028..25.071 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))
8. 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))
9. 109.174 301.039 ↓ 2.3 97,000 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2496kB
10. 21.947 191.865 ↓ 2.3 97,000 1

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

11. 169.917 169.917 ↓ 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.020..169.917 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))
12. 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))
13. 0.313 1.660 ↓ 2.4 970 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
14. 0.088 1.347 ↓ 2.4 970 1

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

15. 1.258 1.258 ↓ 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.030..1.258 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))
16. 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))
17. 0.078 1,145.047 ↑ 37.2 65 1

Merge Full Join (cost=7,312.74..7,334.13 rows=2,419 width=152) (actual time=1,144.965..1,145.047 rows=65 loops=1)

  • Merge Cond: (((COALESCE(data_stats_session.day, (COALESCE(((data."stats_page_view.created_at")::text), '[TOTAL]'::text)))) = data_stats_event.day) AND (((COALESCE(data_stats_session.group1, (COALESCE(data."stats_session.user_agent_family", '[TOTAL]'::character varying))))::text) = (data_stats_event.group1)::text))
18. 0.109 849.349 ↑ 6.2 65 1

Sort (cost=4,634.10..4,635.11 rows=401 width=176) (actual time=849.341..849.349 rows=65 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, (COALESCE(((data."stats_page_view.created_at")::text), '[TOTAL]'::text)))), ((COALESCE(data_stats_session.group1, (COALESCE(data."stats_session.user_agent_family", '[TOTAL]'::character varying))))::text)
  • Sort Method: quicksort Memory: 41kB
19. 0.048 849.240 ↑ 6.2 65 1

Hash Full Join (cost=4,448.20..4,616.77 rows=401 width=176) (actual time=849.174..849.240 rows=65 loops=1)

  • Hash Cond: (((COALESCE(((data."stats_page_view.created_at")::text), '[TOTAL]'::text)) = COALESCE(data_stats_session.day)) AND (((COALESCE(data."stats_session.user_agent_family", '[TOTAL]'::character varying)))::text = (COALESCE(data_stats_session.group1))::text))
20. 5.835 122.157 ↑ 6.2 65 1

MixedAggregate (cost=2,155.56..2,277.97 rows=401 width=160) (actual time=122.129..122.157 rows=65 loops=1)

  • Hash Key: ((data."stats_page_view.created_at")::text), data."stats_session.user_agent_family
  • Hash Key: ((data."stats_page_view.created_at")::text)
  • Group Key: ()
21. 8.251 116.322 ↓ 48.5 9,700 1

Unique (cost=2,155.56..2,266.46 rows=200 width=92) (actual time=92.103..116.322 rows=9,700 loops=1)

22. 62.707 108.071 ↓ 4.4 97,000 1

Sort (cost=2,155.56..2,211.01 rows=22,180 width=92) (actual time=92.102..108.071 rows=97,000 loops=1)

  • Sort Key: data."stats_page_view.id
  • Sort Method: external merge Disk: 6032kB
23. 45.364 45.364 ↓ 4.4 97,000 1

CTE Scan on data (cost=0.00..554.50 rows=22,180 width=92) (actual time=0.039..45.364 rows=97,000 loops=1)

24. 0.030 727.035 ↑ 6.2 65 1

Hash (cost=2,286.62..2,286.62 rows=401 width=80) (actual time=727.035..727.035 rows=65 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
25. 0.012 727.005 ↑ 6.2 65 1

Subquery Scan on data_stats_session (cost=2,276.10..2,286.62 rows=401 width=80) (actual time=726.102..727.005 rows=65 loops=1)

26. 0.860 726.993 ↑ 6.2 65 1

GroupAggregate (cost=2,276.10..2,282.61 rows=401 width=144) (actual time=726.101..726.993 rows=65 loops=1)

  • Group Key: agg_stats_session.day, agg_stats_session.group1
  • Group Key: agg_stats_session.day
  • Group Key: ()
27. 0.932 726.133 ↓ 4.8 970 1

Sort (cost=2,276.10..2,276.60 rows=200 width=96) (actual time=726.077..726.133 rows=970 loops=1)

  • Sort Key: agg_stats_session.day, agg_stats_session.group1
  • Sort Method: quicksort Memory: 161kB
28. 0.182 725.201 ↓ 4.8 970 1

Subquery Scan on agg_stats_session (cost=2,155.56..2,268.46 rows=200 width=96) (actual time=701.472..725.201 rows=970 loops=1)

29. 7.024 725.019 ↓ 4.8 970 1

Unique (cost=2,155.56..2,266.46 rows=200 width=136) (actual time=701.470..725.019 rows=970 loops=1)

30. 71.910 717.995 ↓ 4.4 97,000 1

Sort (cost=2,155.56..2,211.01 rows=22,180 width=136) (actual time=701.469..717.995 rows=97,000 loops=1)

  • Sort Key: data_1."stats_session.id
  • Sort Method: external merge Disk: 7000kB
31. 646.085 646.085 ↓ 4.4 97,000 1

CTE Scan on data data_1 (cost=0.00..554.50 rows=22,180 width=136) (actual time=333.946..646.085 rows=97,000 loops=1)

32. 0.070 295.620 ↑ 37.2 65 1

Sort (cost=2,678.64..2,684.69 rows=2,419 width=104) (actual time=295.616..295.620 rows=65 loops=1)

  • Sort Key: data_stats_event.day, data_stats_event.group1
  • Sort Method: quicksort Memory: 30kB
33. 0.027 295.550 ↑ 37.2 65 1

Subquery Scan on data_stats_event (cost=2,044.66..2,542.69 rows=2,419 width=104) (actual time=147.009..295.550 rows=65 loops=1)

34. 117.978 295.523 ↑ 37.2 65 1

GroupAggregate (cost=2,044.66..2,518.50 rows=2,419 width=168) (actual time=147.008..295.523 rows=65 loops=1)

  • Group Key: ((data_2."stats_event.created_at")::text), data_2."stats_session.user_agent_family
  • Group Key: ((data_2."stats_event.created_at")::text)
  • Group Key: ()
35. 116.771 177.545 ↓ 4.4 97,000 1

Sort (cost=2,044.66..2,100.11 rows=22,180 width=116) (actual time=146.597..177.545 rows=97,000 loops=1)

  • Sort Key: ((data_2."stats_event.created_at")::text), data_2."stats_session.user_agent_family
  • Sort Method: external merge Disk: 6008kB
36. 60.774 60.774 ↓ 4.4 97,000 1

CTE Scan on data data_2 (cost=0.00..443.60 rows=22,180 width=116) (actual time=0.018..60.774 rows=97,000 loops=1)

Planning time : 5.241 ms
Execution time : 1,178.446 ms