explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MxNe

Settings
# exclusive inclusive rows x rows loops node
1. 1.224 332,912.735 ↓ 172.8 691 1

Sort (cost=3,508.74..3,508.75 rows=4 width=184) (actual time=332,912.674..332,912.735 rows=691 loops=1)

  • Sort Key: (COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.day)) DESC, (COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.group1)), (COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text)), data_stats_event.group2))
  • Sort Method: quicksort Memory: 122kB
2.          

CTE data

3. 72,184.667 330,774.128 ↓ 90,000.0 90,000 1

Nested Loop (cost=0.29..3,507.89 rows=1 width=136) (actual time=0.089..330,774.128 rows=90,000 loops=1)

  • Join Filter: (stats_session.id = stats_page_view.session_id)
  • Rows Removed by Join Filter: 80910000
4. 35,117.888 96,589.461 ↓ 81,000,000.0 81,000,000 1

Nested Loop (cost=0.00..3,499.55 rows=1 width=124) (actual time=0.045..96,589.461 rows=81,000,000 loops=1)

  • Join Filter: (((stats_session.campaign_id)::numeric >= '1'::numeric) OR (((stats_event."values" ->> 'scrollTriggerValue'::text))::numeric <= '100'::numeric))
5. 1.573 1.573 ↓ 900.0 900 1

Seq Scan on stats_session (cost=0.00..49.50 rows=1 width=52) (actual time=0.021..1.573 rows=900 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))
6. 61,470.000 61,470.000 ↓ 45,000.0 90,000 900

Seq Scan on stats_event (cost=0.00..3,450.00 rows=2 width=76) (actual time=0.005..68.300 rows=90,000 loops=900)

  • 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))
7. 162,000.000 162,000.000 ↑ 1.0 1 81,000,000

Index Scan using stats_page_view_pkey on stats_page_view (cost=0.29..8.32 rows=1 width=44) (actual time=0.002..0.002 rows=1 loops=81,000,000)

  • Index Cond: (id = stats_event.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))
8. 0.822 332,911.511 ↓ 172.8 691 1

Hash Full Join (cost=0.55..0.81 rows=4 width=184) (actual time=332,852.844..332,911.511 rows=691 loops=1)

  • Hash Cond: ((COALESCE(data_stats_session.day, (COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text))) = data_stats_event.day) AND (COALESCE(data_stats_session.group1, (COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text))) = data_stats_event.group1) AND (COALESCE(data_stats_session.group2, (COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text))) = data_stats_event.group2))
9. 0.714 1,178.687 ↓ 172.8 691 1

Hash Full Join (cost=0.29..0.48 rows=4 width=240) (actual time=1,120.804..1,178.687 rows=691 loops=1)

  • Hash Cond: (((COALESCE((COALESCE((data."stats_page_view.created_at")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.day)) AND ((COALESCE((COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group1)) AND ((COALESCE((COALESCE((data."stats_event.event_id")::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.group2)))
10. 10.302 535.311 ↓ 172.8 691 1

GroupAggregate (cost=0.04..0.14 rows=4 width=224) (actual time=478.119..535.311 rows=691 loops=1)

  • Group Key: (COALESCE((data."stats_page_view.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_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text))
  • Group Key: ()
11. 31.220 525.009 ↓ 17,985.0 17,985 1

Group (cost=0.04..0.07 rows=1 width=124) (actual time=478.067..525.009 rows=17,985 loops=1)

  • Group Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_page_view.runtime_active", data."stats_page_view.id", data."stats_page_view.created_at
12. 414.997 493.789 ↓ 90,000.0 90,000 1

Sort (cost=0.04..0.05 rows=1 width=124) (actual time=478.064..493.789 rows=90,000 loops=1)

  • Sort Key: (COALESCE((data."stats_page_view.created_at")::text, '-'::text)), (COALESCE((data."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data."stats_event.event_id")::text, '-'::text)), data."stats_page_view.runtime_active", data."stats_page_view.id", data."stats_page_view.created_at
  • Sort Method: external sort Disk: 6608kB
13. 78.792 78.792 ↓ 90,000.0 90,000 1

CTE Scan on data (cost=0.00..0.03 rows=1 width=124) (actual time=0.016..78.792 rows=90,000 loops=1)

14. 0.533 642.662 ↓ 171.2 685 1

Hash (cost=0.18..0.18 rows=4 width=112) (actual time=642.662..642.662 rows=685 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 66kB
15. 0.156 642.129 ↓ 171.2 685 1

Subquery Scan on data_stats_session (cost=0.04..0.18 rows=4 width=112) (actual time=582.831..642.129 rows=685 loops=1)

16. 4.833 641.973 ↓ 171.2 685 1

GroupAggregate (cost=0.04..0.14 rows=4 width=208) (actual time=582.829..641.973 rows=685 loops=1)

  • Group Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_1."stats_event.event_id")::text, '-'::text))
  • Group Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text))
  • Group Key: ()
17. 37.705 637.140 ↓ 1,800.0 1,800 1

Group (cost=0.04..0.07 rows=1 width=354) (actual time=582.764..637.140 rows=1,800 loops=1)

  • Group Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_1."stats_event.event_id")::text, '-'::text)), data_1."stats_session.user_agent_family", data_1."stats_session.id", data_1."stats_session.user_id", data_1."stats_session.created_at
18. 519.902 599.435 ↓ 90,000.0 90,000 1

Sort (cost=0.04..0.05 rows=1 width=354) (actual time=582.760..599.435 rows=90,000 loops=1)

  • Sort Key: (COALESCE((data_1."stats_session.created_at")::text, '-'::text)), (COALESCE((data_1."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_1."stats_event.event_id")::text, '-'::text)), data_1."stats_session.user_agent_family", data_1."stats_session.id", data_1."stats_session.user_id", data_1."stats_session.created_at
  • Sort Method: external sort Disk: 8400kB
19. 79.533 79.533 ↓ 90,000.0 90,000 1

CTE Scan on data data_1 (cost=0.00..0.03 rows=1 width=354) (actual time=2.149..79.533 rows=90,000 loops=1)

20. 0.368 331,732.002 ↓ 172.8 691 1

Hash (cost=0.20..0.20 rows=4 width=136) (actual time=331,732.002..331,732.002 rows=691 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
21. 0.151 331,731.634 ↓ 172.8 691 1

Subquery Scan on data_stats_event (cost=0.04..0.20 rows=4 width=136) (actual time=331,622.382..331,731.634 rows=691 loops=1)

22. 53.780 331,731.483 ↓ 172.8 691 1

GroupAggregate (cost=0.04..0.16 rows=4 width=232) (actual time=331,622.381..331,731.483 rows=691 loops=1)

  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_2."stats_event.event_id")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text))
  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text))
  • Group Key: ()
23. 37.410 331,677.703 ↓ 90,000.0 90,000 1

Group (cost=0.04..0.07 rows=1 width=156) (actual time=331,622.330..331,677.703 rows=90,000 loops=1)

  • Group Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_2."stats_event.event_id")::text, '-'::text)), data_2."stats_event.event_id", data_2."stats_event.values->>'scrollTriggerValue'", data_2."stats_event.id", data_2."stats_event.created_at
24. 588.829 331,640.293 ↓ 90,000.0 90,000 1

Sort (cost=0.04..0.05 rows=1 width=156) (actual time=331,622.326..331,640.293 rows=90,000 loops=1)

  • Sort Key: (COALESCE((data_2."stats_event.created_at")::text, '-'::text)), (COALESCE((data_2."stats_session.user_agent_family")::text, '-'::text)), (COALESCE((data_2."stats_event.event_id")::text, '-'::text)), data_2."stats_event.event_id", data_2."stats_event.values->>'scrollTriggerValue'", data_2."stats_event.id", data_2."stats_event.created_at
  • Sort Method: external sort Disk: 6608kB
25. 331,051.464 331,051.464 ↓ 90,000.0 90,000 1

CTE Scan on data data_2 (cost=0.00..0.03 rows=1 width=156) (actual time=0.103..331,051.464 rows=90,000 loops=1)