explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0WQS

Settings
# exclusive inclusive rows x rows loops node
1. 6.632 5,063.837 ↓ 716.0 2,864 1

Sort (cost=168,251.31..168,251.32 rows=4 width=136) (actual time=5,063.679..5,063.837 rows=2,864 loops=1)

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

CTE data

3. 164.997 4,218.880 ↓ 97,000.0 97,000 1

Nested Loop (cost=1,000.00..168,251.07 rows=1 width=241) (actual time=1.938..4,218.880 rows=97,000 loops=1)

  • Join Filter: (((stats_session.campaign_id)::numeric >= '1'::numeric) OR (((_hyper_5_4_chunk."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
4. 79.549 3,665.883 ↓ 8,083.3 97,000 1

Nested Loop (cost=1,000.00..168,220.97 rows=12 width=104) (actual time=1.920..3,665.883 rows=97,000 loops=1)

5. 13.464 3,198.334 ↓ 850.9 97,000 1

Gather (cost=1,000.00..167,939.68 rows=114 width=76) (actual time=1.898..3,198.334 rows=97,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
6. 11.263 3,184.870 ↓ 2,020.8 97,000 1

Parallel Append (cost=0.00..166,928.28 rows=48 width=76) (actual time=1.541..3,184.870 rows=97,000 loops=1)

7. 3,173.607 3,173.607 ↓ 2,063.8 97,000 1

Parallel Seq Scan on _hyper_5_4_chunk (cost=0.00..166,928.04 rows=47 width=76) (actual time=1.540..3,173.607 rows=97,000 loops=1)

  • Filter: (((created_at)::timestamp without time zone >= '2019-03-11 08:22:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-05-10 08:22:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
  • Rows Removed by Filter: 4402999
8. 0.000 0.000 ↓ 0.0 0 1

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

  • Filter: (((created_at)::timestamp without time zone >= '2019-03-11 08:22:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-05-10 08:22:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
9. 0.000 388.000 ↑ 2.0 1 97,000

Custom Scan (ConstraintAwareAppend) (cost=0.00..2.45 rows=2 width=44) (actual time=0.004..0.004 rows=1 loops=97,000)

  • Hypertable: stats_page_view
  • Chunks left after exclusion: 1
10. 97.000 388.000 ↑ 2.0 1 97,000

Append (cost=0.00..2.45 rows=2 width=44) (actual time=0.003..0.004 rows=1 loops=97,000)

11. 291.000 291.000 ↑ 1.0 1 97,000

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=97,000)

  • Index Cond: (id = _hyper_5_4_chunk.page_view_id)
  • Filter: (((created_at)::timestamp without time zone >= '2019-03-11 08:22:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-05-10 08:22:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
12. 97.000 388.000 ↑ 2.0 1 97,000

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

  • Hypertable: stats_session
  • Chunks left after exclusion: 1
13. 0.000 291.000 ↑ 2.0 1 97,000

Append (cost=0.00..2.46 rows=2 width=157) (actual time=0.003..0.003 rows=1 loops=97,000)

14. 291.000 291.000 ↑ 1.0 1 97,000

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.003..0.003 rows=1 loops=97,000)

  • Index Cond: (id = stats_page_view.session_id)
  • Filter: (((created_at)::timestamp without time zone >= '2019-03-11 08:22:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2019-05-10 08:22:59'::timestamp without time zone) AND ((property_id)::numeric = '1'::numeric))
15. 0.570 5,057.205 ↓ 716.0 2,864 1

Subquery Scan on data_stats_event (cost=0.04..0.20 rows=4 width=136) (actual time=4,884.374..5,057.205 rows=2,864 loops=1)

16. 57.085 5,056.635 ↓ 716.0 2,864 1

GroupAggregate (cost=0.04..0.16 rows=4 width=232) (actual time=4,884.373..5,056.635 rows=2,864 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. 37.173 4,999.550 ↓ 97,000.0 97,000 1

Group (cost=0.04..0.07 rows=1 width=156) (actual time=4,884.352..4,999.550 rows=97,000 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)), data."stats_event.event_id", data."stats_event.values->>'scrollTriggerValue'", data."stats_event.id", data."stats_event.created_at
18. 556.709 4,962.377 ↓ 97,000.0 97,000 1

Sort (cost=0.04..0.05 rows=1 width=156) (actual time=4,884.348..4,962.377 rows=97,000 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)), data."stats_event.event_id", data."stats_event.values->>'scrollTriggerValue'", data."stats_event.id", data."stats_event.created_at
  • Sort Method: external merge Disk: 7264kB
19. 4,405.668 4,405.668 ↓ 97,000.0 97,000 1

CTE Scan on data (cost=0.00..0.03 rows=1 width=156) (actual time=1.945..4,405.668 rows=97,000 loops=1)