explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TqB1

Settings
# exclusive inclusive rows x rows loops node
1. 1.530 2,426.152 ↓ 179.0 716 1

Sort (cost=168,251.26..168,251.27 rows=4 width=136) (actual time=2,426.116..2,426.152 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. 21.902 2,372.358 ↓ 14,089.0 14,089 1

Nested Loop (cost=1,000.00..168,251.07 rows=1 width=241) (actual time=1.080..2,372.358 rows=14,089 loops=1)

  • Join Filter: (((stats_session.campaign_id)::numeric >= '1'::numeric) OR (((_hyper_5_4_chunk."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
4. 15.229 2,308.189 ↓ 1,174.1 14,089 1

Nested Loop (cost=1,000.00..168,220.97 rows=12 width=104) (actual time=1.064..2,308.189 rows=14,089 loops=1)

5. 2.097 2,250.693 ↓ 123.6 14,089 1

Gather (cost=1,000.00..167,939.68 rows=114 width=76) (actual time=1.046..2,250.693 rows=14,089 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
6. 1.574 2,248.596 ↓ 293.5 14,089 1

Parallel Append (cost=0.00..166,928.28 rows=48 width=76) (actual time=0.689..2,248.596 rows=14,089 loops=1)

7. 2,247.021 2,247.021 ↓ 299.8 14,089 1

Parallel Seq Scan on _hyper_5_4_chunk (cost=0.00..166,928.04 rows=47 width=76) (actual time=0.687..2,247.021 rows=14,089 loops=1)

  • Filter: (((created_at)::timestamp without time zone >= '2019-04-01 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-04-02 23:59:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
  • Rows Removed by Filter: 4485910
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.001..0.001 rows=0 loops=1)

  • Filter: (((created_at)::timestamp without time zone >= '2019-04-01 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-04-02 23:59:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
9. 0.000 42.267 ↑ 2.0 1 14,089

Custom Scan (ConstraintAwareAppend) (cost=0.00..2.45 rows=2 width=44) (actual time=0.003..0.003 rows=1 loops=14,089)

  • Hypertable: stats_page_view
  • Chunks left after exclusion: 1
10. 0.000 42.267 ↑ 2.0 1 14,089

Append (cost=0.00..2.45 rows=2 width=44) (actual time=0.003..0.003 rows=1 loops=14,089)

11. 42.267 42.267 ↑ 1.0 1 14,089

Index Scan using "3_3_stats_page_view_id" on _hyper_3_3_chunk (cost=0.42..2.44 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)
  • Filter: (((created_at)::timestamp without time zone >= '2019-04-01 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-04-02 23:59:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
12. 0.000 42.267 ↑ 2.0 1 14,089

Custom Scan (ConstraintAwareAppend) (cost=0.00..2.46 rows=2 width=157) (actual time=0.003..0.003 rows=1 loops=14,089)

  • Hypertable: stats_session
  • Chunks left after exclusion: 1
13. 0.000 42.267 ↑ 2.0 1 14,089

Append (cost=0.00..2.46 rows=2 width=157) (actual time=0.002..0.003 rows=1 loops=14,089)

14. 42.267 42.267 ↑ 1.0 1 14,089

Index Scan using "2_2_stats_session_id" on _hyper_2_2_chunk (cost=0.41..2.45 rows=1 width=52) (actual time=0.002..0.003 rows=1 loops=14,089)

  • Index Cond: (id = stats_page_view.session_id)
  • Filter: (((created_at)::timestamp without time zone >= '2019-04-01 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-04-02 23:59:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
15. 0.122 2,424.622 ↓ 179.0 716 1

Subquery Scan on data_stats_event (cost=0.03..0.15 rows=4 width=136) (actual time=2,415.295..2,424.622 rows=716 loops=1)

16. 7.863 2,424.500 ↓ 179.0 716 1

GroupAggregate (cost=0.03..0.11 rows=4 width=232) (actual time=2,415.293..2,424.500 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: ()
17. 27.202 2,416.637 ↓ 14,089.0 14,089 1

Sort (cost=0.03..0.04 rows=1 width=148) (actual time=2,415.278..2,416.637 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
18. 2,389.435 2,389.435 ↓ 14,089.0 14,089 1

CTE Scan on data (cost=0.00..0.02 rows=1 width=148) (actual time=1.086..2,389.435 rows=14,089 loops=1)

Planning time : 0.783 ms
Execution time : 2,426.924 ms