explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v0DJ : Optimization for: Optimization for: plan #3GoF; plan #ktRQ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.852 22.410 ↑ 1.0 208 1

Hash Join (cost=19.98..11,323.82 rows=208 width=542) (actual time=0.657..22.410 rows=208 loops=1)

  • Hash Cond: (t.order_id = o.order_id)
2. 0.100 0.503 ↑ 1.0 208 1

Hash Join (cost=8.59..16.51 rows=208 width=412) (actual time=0.181..0.503 rows=208 loops=1)

  • Hash Cond: (t.project_id = p.project_id)
3. 0.078 0.350 ↑ 1.0 208 1

Hash Left Join (cost=3.31..10.67 rows=208 width=399) (actual time=0.117..0.350 rows=208 loops=1)

  • Hash Cond: (tw.worker_id = u.user_id)
4. 0.103 0.230 ↑ 1.0 208 1

Hash Left Join (cost=2.01..8.65 rows=208 width=309) (actual time=0.064..0.230 rows=208 loops=1)

  • Hash Cond: (t.task_id = tw.task_id)
5. 0.090 0.090 ↑ 1.0 208 1

Seq Scan on tasks t (cost=0.00..6.08 rows=208 width=183) (actual time=0.015..0.090 rows=208 loops=1)

  • Filter: (removed_at IS NULL)
6. 0.017 0.037 ↓ 1.2 52 1

Hash (cost=1.45..1.45 rows=45 width=134) (actual time=0.037..0.037 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
7. 0.020 0.020 ↓ 1.2 52 1

Seq Scan on task_workers tw (cost=0.00..1.45 rows=45 width=134) (actual time=0.008..0.020 rows=52 loops=1)

8. 0.033 0.042 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=98) (actual time=0.042..0.042 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.009 0.009 ↑ 1.0 13 1

Seq Scan on users u (cost=0.00..1.13 rows=13 width=98) (actual time=0.005..0.009 rows=13 loops=1)

10. 0.024 0.053 ↑ 1.3 109 1

Hash (cost=3.46..3.46 rows=146 width=21) (actual time=0.053..0.053 rows=109 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
11. 0.029 0.029 ↑ 1.3 109 1

Seq Scan on projects p (cost=0.00..3.46 rows=146 width=21) (actual time=0.007..0.029 rows=109 loops=1)

12. 0.165 0.255 ↑ 1.0 195 1

Hash (cost=8.95..8.95 rows=195 width=26) (actual time=0.255..0.255 rows=195 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
13. 0.090 0.090 ↑ 1.0 195 1

Seq Scan on orders o (cost=0.00..8.95 rows=195 width=26) (actual time=0.008..0.090 rows=195 loops=1)

14.          

SubPlan (forHash Join)

15. 0.416 5.408 ↑ 1.0 1 208

Aggregate (cost=22.89..22.91 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=208)

16. 0.000 4.992 ↑ 2.0 1 208

Subquery Scan on t1 (cost=0.55..22.89 rows=2 width=94) (actual time=0.021..0.024 rows=1 loops=208)

17. 0.208 4.992 ↑ 2.0 1 208

Limit (cost=0.55..22.87 rows=2 width=70) (actual time=0.020..0.024 rows=1 loops=208)

18. 0.000 4.784 ↑ 2.0 1 208

Nested Loop Left Join (cost=0.55..22.87 rows=2 width=70) (actual time=0.020..0.023 rows=1 loops=208)

19. 0.208 4.576 ↑ 2.0 1 208

Nested Loop Left Join (cost=0.28..21.76 rows=2 width=8) (actual time=0.019..0.022 rows=1 loops=208)

  • Join Filter: (sf.file_id = wsf.file_id)
  • Rows Removed by Join Filter: 2
20. 0.243 4.368 ↑ 2.0 1 208

Nested Loop (cost=0.28..20.63 rows=2 width=16) (actual time=0.018..0.021 rows=1 loops=208)

21. 3.744 3.744 ↑ 2.0 1 208

Seq Scan on task_source_files sf (cost=0.00..4.03 rows=2 width=8) (actual time=0.016..0.018 rows=1 loops=208)

  • Filter: (task_id = t.task_id)
  • Rows Removed by Filter: 145
22. 0.381 0.381 ↑ 1.0 1 127

Index Scan using files_pkey on files f_1 (cost=0.28..8.29 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=127)

  • Index Cond: (file_id = sf.file_id)
23. 0.000 0.000 ↑ 1.0 3 127

Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.000..0.000 rows=3 loops=127)

24. 0.008 0.008 ↑ 1.0 3 1

Seq Scan on task_worker_source_files wsf (cost=0.00..1.03 rows=3 width=8) (actual time=0.005..0.008 rows=3 loops=1)

25. 0.254 0.254 ↑ 1.0 1 127

Index Scan using files_pkey on files f (cost=0.28..0.55 rows=1 width=70) (actual time=0.002..0.002 rows=1 loops=127)

  • Index Cond: (f_1.file_id = file_id)
26. 0.416 4.576 ↑ 1.0 1 208

Aggregate (cost=4.09..4.10 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=208)

27. 4.160 4.160 ↑ 2.0 1 208

Seq Scan on task_source_files (cost=0.00..4.03 rows=2 width=8) (actual time=0.016..0.020 rows=1 loops=208)

  • Filter: (task_id = t.task_id)
  • Rows Removed by Filter: 161
28. 0.208 4.992 ↑ 1.0 1 208

Aggregate (cost=21.83..21.84 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=208)

29. 0.208 4.784 ↑ 2.0 1 208

Nested Loop Left Join (cost=0.28..21.76 rows=2 width=16) (actual time=0.018..0.023 rows=1 loops=208)

  • Join Filter: (sf_1.file_id = wsf_1.file_id)
  • Rows Removed by Join Filter: 2
30. 0.300 4.576 ↑ 2.0 1 208

Nested Loop (cost=0.28..20.63 rows=2 width=16) (actual time=0.018..0.022 rows=1 loops=208)

31. 3.952 3.952 ↑ 2.0 1 208

Seq Scan on task_source_files sf_1 (cost=0.00..4.03 rows=2 width=16) (actual time=0.016..0.019 rows=1 loops=208)

  • Filter: (task_id = t.task_id)
  • Rows Removed by Filter: 161
32. 0.324 0.324 ↑ 1.0 1 162

Index Scan using files_pkey on files f_2 (cost=0.28..8.29 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=162)

  • Index Cond: (file_id = sf_1.file_id)
33. 0.000 0.000 ↑ 1.0 3 162

Materialize (cost=0.00..1.04 rows=3 width=16) (actual time=0.000..0.000 rows=3 loops=162)

34. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on task_worker_source_files wsf_1 (cost=0.00..1.03 rows=3 width=16) (actual time=0.002..0.003 rows=3 loops=1)

35. 0.416 4.576 ↑ 1.0 1 208

Aggregate (cost=4.03..4.04 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=208)

36. 4.160 4.160 ↑ 2.0 1 208

Seq Scan on task_source_files task_source_files_1 (cost=0.00..4.03 rows=2 width=8) (actual time=0.016..0.020 rows=1 loops=208)

  • Filter: (task_id = t.task_id)
  • Rows Removed by Filter: 161
37. 0.208 1.248 ↑ 1.0 1 208

Aggregate (cost=1.40..1.41 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=208)

38. 1.040 1.040 ↓ 0.0 0 208

Seq Scan on task_result_files (cost=0.00..1.39 rows=3 width=8) (actual time=0.005..0.005 rows=0 loops=208)

  • Filter: (task_id = t.task_id)
  • Rows Removed by Filter: 31
Planning time : 2.251 ms
Execution time : 22.682 ms