explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aE1w

Settings
# exclusive inclusive rows x rows loops node
1. 4,100.062 35,019.326 ↑ 2.3 17,500 1

GroupAggregate (cost=203,279,828,303,575.09..210,977,903,364,631.84 rows=40,000 width=100) (actual time=29,647.291..35,019.326 rows=17,500 loops=1)

  • Group Key: subquery."stats_session.user_agent_family", subquery."stats_event.event_id
2. 6,963.649 30,919.264 ↑ 38,015,193.9 4,499,999 1

Sort (cost=203,279,828,303,575.09..203,707,499,140,256.03 rows=171,068,334,672,372 width=116) (actual time=29,646.956..30,919.264 rows=4,499,999 loops=1)

  • Sort Key: subquery."stats_session.user_agent_family", subquery."stats_event.event_id
  • Sort Method: external merge Disk: 330984kB
3. 952.340 23,955.615 ↑ 38,015,193.9 4,499,999 1

Subquery Scan on subquery (cost=106,572,490,015,334.06..108,710,844,198,738.72 rows=171,068,334,672,372 width=116) (actual time=21,344.830..23,955.615 rows=4,499,999 loops=1)

4. 11,161.278 23,003.275 ↑ 38,015,193.9 4,499,999 1

Sort (cost=106,572,490,015,334.06..107,000,160,852,015.00 rows=171,068,334,672,372 width=132) (actual time=21,344.826..23,003.275 rows=4,499,999 loops=1)

  • Sort Key: stats_session.user_agent_family, stats_event.event_id DESC
  • Sort Method: external merge Disk: 401480kB
5. 2,758.417 11,841.997 ↑ 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,173.976..11,841.997 rows=4,499,999 loops=1)

6. 1,168.989 9,083.580 ↑ 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,173.954..9,083.580 rows=4,499,999 loops=1)

  • Merge Cond: (stats_page_view.id = stats_event.page_view_id)
7. 380.186 1,119.121 ↑ 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=978.920..1,119.121 rows=450,000 loops=1)

  • Sort Key: stats_page_view.id
  • Sort Method: external merge Disk: 22840kB
8. 154.033 738.935 ↑ 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=456.374..738.935 rows=450,000 loops=1)

  • Merge Cond: (stats_page_view.session_id = stats_session.id)
9. 278.781 434.749 ↑ 1.0 450,000 1

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

  • Sort Key: stats_page_view.session_id
  • Sort Method: external merge Disk: 20288kB
10. 45.680 155.968 ↑ 1.0 450,000 1

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

11. 0.003 0.003 ↓ 0.0 0 1

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

12. 110.285 110.285 ↑ 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..110.285 rows=450,000 loops=1)

13. 35.175 150.153 ↑ 7.5 449,991 1

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

14. 32.264 114.978 ↑ 75.0 45,000 1

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

  • Sort Key: stats_session.id
  • Sort Method: external merge Disk: 2112kB
15. 12.487 82.714 ↑ 75.0 45,000 1

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

  • Merge Cond: (stats_user.id = stats_session.user_id)
16. 1.528 9.182 ↑ 1.0 15,000 1

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

  • Sort Key: stats_user.id
17. 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
18. 7.645 7.645 ↑ 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..7.645 rows=15,000 loops=1)

  • Heap Fetches: 15000
19. 42.525 61.045 ↑ 1.0 45,000 1

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

  • Sort Key: stats_session.user_id
  • Sort Method: external sort Disk: 2640kB
20. 4.517 18.520 ↑ 1.0 45,000 1

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

21. 0.004 0.004 ↓ 0.0 0 1

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

22. 13.999 13.999 ↑ 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..13.999 rows=45,000 loops=1)

23. 603.162 6,795.470 ↑ 1.0 4,499,999 1

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

24. 4,483.908 6,192.308 ↑ 1.0 4,499,999 1

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

  • Sort Key: stats_event.page_view_id
  • Sort Method: external merge Disk: 202600kB
25. 459.845 1,708.400 ↑ 1.0 4,499,999 1

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

26. 0.008 0.008 ↓ 0.0 0 1

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

27. 1,248.547 1,248.547 ↑ 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.028..1,248.547 rows=4,499,999 loops=1)