explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 161E : Optimization for: Optimization for: Optimization for: Optimization for: Comparative Anal Files List; plan #fYLX; plan #fvyE; plan #S1OY; plan #cSLm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4.335 209.178 ↑ 284.1 4,401 1

Sort (cost=460,167.05..463,292.4 rows=1,250,142 width=251) (actual time=208.812..209.178 rows=4,401 loops=1)

  • Sort Key: files.id, files.created
  • Sort Method: quicksort Memory: 1363kB
  • Buffers: shared hit=2904, temp read=697 written=697
2.          

CTE files

3. 13.816 14.974 ↓ 1.0 15,876 1

Bitmap Heap Scan on files f (cost=378.73..3,938.54 rows=15,254 width=107) (actual time=1.3..14.974 rows=15,876 loops=1)

  • Filter: ((NOT f.removed) AND (f.content_type <> 1) AND (f.is_public OR (f.owner = 'f137e2bf-785d-4cd2-ac07-96ed1f78b7c8'::uuid)))
  • Heap Blocks: exact=870
  • Buffers: shared hit=936
4. 0.001 1.158 ↓ 0.0 0 1

BitmapOr (cost=378.73..378.73 rows=15,787 width=0) (actual time=1.158..1.158 rows=0 loops=1)

  • Buffers: shared hit=66
5. 0.023 0.023 ↑ 1.1 63 1

Bitmap Index Scan on idx_files_public (cost=0..4.8 rows=68 width=0) (actual time=0.023..0.023 rows=63 loops=1)

  • Index Cond: (f.is_public = true)
  • Buffers: shared hit=2
6. 1.134 1.134 ↓ 1.0 15,857 1

Bitmap Index Scan on idx_files_owner (cost=0..366.31 rows=15,719 width=0) (actual time=1.134..1.134 rows=15,857 loops=1)

  • Index Cond: (f.owner = 'f137e2bf-785d-4cd2-ac07-96ed1f78b7c8'::uuid)
  • Buffers: shared hit=64
7.          

CTE runs_map

8. 4.511 51.475 ↑ 2.0 8,115 1

Unique (cost=3,403.92..3,497.63 rows=16,391 width=32) (actual time=43.441..51.475 rows=8,115 loops=1)

  • Buffers: shared hit=984
9. 18.569 46.964 ↓ 1.3 23,485 1

Sort (cost=3,403.92..3,450.77 rows=18,742 width=32) (actual time=43.439..46.964 rows=23,485 loops=1)

  • Sort Key: sample_runs.file_id
  • Sort Method: quicksort Memory: 2603kB
  • Buffers: shared hit=984
10. 28.395 28.395 ↓ 1.3 23,485 1

Seq Scan on sample_runs sample_runs (cost=0..2,073.8 rows=18,742 width=32) (actual time=0.019..28.395 rows=23,485 loops=1)

  • Filter: ((sample_runs.status IS NOT NULL) AND (sample_runs.file_id IS NOT NULL) AND ((sample_runs.status)::text <> ALL ('{Success,Inconclusive}'::text[])))
  • Buffers: shared hit=984
11.          

CTE runs

12. 24.638 146.000 ↑ 2.0 8,115 1

Hash Join (cost=3,882.64..5,352.49 rows=16,391 width=63) (actual time=110.872..146 rows=8,115 loops=1)

  • Buffers: shared hit=1968, temp read=697 written=697
13. 54.384 54.384 ↑ 2.0 8,115 1

CTE Scan on runs_map m (cost=0..327.82 rows=16,391 width=16) (actual time=43.451..54.384 rows=8,115 loops=1)

  • Buffers: shared hit=984
14. 43.120 66.978 ↑ 1.0 87,184 1

Hash (cost=1,855.84..1,855.84 rows=87,184 width=63) (actual time=66.978..66.978 rows=87,184 loops=1)

  • Buffers: shared hit=984, temp written=666
15. 23.858 23.858 ↑ 1.0 87,184 1

Seq Scan on sample_runs r (cost=0..1,855.84 rows=87,184 width=63) (actual time=0.021..23.858 rows=87,184 loops=1)

  • Buffers: shared hit=984
16. 5.973 204.843 ↑ 284.1 4,401 1

Merge Join (cost=2,840.24..21,668.64 rows=1,250,142 width=251) (actual time=195.364..204.843 rows=4,401 loops=1)

  • Buffers: shared hit=2904, temp read=697 written=697
17. 13.025 43.536 ↓ 1.0 15,876 1

Sort (cost=1,365..1,403.13 rows=15,254 width=113) (actual time=40.807..43.536 rows=15,876 loops=1)

  • Sort Key: files.id
  • Sort Method: quicksort Memory: 2745kB
  • Buffers: shared hit=936
18. 30.511 30.511 ↓ 1.0 15,876 1

CTE Scan on files files (cost=0..305.08 rows=15,254 width=113) (actual time=1.304..30.511 rows=15,876 loops=1)

  • Buffers: shared hit=936
19. 4.644 155.334 ↑ 2.0 8,115 1

Sort (cost=1,475.24..1,516.22 rows=16,391 width=114) (actual time=154.532..155.334 rows=8,115 loops=1)

  • Sort Key: runs.file_id
  • Sort Method: quicksort Memory: 1334kB
  • Buffers: shared hit=1968, temp read=697 written=697
20. 150.690 150.690 ↑ 2.0 8,115 1

CTE Scan on runs runs (cost=0..327.82 rows=16,391 width=114) (actual time=110.874..150.69 rows=8,115 loops=1)

  • Buffers: shared hit=1968, temp read=697 written=697
Planning time : 0.439 ms
Execution time : 214.616 ms