explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8jfu

Settings
# exclusive inclusive rows x rows loops node
1. 4,351.079 30,071.143 ↑ 2.3 17,500 1

GroupAggregate (cost=102,269,052,221,231.78..109,967,127,282,288.53 rows=40,000 width=100) (actual time=24,099.015..30,071.143 rows=17,500 loops=1)

  • Group Key: subquery."stats_session.user_agent_family", subquery."stats_event.event_id
2. 11,680.675 25,720.064 ↑ 38,015,193.9 4,499,999 1

Sort (cost=102,269,052,221,231.78..102,696,723,057,912.72 rows=171,068,334,672,372 width=116) (actual time=24,098.694..25,720.064 rows=4,499,999 loops=1)

  • Sort Key: subquery."stats_session.user_agent_family", subquery."stats_event.event_id" DESC
  • Sort Method: external merge Disk: 331024kB
3. 808.825 14,039.389 ↑ 38,015,193.9 4,499,999 1

Subquery Scan on subquery (cost=1,955,072,666.55..7,700,068,116,395.41 rows=171,068,334,672,372 width=116) (actual time=6,700.820..14,039.389 rows=4,499,999 loops=1)

4. 3,181.012 13,230.564 ↑ 38,015,193.9 4,499,999 1

WindowAgg (cost=1,955,072,666.55..5,989,384,769,671.69 rows=171,068,334,672,372 width=132) (actual time=6,700.819..13,230.564 rows=4,499,999 loops=1)

5. 1,313.248 10,049.552 ↑ 38,015,193.9 4,499,999 1

Merge Left Join (cost=1,955,072,666.55..2,568,018,076,224.25 rows=171,068,334,672,372 width=92) (actual time=6,700.797..10,049.552 rows=4,499,999 loops=1)

  • Merge Cond: (stats_page_view.id = stats_event.page_view_id)
6. 445.753 1,228.961 ↑ 16,876.5 450,000 1

Sort (cost=1,954,176,026.99..1,973,162,131.68 rows=7,594,441,876 width=40) (actual time=1,077.001..1,228.961 rows=450,000 loops=1)

  • Sort Key: stats_page_view.id
  • Sort Method: external merge Disk: 22840kB
7. 172.416 783.208 ↑ 16,876.5 450,000 1

Merge Right Join (cost=597,804.27..114,525,120.67 rows=7,594,441,876 width=40) (actual time=458.063..783.208 rows=450,000 loops=1)

  • Merge Cond: (stats_page_view.session_id = stats_session.id)
8. 293.800 451.697 ↑ 1.0 450,000 1

Sort (cost=69,485.13..70,610.13 rows=450,001 width=36) (actual time=346.038..451.697 rows=450,000 loops=1)

  • Sort Key: stats_page_view.session_id
  • Sort Method: external merge Disk: 20288kB
9. 46.429 157.897 ↑ 1.0 450,000 1

Append (cost=0.00..20,199.01 rows=450,001 width=36) (actual time=0.014..157.897 rows=450,000 loops=1)

10. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on stats_page_view (cost=0.00..0.00 rows=1 width=36) (actual time=0.006..0.006 rows=0 loops=1)

11. 111.462 111.462 ↑ 1.0 450,000 1

Seq Scan on _hyper_3_3_chunk (cost=0.00..17,949.00 rows=450,000 width=36) (actual time=0.008..111.462 rows=450,000 loops=1)

12. 37.840 159.095 ↑ 7.5 449,991 1

Materialize (cost=528,319.15..545,195.65 rows=3,375,300 width=36) (actual time=112.018..159.095 rows=449,991 loops=1)

13. 34.222 121.255 ↑ 75.0 45,000 1

Sort (cost=528,319.15..536,757.40 rows=3,375,300 width=36) (actual time=112.015..121.255 rows=45,000 loops=1)

  • Sort Key: stats_session.id
  • Sort Method: external merge Disk: 2112kB
14. 12.794 87.033 ↑ 75.0 45,000 1

Merge Left Join (cost=5,607.48..56,845.51 rows=3,375,300 width=36) (actual time=56.192..87.033 rows=45,000 loops=1)

  • Merge Cond: (stats_user.id = stats_session.user_id)
15. 1.746 9.984 ↑ 1.0 15,000 1

Merge Append (cost=0.42..571.45 rows=15,001 width=24) (actual time=0.025..9.984 rows=15,000 loops=1)

  • Sort Key: stats_user.id
16. 0.009 0.009 ↓ 0.0 0 1

Index Only Scan using stats_user_id on stats_user (cost=0.12..2.14 rows=1 width=24) (actual time=0.009..0.009 rows=0 loops=1)

  • Heap Fetches: 0
17. 8.229 8.229 ↑ 1.0 15,000 1

Index Only Scan using "1_1_stats_user_id" on _hyper_4_1_chunk (cost=0.29..419.29 rows=15,000 width=24) (actual time=0.015..8.229 rows=15,000 loops=1)

  • Heap Fetches: 15000
18. 45.052 64.255 ↑ 1.0 45,000 1

Sort (cost=5,607.06..5,719.56 rows=45,001 width=44) (actual time=56.159..64.255 rows=45,000 loops=1)

  • Sort Key: stats_session.user_id
  • Sort Method: external sort Disk: 2640kB
19. 4.739 19.203 ↑ 1.0 45,000 1

Append (cost=0.00..2,129.01 rows=45,001 width=44) (actual time=0.011..19.203 rows=45,000 loops=1)

20. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on stats_session (cost=0.00..0.00 rows=1 width=254) (actual time=0.003..0.003 rows=0 loops=1)

21. 14.461 14.461 ↑ 1.0 45,000 1

Seq Scan on _hyper_2_2_chunk (cost=0.00..1,904.00 rows=45,000 width=44) (actual time=0.007..14.461 rows=45,000 loops=1)

22. 651.720 7,507.343 ↑ 1.0 4,499,999 1

Materialize (cost=896,639.56..919,165.03 rows=4,505,093 width=68) (actual time=5,623.789..7,507.343 rows=4,499,999 loops=1)

23. 4,823.022 6,855.623 ↑ 1.0 4,499,999 1

Sort (cost=896,639.56..907,902.30 rows=4,505,093 width=68) (actual time=5,623.785..6,855.623 rows=4,499,999 loops=1)

  • Sort Key: stats_event.page_view_id
  • Sort Method: external merge Disk: 202600kB
24. 467.762 2,032.601 ↑ 1.0 4,499,999 1

Append (cost=0.00..187,576.38 rows=4,505,093 width=68) (actual time=0.031..2,032.601 rows=4,499,999 loops=1)

25. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on stats_event (cost=0.00..0.00 rows=1 width=68) (actual time=0.006..0.006 rows=0 loops=1)

26. 1,564.833 1,564.833 ↑ 1.0 4,499,999 1

Seq Scan on _hyper_5_4_chunk (cost=0.00..165,050.92 rows=4,505,092 width=68) (actual time=0.023..1,564.833 rows=4,499,999 loops=1)