explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4894

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 182.109 ↑ 14.4 7 1

Hash Left Join (cost=2,108.25..2,110.71 rows=101 width=12) (actual time=182.106..182.109 rows=7 loops=1)

  • Hash Cond: (year_index.id = filtered_query.key)
2.          

CTE year_index

3. 0.030 0.194 ↑ 14.4 7 1

Recursive Union (cost=0.43..52.97 rows=101 width=4) (actual time=0.017..0.194 rows=7 loops=1)

4. 0.001 0.017 ↑ 1.0 1 1

Limit (cost=0.43..0.46 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1)

5. 0.016 0.016 ↑ 1,110,798.0 1 1

Index Only Scan using index_reports_on_enrolment_year_and_generation_time_and_id on reports (cost=0.43..38,698.34 rows=1,110,798 width=4) (actual time=0.015..0.016 rows=1 loops=1)

  • Heap Fetches: 0
6. 0.021 0.147 ↑ 10.0 1 7

Nested Loop (cost=0.43..5.05 rows=10 width=4) (actual time=0.017..0.021 rows=1 loops=7)

7. 0.014 0.014 ↑ 10.0 1 7

WorkTable Scan on year_index year_index_1 (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.002 rows=1 loops=7)

8. 0.014 0.112 ↑ 1.0 1 7

Limit (cost=0.43..0.46 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=7)

9. 0.098 0.098 ↑ 370,266.0 1 7

Index Only Scan using index_reports_on_enrolment_year_and_generation_time_and_id on reports reports_1 (cost=0.43..13,828.08 rows=370,266 width=4) (actual time=0.014..0.014 rows=1 loops=7)

  • Index Cond: (enrolment_year > year_index_1.id)
  • Heap Fetches: 0
10.          

CTE filtered_query

11. 0.018 182.054 ↑ 1.2 5 1

Nested Loop Semi Join (cost=0.43..2,055.09 rows=6 width=8) (actual time=75.721..182.054 rows=5 loops=1)

12. 0.183 0.183 ↑ 14.4 7 1

CTE Scan on year_index year_index_2 (cost=0.00..2.02 rows=101 width=4) (actual time=0.000..0.183 rows=7 loops=1)

13. 181.853 181.853 ↑ 290.0 1 7

Index Scan using index_reports_on_enrolment_year_and_generation_time_and_id on reports reports_2 (cost=0.43..5,449.58 rows=290 width=4) (actual time=25.979..25.979 rows=1 loops=7)

  • Index Cond: (enrolment_year = year_index_2.id)
  • Filter: (discipline_id = '3628507158065496'::bigint)
  • Rows Removed by Filter: 53086
14. 0.020 0.020 ↑ 14.4 7 1

CTE Scan on year_index (cost=0.00..2.02 rows=101 width=4) (actual time=0.019..0.020 rows=7 loops=1)

15. 0.012 182.081 ↑ 1.2 5 1

Hash (cost=0.12..0.12 rows=6 width=8) (actual time=182.081..182.081 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 182.069 182.069 ↑ 1.2 5 1

CTE Scan on filtered_query (cost=0.00..0.12 rows=6 width=8) (actual time=75.725..182.069 rows=5 loops=1)