explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Iz4S

Settings
# exclusive inclusive rows x rows loops node
1. 1.318 319,225.089 ↓ 172.8 691 1

Sort (cost=3,508.74..3,508.75 rows=4 width=184) (actual time=319,225.028..319,225.089 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. 63,695.229 317,118.744 ↓ 90,000.0 90,000 1

Nested Loop (cost=0.29..3,507.89 rows=1 width=136) (actual time=0.087..317,118.744 rows=90,000 loops=1)

  • Join Filter: (stats_session.id = stats_page_view.session_id)
  • Rows Removed by Join Filter: 80910000
4. 32,960.443 91,423.515 ↓ 81,000,000.0 81,000,000 1

Nested Loop (cost=0.00..3,499.55 rows=1 width=124) (actual time=0.043..91,423.515 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.772 1.772 ↓ 900.0 900 1

Seq Scan on stats_session (cost=0.00..49.50 rows=1 width=52) (actual time=0.020..1.772 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. 58,461.300 58,461.300 ↓ 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..64.957 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.798 319,223.771 ↓ 172.8 691 1

Hash Full Join (cost=0.55..0.81 rows=4 width=184) (actual time=319,173.899..319,223.771 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.779 1,123.670 ↓ 172.8 691 1

Hash Full Join (cost=0.29..0.48 rows=4 width=240) (actual time=1,074.561..1,123.670 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. 8.737 488.993 ↓ 172.8 691 1

GroupAggregate (cost=0.04..0.14 rows=4 width=224) (actual time=440.630..488.993 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. 26.313 480.256 ↓ 17,985.0 17,985 1

Group (cost=0.04..0.07 rows=1 width=124) (actual time=440.596..480.256 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. 383.805 453.943 ↓ 90,000.0 90,000 1

Sort (cost=0.04..0.05 rows=1 width=124) (actual time=440.593..453.943 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. 70.138 70.138 ↓ 90,000.0 90,000 1

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

14. 0.495 633.898 ↓ 171.2 685 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 66kB
15. 0.126 633.403 ↓ 171.2 685 1

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

16. 4.572 633.277 ↓ 171.2 685 1

GroupAggregate (cost=0.04..0.14 rows=4 width=208) (actual time=584.472..633.277 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. 30.445 628.705 ↓ 1,800.0 1,800 1

Group (cost=0.04..0.07 rows=1 width=354) (actual time=584.425..628.705 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. 511.839 598.260 ↓ 90,000.0 90,000 1

Sort (cost=0.04..0.05 rows=1 width=354) (actual time=584.423..598.260 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. 86.421 86.421 ↓ 90,000.0 90,000 1

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

20. 0.529 318,099.303 ↓ 172.8 691 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
21. 0.176 318,098.774 ↓ 172.8 691 1

Subquery Scan on data_stats_event (cost=0.04..0.20 rows=4 width=136) (actual time=317,993.178..318,098.774 rows=691 loops=1)

22. 51.136 318,098.598 ↓ 172.8 691 1

GroupAggregate (cost=0.04..0.16 rows=4 width=232) (actual time=317,993.177..318,098.598 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. 35.670 318,047.462 ↓ 90,000.0 90,000 1

Group (cost=0.04..0.07 rows=1 width=156) (actual time=317,993.109..318,047.462 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. 599.226 318,011.792 ↓ 90,000.0 90,000 1

Sort (cost=0.04..0.05 rows=1 width=156) (actual time=317,993.106..318,011.792 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. 317,412.566 317,412.566 ↓ 90,000.0 90,000 1

CTE Scan on data data_2 (cost=0.00..0.03 rows=1 width=156) (actual time=0.100..317,412.566 rows=90,000 loops=1)

Planning time : 1.212 ms
Execution time : 319,233.958 ms