explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UbYT

Settings
# exclusive inclusive rows x rows loops node
1. 1.578 553.845 ↓ 1.2 716 1

Sort (cost=67,142.24..67,143.74 rows=601 width=136) (actual time=553.810..553.845 rows=716 loops=1)

  • Sort Key: (COALESCE(data_stats_event.day)) DESC, (COALESCE(data_stats_event.group1)), (COALESCE(data_stats_event.group2))
  • Sort Method: quicksort Memory: 87kB
2.          

CTE data

3. 2.733 496.256 ↓ 36.1 14,089 1

Gather (cost=1,724.83..67,064.69 rows=390 width=143) (actual time=5.131..496.256 rows=14,089 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 20.828 493.523 ↓ 87.0 14,089 1

Parallel Hash Join (cost=724.83..66,025.69 rows=162 width=143) (actual time=4.472..493.523 rows=14,089 loops=1)

  • Hash Cond: (stats_page_view.session_id = _hyper_2_2_chunk.id)
  • Join Filter: (((_hyper_2_2_chunk.campaign_id)::numeric >= '1'::numeric) OR (((_hyper_5_4_chunk."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
5. 71.706 468.475 ↓ 7.7 14,089 1

Nested Loop (cost=0.00..65,289.20 rows=1,824 width=104) (actual time=0.072..468.475 rows=14,089 loops=1)

6. 1.482 396.764 ↓ 11.3 14,089 1

Parallel Append (cost=0.00..62,746.64 rows=1,246 width=76) (actual time=0.049..396.764 rows=14,089 loops=1)

7. 395.281 395.281 ↓ 11.3 14,089 1

Parallel Index Scan using _hyper_5_4_chunk_stats_event_created_at_idx on _hyper_5_4_chunk (cost=0.43..62,740.41 rows=1,245 width=76) (actual time=0.048..395.281 rows=14,089 loops=1)

  • Index Cond: ((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))
  • Filter: ((property_id)::numeric = '1'::numeric)
  • Rows Removed by Filter: 579581
8. 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.000..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)::numeric = '1'::numeric))
9. 0.002 0.005 ↑ 2.0 1 14,089

Append (cost=0.00..2.02 rows=2 width=44) (actual time=0.004..0.005 rows=1 loops=14,089)

10. 0.000 0.000 ↓ 0.0 0 14,089

Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=44) (actual time=0.000..0.000 rows=0 loops=14,089)

  • 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 (_hyper_5_4_chunk.page_view_id = id) AND ((property_id)::numeric = '1'::numeric))
11. 0.003 0.003 ↑ 1.0 1 14,089

Index Scan using "3_3_stats_page_view_id" on _hyper_3_3_chunk (cost=0.42..2.01 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=14,089)

  • Index Cond: ((id = _hyper_5_4_chunk.page_view_id) 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))
  • Filter: ((property_id)::numeric = '1'::numeric)
12. 0.065 4.220 ↓ 10.3 144 1

Parallel Hash (cost=724.66..724.66 rows=14 width=59) (actual time=4.219..4.220 rows=144 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
13. 0.016 4.155 ↓ 10.3 144 1

Parallel Append (cost=0.00..724.66 rows=14 width=59) (actual time=0.109..4.155 rows=144 loops=1)

14. 4.138 4.138 ↓ 8.0 144 1

Parallel Index Scan using _hyper_2_2_chunk_stats_session_created_at_idx on _hyper_2_2_chunk (cost=0.29..724.59 rows=18 width=52) (actual time=0.108..4.138 rows=144 loops=1)

  • Index Cond: ((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))
  • Filter: ((property_id)::numeric = '1'::numeric)
  • Rows Removed by Filter: 5918
15. 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-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)::numeric = '1'::numeric))
16. 0.122 552.267 ↓ 1.2 716 1

Subquery Scan on data_stats_event (cost=24.58..49.81 rows=601 width=136) (actual time=542.555..552.267 rows=716 loops=1)

17. 7.685 552.145 ↓ 1.2 716 1

GroupAggregate (cost=24.58..43.80 rows=601 width=232) (actual time=542.554..552.145 rows=716 loops=1)

  • Group Key: (COALESCE((data."stats_event.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_event.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_event.created_at")::text, '-'::text))
  • Group Key: ()
18. 29.470 544.460 ↓ 36.1 14,089 1

Sort (cost=24.58..25.56 rows=390 width=148) (actual time=542.537..544.460 rows=14,089 loops=1)

  • Sort Key: (COALESCE((data."stats_event.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text))
  • Sort Method: quicksort Memory: 2355kB
19. 514.990 514.990 ↓ 36.1 14,089 1

CTE Scan on data (cost=0.00..7.80 rows=390 width=148) (actual time=5.137..514.990 rows=14,089 loops=1)

Planning time : 1.041 ms
Execution time : 554.607 ms