explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3i4A

Settings
# exclusive inclusive rows x rows loops node
1. 0.093 78.811 ↑ 26.6 211 1

Sort (cost=30,663.86..30,677.89 rows=5,613 width=136) (actual time=78.800..78.811 rows=211 loops=1)

  • Sort Key: (COALESCE(data_session.group1, data_page_view.group1, data_event.group1)), (COALESCE(data_session.group2, data_page_view.group2, data_event.group2))
  • Sort Method: quicksort Memory: 54kB
2.          

CTE data

3. 11.585 24.480 ↑ 4.2 12,781 1

Hash Join (cost=1,370.38..17,346.89 rows=54,121 width=646) (actual time=1.858..24.480 rows=12,781 loops=1)

  • Hash Cond: (stats_event.page_view_id = stats_page_view.id)
4. 2.998 11.094 ↑ 1.1 12,781 1

Hash Left Join (cost=10.68..13,515.71 rows=13,719 width=592) (actual time=0.049..11.094 rows=12,781 loops=1)

  • Hash Cond: (stats_event.event_id = event.id)
5. 1.214 8.088 ↑ 1.1 12,781 1

Append (cost=0.00..13,468.25 rows=13,719 width=76) (actual time=0.025..8.088 rows=12,781 loops=1)

6. 0.006 0.006 ↓ 0.0 0 1

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

  • Filter: ((created_at >= '2019-04-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2019-04-02 23:59:59+00'::timestamp with time zone) AND (property_id = 2))
7. 6.868 6.868 ↑ 1.1 12,781 1

Index Scan using _hyper_5_4_chunk_idx_event_property_id_and_created_at on _hyper_5_4_chunk (cost=0.43..13,399.66 rows=13,718 width=76) (actual time=0.019..6.868 rows=12,781 loops=1)

  • Index Cond: ((property_id = 2) AND (created_at >= '2019-04-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2019-04-02 23:59:59+00'::timestamp with time zone))
8. 0.003 0.008 ↑ 10.0 3 1

Hash (cost=10.30..10.30 rows=30 width=520) (actual time=0.007..0.008 rows=3 loops=1)

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

Seq Scan on event (cost=0.00..10.30 rows=30 width=520) (actual time=0.004..0.005 rows=3 loops=1)

10. 0.311 1.801 ↓ 1.6 1,279 1

Hash (cost=1,349.84..1,349.84 rows=789 width=70) (actual time=1.801..1.801 rows=1,279 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 151kB
11. 0.470 1.490 ↓ 1.6 1,279 1

Hash Join (cost=129.41..1,349.84 rows=789 width=70) (actual time=0.214..1.490 rows=1,279 loops=1)

  • Hash Cond: (stats_page_view.session_id = stats_session.id)
12. 0.117 0.835 ↓ 1.0 1,279 1

Append (cost=0.00..1,207.95 rows=1,223 width=44) (actual time=0.022..0.835 rows=1,279 loops=1)

13. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((created_at >= '2019-04-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2019-04-02 23:59:59+00'::timestamp with time zone) AND (property_id = 2))
14. 0.716 0.716 ↓ 1.0 1,279 1

Index Scan using _hyper_3_3_chunk_idx_pageview_property_id_and_created_at on _hyper_3_3_chunk (cost=0.42..1,201.84 rows=1,222 width=44) (actual time=0.020..0.716 rows=1,279 loops=1)

  • Index Cond: ((property_id = 2) AND (created_at >= '2019-04-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2019-04-02 23:59:59+00'::timestamp with time zone))
15. 0.026 0.185 ↓ 1.0 130 1

Hash (cost=127.80..127.80 rows=129 width=42) (actual time=0.185..0.185 rows=130 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
16. 0.012 0.159 ↓ 1.0 130 1

Append (cost=0.00..127.80 rows=129 width=42) (actual time=0.014..0.159 rows=130 loops=1)

17. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=78) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((created_at >= '2019-04-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2019-04-02 23:59:59+00'::timestamp with time zone) AND (property_id = 2))
18. 0.146 0.146 ↓ 1.0 130 1

Index Scan using _hyper_2_2_chunk_idx_session_property_id_and_created_at on _hyper_2_2_chunk (cost=0.29..127.16 rows=128 width=42) (actual time=0.012..0.146 rows=130 loops=1)

  • Index Cond: ((property_id = 2) AND (created_at >= '2019-04-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2019-04-02 23:59:59+00'::timestamp with time zone))
19. 0.211 78.718 ↑ 26.6 211 1

Merge Full Join (cost=12,918.73..12,967.43 rows=5,613 width=136) (actual time=78.487..78.718 rows=211 loops=1)

  • Merge Cond: (((COALESCE(data_page_view.group1, data_session.group1)) = (COALESCE(data_event.group1))) AND ((COALESCE(data_page_view.group2, data_session.group2)) = (COALESCE(data_event.group2))))
20. 0.137 56.178 ↑ 3.6 209 1

Sort (cost=4,692.43..4,694.28 rows=742 width=176) (actual time=56.165..56.178 rows=209 loops=1)

  • Sort Key: (COALESCE(data_page_view.group1, data_session.group1)), (COALESCE(data_page_view.group2, data_session.group2))
  • Sort Method: quicksort Memory: 60kB
21. 0.163 56.041 ↑ 3.6 209 1

Merge Full Join (cost=4,645.78..4,657.05 rows=742 width=176) (actual time=55.859..56.041 rows=209 loops=1)

  • Merge Cond: (((COALESCE(data_session.group1)) = (COALESCE(data_page_view.group1))) AND ((COALESCE(data_session.group2)) = (COALESCE(data_page_view.group2))))
22. 0.074 43.600 ↑ 5.1 145 1

Sort (cost=2,483.00..2,484.85 rows=742 width=80) (actual time=43.589..43.600 rows=145 loops=1)

  • Sort Key: (COALESCE(data_session.group1)), (COALESCE(data_session.group2))
  • Sort Method: quicksort Memory: 45kB
23. 0.030 43.526 ↑ 5.1 145 1

Subquery Scan on data_session (cost=2,365.13..2,447.62 rows=742 width=80) (actual time=43.275..43.526 rows=145 loops=1)

24. 0.230 43.496 ↑ 5.1 145 1

GroupAggregate (cost=2,365.13..2,440.20 rows=742 width=144) (actual time=43.274..43.496 rows=145 loops=1)

  • Group Key: agg_session.group1, agg_session.group2
  • Group Key: agg_session.group1
  • Group Key: ()
25. 0.211 43.266 ↑ 42.0 129 1

Sort (cost=2,365.13..2,378.66 rows=5,412 width=96) (actual time=43.257..43.266 rows=129 loops=1)

  • Sort Key: agg_session.group1, agg_session.group2
  • Sort Method: quicksort Memory: 43kB
26. 0.021 43.055 ↑ 42.0 129 1

Subquery Scan on agg_session (cost=1,894.24..2,029.53 rows=5,412 width=96) (actual time=42.979..43.055 rows=129 loops=1)

27. 5.768 43.034 ↑ 42.0 129 1

HashAggregate (cost=1,894.24..1,975.41 rows=5,412 width=96) (actual time=42.979..43.034 rows=129 loops=1)

  • Group Key: data."stats_session.id", COALESCE((data."stats_session.created_at")::text, '-'::text), COALESCE((data."stats_session.geo_country_code")::text, '-'::text), data."stats_session.user_id
28. 37.266 37.266 ↑ 4.2 12,781 1

CTE Scan on data (cost=0.00..1,353.03 rows=54,121 width=96) (actual time=1.863..37.266 rows=12,781 loops=1)

29. 0.389 12.278 ↑ 3.6 205 1

Sort (cost=2,162.79..2,164.64 rows=742 width=96) (actual time=12.264..12.278 rows=205 loops=1)

  • Sort Key: (COALESCE(data_page_view.group1)), (COALESCE(data_page_view.group2))
  • Sort Method: quicksort Memory: 53kB
30. 0.040 11.889 ↑ 3.6 205 1

Subquery Scan on data_page_view (cost=1,894.24..2,127.41 rows=742 width=96) (actual time=11.732..11.889 rows=205 loops=1)

31. 0.865 11.849 ↑ 3.6 205 1

MixedAggregate (cost=1,894.24..2,119.99 rows=742 width=160) (actual time=11.730..11.849 rows=205 loops=1)

  • Hash Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.geo_country_code")::text, '-'::text))
  • Hash Key: (COALESCE((data_1."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
32. 5.294 10.984 ↑ 4.2 1,279 1

HashAggregate (cost=1,894.24..1,975.41 rows=5,412 width=84) (actual time=10.684..10.984 rows=1,279 loops=1)

  • Group Key: data_1."stats_page_view.id", COALESCE((data_1."stats_page_view.created_at")::text, '-'::text), COALESCE((data_1."stats_session.geo_country_code")::text, '-'::text), data_1."stats_page_view.runtime_active
33. 5.690 5.690 ↑ 4.2 12,781 1

CTE Scan on data data_1 (cost=0.00..1,353.03 rows=54,121 width=84) (actual time=0.002..5.690 rows=12,781 loops=1)

34. 0.120 22.329 ↑ 27.0 208 1

Sort (cost=8,226.30..8,240.33 rows=5,613 width=88) (actual time=22.315..22.329 rows=208 loops=1)

  • Sort Key: (COALESCE(data_event.group1)), (COALESCE(data_event.group2))
  • Sort Method: quicksort Memory: 54kB
35. 0.042 22.209 ↑ 27.0 208 1

Subquery Scan on data_event (cost=6,817.39..7,876.76 rows=5,613 width=88) (actual time=13.793..22.209 rows=208 loops=1)

36. 7.625 22.167 ↑ 27.0 208 1

GroupAggregate (cost=6,817.39..7,820.63 rows=5,613 width=152) (actual time=13.792..22.167 rows=208 loops=1)

  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.geo_country_code")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text))
  • Group Key: ()
37. 9.123 14.542 ↑ 4.2 12,781 1

Sort (cost=6,817.39..6,952.69 rows=54,121 width=84) (actual time=13.764..14.542 rows=12,781 loops=1)

  • Sort Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.geo_country_code")::text, '-'::text))
  • Sort Method: quicksort Memory: 1383kB
38. 5.419 5.419 ↑ 4.2 12,781 1

CTE Scan on data data_2 (cost=0.00..1,082.42 rows=54,121 width=84) (actual time=0.004..5.419 rows=12,781 loops=1)

Planning time : 1.261 ms
Execution time : 79.559 ms