explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5xsP

Settings
# exclusive inclusive rows x rows loops node
1. 57.361 14,728.417 ↑ 8.3 67,970 1

GroupAggregate (cost=22,577,213.15..23,526,010.9 rows=560,881 width=12) (actual time=14,626.348..14,728.417 rows=67,970 loops=1)

  • Group Key: progress_progress.id
  • Buffers: shared hit=35425 read=72330 dirtied=5, temp read=40605 written=40643
2. 224.480 14,671.056 ↑ 386.8 325,089 1

Sort (cost=22,577,213.15..22,891,609.47 rows=125,758,525 width=12) (actual time=14,626.333..14,671.056 rows=325,089 loops=1)

  • Sort Key: progress_progress.id
  • Sort Method: external merge Disk: 8280kB
  • Buffers: shared hit=35425 read=72330 dirtied=5, temp read=40605 written=40643
3. 644.672 14,446.576 ↑ 386.8 325,089 1

Merge Join (cost=678,331.89..3,202,643.88 rows=125,758,525 width=12) (actual time=12,218.799..14,446.576 rows=325,089 loops=1)

  • Buffers: shared hit=35425 read=72330 dirtied=5, temp read=39570 written=39607
4. 3.769 80.688 ↑ 3.3 2,118 1

Sort (cost=10,976.2..10,993.53 rows=6,931 width=40) (actual time=79.735..80.688 rows=2,118 loops=1)

  • Sort Key: lessons.lesson_token
  • Sort Method: quicksort Memory: 262kB
  • Buffers: shared hit=1377
5. 1.842 76.919 ↑ 3.3 2,118 1

Subquery Scan on lessons (cost=10,360.77..10,534.05 rows=6,931 width=40) (actual time=74.003..76.919 rows=2,118 loops=1)

  • Filter: lessons.is_homework
  • Buffers: shared hit=1377
6. 19.971 75.077 ↓ 1.0 14,406 1

Sort (cost=10,360.77..10,395.43 rows=13,862 width=1,653) (actual time=73.997..75.077 rows=14,406 loops=1)

  • Sort Key: ((lm.token)::text), (row_number() OVER (?)), (row_number() OVER (?))
  • Sort Method: quicksort Memory: 4211kB
  • Buffers: shared hit=1377
7. 18.085 55.106 ↓ 1.0 14,406 1

WindowAgg (cost=3,235.97..3,721.14 rows=13,862 width=1,653) (actual time=35.645..55.106 rows=14,406 loops=1)

  • Buffers: shared hit=1377
8. 13.140 36.343 ↓ 1.0 14,406 1

Sort (cost=3,157.77..3,192.43 rows=13,862 width=50) (actual time=34.388..36.343 rows=14,406 loops=1)

  • Sort Key: lm.token, courses_topic.id, courses_vertex.sort
  • Sort Method: quicksort Memory: 2410kB
  • Buffers: shared hit=1330
9. 5.125 23.203 ↓ 1.0 14,406 1

Hash Join (cost=657.68..2,204.15 rows=13,862 width=50) (actual time=7.682..23.203 rows=14,406 loops=1)

  • Buffers: shared hit=1330
10. 10.421 10.421 ↓ 1.0 14,406 1

Seq Scan on courses_vertex courses_vertex (cost=0..1,355.86 rows=13,862 width=26) (actual time=0.012..10.421 rows=14,406 loops=1)

  • Filter: ((courses_vertex.deleted_at IS NULL) AND ((courses_vertex.status)::text = 'published'::text))
  • Buffers: shared hit=1161
11. 0.804 7.657 ↓ 1.0 4,031 1

Hash (cost=607.4..607.4 rows=4,023 width=28) (actual time=7.657..7.657 rows=4,031 loops=1)

  • Buffers: shared hit=169
12. 1.052 6.853 ↓ 1.0 4,031 1

Hash Join (cost=476.07..607.4 rows=4,023 width=28) (actual time=3.278..6.853 rows=4,031 loops=1)

  • Buffers: shared hit=169
13. 2.255 5.461 ↓ 1.0 4,031 1

WindowAgg (cost=418.55..499.01 rows=4,023 width=570) (actual time=2.928..5.461 rows=4,031 loops=1)

  • Buffers: shared hit=125
14. 1.922 3.206 ↓ 1.0 4,031 1

Sort (cost=418.55..428.6 rows=4,023 width=10) (actual time=2.917..3.206 rows=4,031 loops=1)

  • Sort Key: courses_topic.course_id, courses_topic.sort
  • Sort Method: quicksort Memory: 285kB
  • Buffers: shared hit=125
15. 1.284 1.284 ↓ 1.0 4,031 1

Seq Scan on courses_topic courses_topic (cost=0..177.69 rows=4,023 width=10) (actual time=0.006..1.284 rows=4,031 loops=1)

  • Filter: ((courses_topic.status)::text = 'published'::text)
  • Buffers: shared hit=125
16. 0.107 0.340 ↓ 1.0 605 1

Hash (cost=50.01..50.01 rows=601 width=20) (actual time=0.339..0.34 rows=605 loops=1)

  • Buffers: shared hit=44
17. 0.233 0.233 ↓ 1.0 605 1

Seq Scan on courses_learningmaterials lm (cost=0..50.01 rows=601 width=20) (actual time=0.005..0.233 rows=605 loops=1)

  • Buffers: shared hit=44
18.          

SubPlan (for WindowAgg)

19. 0.678 0.678 ↑ 1.0 2,496 1

Seq Scan on courses_homework courses_homework (cost=0..71.96 rows=2,496 width=4) (actual time=0.02..0.678 rows=2,496 loops=1)

  • Buffers: shared hit=47
20. 538.917 13,721.216 ↓ 1.0 3,642,442 1

Materialize (cost=667,355.69..685,500.04 rows=3,628,871 width=20) (actual time=12,135.257..13,721.216 rows=3,642,442 loops=1)

  • Buffers: shared hit=34048 read=72330 dirtied=5, temp read=39570 written=39607
21. 8,000.564 13,182.299 ↓ 1.0 3,642,442 1

Sort (cost=667,355.69..676,427.86 rows=3,628,871 width=20) (actual time=12,135.252..13,182.299 rows=3,642,442 loops=1)

  • Sort Key: ((progress_progresslesson.lesson_token)::text)
  • Sort Method: external merge Disk: 238936kB
  • Buffers: shared hit=34048 read=72330 dirtied=5, temp read=39570 written=39607
22. 2,595.076 5,181.735 ↓ 1.0 3,644,536 1

Hash Join (cost=33,075.82..229,444.43 rows=3,628,871 width=20) (actual time=895.456..5,181.735 rows=3,644,536 loops=1)

  • Buffers: shared hit=34048 read=72330 dirtied=5, temp read=9703 written=9703
23. 1,692.042 1,692.042 ↓ 1.0 3,644,536 1

Seq Scan on progress_progresslesson progress_progresslesson (cost=0..142,125.75 rows=3,628,871 width=20) (actual time=0.029..1,692.042 rows=3,644,536 loops=1)

  • Filter: ((progress_progresslesson.status)::text = ANY ('{wait,fail,done}'::text[]))
  • Buffers: shared hit=33518 read=54595 dirtied=5
24. 165.623 894.617 ↓ 1.0 565,476 1

Hash (cost=23,873.81..23,873.81 rows=560,881 width=4) (actual time=894.617..894.617 rows=565,476 loops=1)

  • Buffers: shared hit=530 read=17735, temp written=828
25. 728.994 728.994 ↓ 1.0 565,476 1

Seq Scan on progress_progress progress_progress (cost=0..23,873.81 rows=560,881 width=4) (actual time=1.043..728.994 rows=565,476 loops=1)

  • Buffers: shared hit=530 read=17735
Planning time : 1.299 ms
Execution time : 14,773.422 ms