explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IXfQ

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

Limit (cost=15,532.27..15,544.83 rows=1 width=208) (actual time=1,494.522..1,494.526 rows=1 loops=1)

2.          

CTE latest

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

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

WindowAgg (cost=419.27..205,123.64 rows=16,299 width=208) (actual time=1,494.521..1,494.521 rows=1 loops=1)

5. 0.015 1,494.507 ↑ 4,074.8 4 1

Nested Loop Left Join (cost=419.27..204,919.90 rows=16,299 width=200) (actual time=1,483.321..1,494.507 rows=4 loops=1)

6. 0.009 1,494.472 ↑ 4,074.8 4 1

Nested Loop (cost=418.98..198,260.95 rows=16,299 width=197) (actual time=1,483.306..1,494.472 rows=4 loops=1)

7. 1.870 1,494.443 ↑ 4,074.8 4 1

Merge Join (cost=418.70..193,346.11 rows=16,299 width=189) (actual time=1,483.290..1,494.443 rows=4 loops=1)

  • Merge Cond: (fr.id = latest.id)
8. 1,471.831 1,471.831 ↑ 4.3 7,627 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,471.831 rows=7,627 loops=1)

  • Filter: (lower(filename) ~~ '%json%'::text)
  • Rows Removed by Filter: 3907102
9. 5.993 20.742 ↓ 101.2 20,236 1

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

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

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

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

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

12. 0.020 0.020 ↑ 1.0 1 4

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

  • Index Cond: (id = fr.request_id)
13. 0.020 0.020 ↓ 0.0 0 4

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=0 loops=4)

  • 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))