explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FkdU

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 63.119 ↑ 4,074.8 4 1

Sort (cost=18,206.94..18,247.69 rows=16,299 width=208) (actual time=63.119..63.119 rows=4 loops=1)

  • Sort Key: fr.id
  • Sort Method: quicksort Memory: 26kB
2.          

CTE latest

3. 4.516 4.516 ↓ 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.018..4.516 rows=20,236 loops=1)

  • Index Cond: (product_context_id = 464)
4. 0.019 63.112 ↑ 4,074.8 4 1

WindowAgg (cost=991.71..1,953.62 rows=16,299 width=208) (actual time=63.109..63.112 rows=4 loops=1)

5. 0.033 63.093 ↑ 4,074.8 4 1

Hash Left Join (cost=991.71..1,749.88 rows=16,299 width=200) (actual time=29.674..63.093 rows=4 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)))
6. 0.015 56.594 ↑ 4,074.8 4 1

Hash Join (cost=597.72..1,172.53 rows=16,299 width=197) (actual time=23.190..56.594 rows=4 loops=1)

  • Hash Cond: (fr.request_id = req.id)
7. 0.000 55.254 ↑ 4,074.8 4 1

Nested Loop (cost=409.05..941.02 rows=16,299 width=189) (actual time=21.855..55.254 rows=4 loops=1)

8. 7.619 15.406 ↓ 101.2 20,236 1

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

  • Group Key: latest.id
9. 7.787 7.787 ↓ 1.1 20,236 1

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

10. 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: (lower(filename) ~~ '%json%'::text)
  • Rows Removed by Filter: 1
11. 0.359 1.325 ↓ 1.0 3,619 1

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

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

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

13. 5.075 6.466 ↑ 1.0 7,733 1

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

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

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