explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gtYL

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 1,509.311 ↑ 1.0 1 1

Limit (cost=15,532.27..15,544.83 rows=1 width=216) (actual time=1,509.307..1,509.311 rows=1 loops=1)

2.          

CTE latest

3. 4.556 4.556 ↓ 1.1 20,236 1

Index Scan using latest_file_results_product_context_id_ix on latest_file_results (cost=0.43..15,113.00 rows=18,161 width=8) (actual time=0.016..4.556 rows=20,236 loops=1)

  • Index Cond: (product_context_id = 464)
4. 0.017 1,509.306 ↑ 16,299.0 1 1

WindowAgg (cost=419.27..205,164.39 rows=16,299 width=216) (actual time=1,509.306..1,509.306 rows=1 loops=1)

5. 0.029 1,509.289 ↑ 1,481.7 11 1

Nested Loop Left Join (cost=419.27..204,919.90 rows=16,299 width=200) (actual time=1,498.310..1,509.289 rows=11 loops=1)

6. 0.010 1,509.205 ↑ 1,481.7 11 1

Nested Loop (cost=418.98..198,260.95 rows=16,299 width=197) (actual time=1,498.294..1,509.205 rows=11 loops=1)

7. 7.369 1,509.162 ↑ 1,481.7 11 1

Merge Join (cost=418.70..193,346.11 rows=16,299 width=189) (actual time=1,498.275..1,509.162 rows=11 loops=1)

  • Merge Cond: (fr.id = latest.id)
8. 1,480.041 1,480.041 ↓ 3.9 126,650 1

Index Scan using file_results_pkey on file_results fr (cost=0.43..192,845.33 rows=32,598 width=189) (actual time=0.159..1,480.041 rows=126,650 loops=1)

  • Filter: (upper(filename) ~~ '%JAVA%'::text)
  • Rows Removed by Filter: 3787287
9. 6.763 21.752 ↓ 101.2 20,236 1

Sort (cost=418.27..418.77 rows=200 width=8) (actual time=19.436..21.752 rows=20,236 loops=1)

  • Sort Key: latest.id
  • Sort Method: quicksort Memory: 1717kB
10. 7.051 14.989 ↓ 101.2 20,236 1

HashAggregate (cost=408.62..410.62 rows=200 width=8) (actual time=12.079..14.989 rows=20,236 loops=1)

  • Group Key: latest.id
11. 7.938 7.938 ↓ 1.1 20,236 1

CTE Scan on latest (cost=0.00..363.22 rows=18,161 width=8) (actual time=0.018..7.938 rows=20,236 loops=1)

12. 0.033 0.033 ↑ 1.0 1 11

Index Scan using scan_requests_pkey on scan_requests req (cost=0.28..0.30 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: (id = fr.request_id)
13. 0.055 0.055 ↑ 1.0 1 11

Index Scan using file_comments_md5_ux on file_comments fc (cost=0.29..0.40 rows=1 width=124) (actual time=0.005..0.005 rows=1 loops=11)

  • Index Cond: ((product_id = fr.product_id) AND (context_id = req.product_context_id) AND (md5(file_name) = md5(((fr.filepath || '/'::text) || fr.filename))))
  • Filter: (file_name = ((fr.filepath || '/'::text) || fr.filename))