explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NQLP

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 64.920 ↑ 81.0 1 1

Subquery Scan on x (cost=18,247.69..18,532.92 rows=81 width=216) (actual time=64.919..64.920 rows=1 loops=1)

  • Filter: ((x._rownum_ >= 0) AND (x._rownum_ <= 1))
  • Rows Removed by Filter: 10
2. 0.010 64.916 ↑ 1,481.7 11 1

Sort (cost=18,247.69..18,288.43 rows=16,299 width=216) (actual time=64.916..64.916 rows=11 loops=1)

  • Sort Key: fr.id
  • Sort Method: quicksort Memory: 27kB
3.          

CTE latest

4. 4.856 4.856 ↓ 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.022..4.856 rows=20,236 loops=1)

  • Index Cond: (product_context_id = 464)
5. 0.030 64.906 ↑ 1,481.7 11 1

WindowAgg (cost=991.71..1,994.36 rows=16,299 width=216) (actual time=64.901..64.906 rows=11 loops=1)

6. 0.059 64.876 ↑ 1,481.7 11 1

Hash Left Join (cost=991.71..1,749.88 rows=16,299 width=200) (actual time=24.933..64.876 rows=11 loops=1)

  • Hash Cond: ((fr.product_id = fc.product_id) AND (req.product_context_id = fc.context_id) AND (((fr.filepath || '/'::text) || fr.filename) = fc.file_name) AND (md5(((fr.filepath || '/'::text) || fr.filename)) = md5(fc.file_name)))
7. 0.019 58.441 ↑ 1,481.7 11 1

Hash Join (cost=597.72..1,172.53 rows=16,299 width=197) (actual time=18.538..58.441 rows=11 loops=1)

  • Hash Cond: (fr.request_id = req.id)
8. 0.295 56.991 ↑ 1,481.7 11 1

Nested Loop (cost=409.05..941.02 rows=16,299 width=189) (actual time=17.097..56.991 rows=11 loops=1)

9. 7.857 16.224 ↓ 101.2 20,236 1

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

  • Group Key: latest.id
10. 8.367 8.367 ↓ 1.1 20,236 1

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

11. 40.472 40.472 ↓ 0.0 0 20,236

Index Scan using file_results_pkey on file_results fr (cost=0.43..2.65 rows=1 width=189) (actual time=0.002..0.002 rows=0 loops=20,236)

  • Index Cond: (id = latest.id)
  • Filter: (upper(filename) ~~ '%JAVA%'::text)
  • Rows Removed by Filter: 1
12. 0.377 1.431 ↓ 1.0 3,619 1

Hash (cost=143.85..143.85 rows=3,585 width=16) (actual time=1.431..1.431 rows=3,619 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 202kB
13. 1.054 1.054 ↓ 1.0 3,619 1

Seq Scan on scan_requests req (cost=0.00..143.85 rows=3,585 width=16) (actual time=0.012..1.054 rows=3,619 loops=1)

14. 5.027 6.376 ↑ 1.0 7,733 1

Hash (cost=239.33..239.33 rows=7,733 width=124) (actual time=6.376..6.376 rows=7,733 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1290kB
15. 1.349 1.349 ↑ 1.0 7,733 1

Seq Scan on file_comments fc (cost=0.00..239.33 rows=7,733 width=124) (actual time=0.007..1.349 rows=7,733 loops=1)