explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7oRG

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 151.711 ↑ 27.0 208 1

Subquery Scan on data_event (cost=24,164.27..25,223.65 rows=5,613 width=88) (actual time=126.547..151.711 rows=208 loops=1)

2.          

CTE data

3. 52.696 73.623 ↑ 4.2 12,781 1

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

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

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

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

Append (cost=0.00..13,468.25 rows=13,719 width=76) (actual time=0.025..13.589 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.005..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. 12.229 12.229 ↑ 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..12.229 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.322 4.215 ↓ 1.6 1,279 1

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

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

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

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

Append (cost=0.00..1,207.95 rows=1,223 width=44) (actual time=0.024..3.198 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. 3.076 3.076 ↓ 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.022..3.076 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.028 0.237 ↓ 1.0 130 1

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

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

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

17. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=78) (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))
18. 0.195 0.195 ↓ 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.195 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. 28.673 151.662 ↑ 27.0 208 1

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

  • Group Key: (COALESCE((data."stats_event.created_at")::text, '-'::text)), (COALESCE((data."stats_session.geo_country_code")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_event.created_at")::text, '-'::text))
  • Group Key: ()
20. 17.642 122.989 ↑ 4.2 12,781 1

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

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

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