explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rUxP : 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. 6.879 686.834 ↑ 1.1 7,378 1

Sort (cost=2,194.41..2,214.03 rows=7,849 width=64) (actual time=684.646..686.834 rows=7,378 loops=1)

  • Sort Key: t.created DESC
  • Sort Method: quicksort Memory: 1229kB
  • Buffers: shared hit=248512, temp read=410 written=411
2.          

CTE args

3. 0.005 0.470 ↑ 1.0 1 1

Result (cost=124.73..124.74 rows=1 width=32) (actual time=0.47..0.47 rows=1 loops=1)

  • Buffers: shared hit=44
4.          

Initplan (for Result)

5. 0.432 0.432 ↑ 1.0 1 1

Seq Scan on users users (cost=0..57.84 rows=1 width=16) (actual time=0.41..0.432 rows=1 loops=1)

  • Filter: (users.email = 'serbut@ser.but'::text)
  • Buffers: shared hit=40
6. 0.033 0.033 ↑ 1.0 1 1

Seq Scan on databases databases (cost=0..4.53 rows=1 width=16) (actual time=0.032..0.033 rows=1 loops=1)

  • Filter: (databases.name = 'viruses'::text)
  • Buffers: shared hit=4
7. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on users users_1 (cost=0..57.84 rows=1 width=16) (never executed)

  • Filter: (users_1.email = 'serbut@ser.but'::text)
8. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on databases databases_1 (cost=0..4.53 rows=1 width=16) (never executed)

  • Filter: (databases_1.name = 'viruses'::text)
9. 2.455 679.955 ↑ 1.1 7,378 1

Subquery Scan on t (cost=1,424.55..1,561.91 rows=7,849 width=64) (actual time=661.085..679.955 rows=7,378 loops=1)

  • Buffers: shared hit=248509, temp read=410 written=411
10. 7.378 677.500 ↑ 1.1 7,378 1

Unique (cost=1,424.55..1,483.42 rows=7,849 width=104) (actual time=661.081..677.5 rows=7,378 loops=1)

  • Buffers: shared hit=248509, temp read=410 written=411
11. 87.115 670.122 ↓ 3.5 27,402 1

Sort (cost=1,424.55..1,444.18 rows=7,849 width=104) (actual time=661.08..670.122 rows=27,402 loops=1)

  • Sort Key: files.id, an_status.dataset, an_status.created DESC
  • Sort Method: external merge Disk: 3280kB
  • Buffers: shared hit=248509, temp read=410 written=411
12. 18.731 583.007 ↓ 3.5 27,402 1

Hash Join (cost=27.55..916.79 rows=7,849 width=104) (actual time=2.066..583.007 rows=27,402 loops=1)

  • Buffers: shared hit=248506
13. 21.856 564.216 ↓ 3.5 27,402 1

Nested Loop (cost=25.07..892.38 rows=7,849 width=114) (actual time=1.986..564.216 rows=27,402 loops=1)

  • Buffers: shared hit=248505
14. 13.730 114.115 ↓ 60.0 10,445 1

Nested Loop (cost=24.65..681.54 rows=174 width=74) (actual time=1.959..114.115 rows=10,445 loops=1)

  • Buffers: shared hit=64548
15. 4.891 20.950 ↓ 94.0 15,887 1

Nested Loop (cost=24.23..586.77 rows=169 width=58) (actual time=1.901..20.95 rows=15,887 loops=1)

  • Buffers: shared hit=980
16. 0.473 0.473 ↑ 1.0 1 1

CTE Scan on args args (cost=0..0.02 rows=1 width=16) (actual time=0.472..0.473 rows=1 loops=1)

  • Buffers: shared hit=44
17. 14.308 15.586 ↓ 94.0 15,887 1

Bitmap Heap Scan on files files (cost=24.23..585.06 rows=169 width=75) (actual time=1.424..15.586 rows=15,887 loops=1)

  • Filter: ((NOT files.removed) AND ((files.owner = args.var_owner) OR files.is_public))
  • Heap Blocks: exact=870
  • Buffers: shared hit=936
18. 0.002 1.278 ↓ 0.0 0 1

BitmapOr (cost=24.23..24.23 rows=172 width=0) (actual time=1.278..1.278 rows=0 loops=1)

  • Buffers: shared hit=66
19. 1.254 1.254 ↓ 151.0 15,857 1

Bitmap Index Scan on idx_files_owner (cost=0..5.2 rows=105 width=0) (actual time=1.254..1.254 rows=15,857 loops=1)

  • Index Cond: (files.owner = args.var_owner)
  • Buffers: shared hit=64
20. 0.022 0.022 ↑ 1.1 63 1

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

  • Index Cond: (files.is_public = true)
  • Buffers: shared hit=2
21. 79.435 79.435 ↑ 1.0 1 15,887

Index Scan using idx_sample_runs_file_id on sample_runs runs (cost=0.42..0.55 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=15,887)

  • Index Cond: (runs.file_id = files.id)
  • Filter: ((runs.status)::text = ANY ('{Success,Inconclusive}'::text[]))
  • Buffers: shared hit=63568
22. 428.245 428.245 ↓ 3.0 3 10,445

Index Scan using idx_analysis_status_dataset on analysis_status an_status (cost=0.42..1.2 rows=1 width=56) (actual time=0.014..0.041 rows=3 loops=10,445)

  • Index Cond: (an_status.dataset = runs.dataset_id)
  • Filter: ((an_status.database = '37f9c39f-4b3d-40bd-be60-05b9ceac286c'::uuid) AND (an_status.status = 'Success'::text))
  • Buffers: shared hit=183957
23. 0.035 0.060 ↑ 1.0 66 1

Hash (cost=1.66..1.66 rows=66 width=22) (actual time=0.059..0.06 rows=66 loops=1)

  • Buffers: shared hit=1
24. 0.025 0.025 ↑ 1.0 66 1

Seq Scan on analysis_versions dbs (cost=0..1.66 rows=66 width=22) (actual time=0.01..0.025 rows=66 loops=1)

  • Buffers: shared hit=1
Planning time : 5.234 ms
Execution time : 688.617 ms