explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e9Ev

Settings
# exclusive inclusive rows x rows loops node
1. 2.113 581.693 ↓ 5.0 10 1

Unique (cost=23,736.80..23,736.81 rows=2 width=4) (actual time=578.242..581.693 rows=10 loops=1)

2. 4.675 579.580 ↓ 13,427.5 26,855 1

Sort (cost=23,736.80..23,736.81 rows=2 width=4) (actual time=578.241..579.580 rows=26,855 loops=1)

  • Sort Key: pf.id
  • Sort Method: quicksort Memory: 2,027kB
3. 4.726 574.905 ↓ 13,427.5 26,855 1

Hash Left Join (cost=18,848.15..23,736.79 rows=2 width=4) (actual time=563.713..574.905 rows=26,855 loops=1)

  • Hash Cond: ((fi.id = rf1.field_id) AND (fa.id = rf1.farm_id))
4. 7.169 47.837 ↓ 13,427.5 26,855 1

Nested Loop (cost=62.36..4,942.02 rows=2 width=12) (actual time=41.194..47.837 rows=26,855 loops=1)

  • Join Filter: (projects.client_id = clients.id)
  • Rows Removed by Join Filter: 26,855
5. 0.092 0.092 ↑ 1.0 2 1

Index Only Scan using clients_pk on clients (cost=0.15..16.33 rows=2 width=4) (actual time=0.076..0.092 rows=2 loops=1)

  • Index Cond: (id = ANY ('{21,22}'::integer[]))
  • Heap Fetches: 2
6. 7.530 40.576 ↓ 75.2 26,855 2

Materialize (cost=62.21..4,915.87 rows=357 width=16) (actual time=6.391..20.288 rows=26,855 loops=2)

7. 8.108 33.046 ↓ 75.2 26,855 1

Nested Loop Left Join (cost=62.21..4,914.08 rows=357 width=16) (actual time=12.775..33.046 rows=26,855 loops=1)

  • Join Filter: (tsrange((fi.date_from)::timestamp without time zone, (fi.date_to)::timestamp without time zone) @> (dbpf2.date)::timestamp without time zone)
8. 0.150 18.665 ↓ 1.0 369 1

Nested Loop Left Join (cost=61.92..2,805.20 rows=357 width=16) (actual time=12.742..18.665 rows=369 loops=1)

9. 0.098 18.269 ↓ 2.7 82 1

Nested Loop (cost=61.64..2,765.81 rows=30 width=16) (actual time=12.710..18.269 rows=82 loops=1)

  • Join Filter: (pf.map_id = re.map_id)
  • Rows Removed by Join Filter: 737
10. 0.025 0.025 ↑ 1.0 1 1

Index Scan using regions_pk on regions re (cost=0.14..8.16 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: (id = 1,071)
11. 0.817 18.146 ↓ 9.2 819 1

Hash Join (cost=61.50..2,756.54 rows=89 width=20) (actual time=12.683..18.146 rows=819 loops=1)

  • Hash Cond: (dbpf2.process_f2_id = pf.id)
12. 16.619 16.619 ↓ 8.8 5,298 1

Seq Scan on date_by_process_f2 dbpf2 (cost=0.00..2,691.89 rows=602 width=8) (actual time=0.986..16.619 rows=5,298 loops=1)

  • Filter: ((date)::timestamp without time zone <@ '["2019-06-01 00:00:00","2020-06-30 00:00:00"]'::tsrange)
  • Rows Removed by Filter: 115,095
13. 0.028 0.710 ↑ 1.5 120 1

Hash (cost=59.26..59.26 rows=179 width=16) (actual time=0.710..0.710 rows=120 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
14. 0.221 0.682 ↑ 1.5 120 1

Hash Join (cost=18.80..59.26 rows=179 width=16) (actual time=0.255..0.682 rows=120 loops=1)

  • Hash Cond: (pf.process_result_id = pr.id)
15. 0.237 0.237 ↑ 1.0 1,213 1

Seq Scan on process_functions pf (cost=0.00..34.13 rows=1,213 width=12) (actual time=0.005..0.237 rows=1,213 loops=1)

16. 0.007 0.224 ↑ 1.8 24 1

Hash (cost=18.27..18.27 rows=42 width=12) (actual time=0.224..0.224 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.059 0.217 ↑ 1.8 24 1

Hash Join (cost=8.91..18.27 rows=42 width=12) (actual time=0.156..0.217 rows=24 loops=1)

  • Hash Cond: (pr.version_id = v.id)
18. 0.038 0.038 ↑ 1.0 287 1

Seq Scan on process_results pr (cost=0.00..7.87 rows=287 width=8) (actual time=0.004..0.038 rows=287 loops=1)

19. 0.011 0.120 ↑ 1.5 24 1

Hash (cost=8.47..8.47 rows=35 width=8) (actual time=0.119..0.120 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.055 0.109 ↑ 1.5 24 1

Hash Join (cost=2.41..8.47 rows=35 width=8) (actual time=0.057..0.109 rows=24 loops=1)

  • Hash Cond: (v.project_id = projects.id)
21. 0.029 0.029 ↑ 1.0 239 1

Seq Scan on versions v (cost=0.00..5.39 rows=239 width=8) (actual time=0.005..0.029 rows=239 loops=1)

22. 0.009 0.025 ↑ 1.0 9 1

Hash (cost=2.30..2.30 rows=9 width=8) (actual time=0.025..0.025 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.016 0.016 ↑ 1.0 9 1

Seq Scan on projects (cost=0.00..2.30 rows=9 width=8) (actual time=0.010..0.016 rows=9 loops=1)

  • Filter: (id = ANY ('{55,56,47,73,48,59,54,67,61}'::integer[]))
  • Rows Removed by Filter: 52
24. 0.246 0.246 ↑ 3.0 4 82

Index Scan using farms_version_id_idx on farms fa (cost=0.28..1.19 rows=12 width=8) (actual time=0.003..0.003 rows=4 loops=82)

  • Index Cond: (v.id = version_id)
25. 6.273 6.273 ↓ 2.6 73 369

Index Scan using fields_farm_id_idx on fields fi (cost=0.29..5.28 rows=28 width=16) (actual time=0.003..0.017 rows=73 loops=369)

  • Index Cond: (fa.id = farm_id)
26. 6.769 522.342 ↓ 2.0 36,339 1

Hash (cost=18,516.80..18,516.80 rows=17,933 width=8) (actual time=522.342..522.342 rows=36,339 loops=1)

  • Buckets: 65,536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 1,932kB
27. 11.836 515.573 ↓ 2.0 36,339 1

Subquery Scan on rf1 (cost=17,261.49..18,516.80 rows=17,933 width=8) (actual time=350.678..515.573 rows=36,339 loops=1)

28. 29.124 503.737 ↓ 2.0 36,339 1

Unique (cost=17,261.49..18,337.47 rows=17,933 width=371) (actual time=350.674..503.737 rows=36,339 loops=1)

29. 223.571 474.613 ↓ 2.0 36,339 1

Sort (cost=17,261.49..17,306.32 rows=17,933 width=371) (actual time=350.673..474.613 rows=36,339 loops=1)

  • Sort Key: r.id, r.selected, r.selected_by_user, r.pixel_id, r.centroid_y, r.centroid_x, r.intersect_idx, f.id, f.name, f.date_from, f.date_to, fa_1.id, fa_1.name, pre.id, v_1.name, pr_1.name, c.name, u_1.name, u.id, u.name, ((dc.date_from)::date), ((dc.date_to)::date), r.the_geom
  • Sort Method: external merge Disk: 9,984kB
30. 19.507 251.042 ↓ 2.0 36,339 1

Hash Left Join (cost=7,930.77..12,928.49 rows=17,933 width=371) (actual time=135.164..251.042 rows=36,339 loops=1)

  • Hash Cond: (dc.use_concrete_id = u.id)
31. 85.224 231.489 ↓ 2.0 36,339 1

Hash Right Join (cost=7,926.05..12,785.38 rows=17,933 width=361) (actual time=135.101..231.489 rows=36,339 loops=1)

  • Hash Cond: (dc.declaration_id = de.id)
32. 23.850 23.850 ↑ 1.0 114,982 1

Seq Scan on declarations_concrete dc (cost=0.00..2,173.82 rows=114,982 width=24) (actual time=0.013..23.850 rows=114,982 loops=1)

33. 29.313 122.415 ↓ 2.3 36,339 1

Hash (cost=7,001.15..7,001.15 rows=15,832 width=345) (actual time=122.415..122.415 rows=36,339 loops=1)

  • Buckets: 16,384 (originally 16384) Batches: 4 (originally 2) Memory Usage: 3,969kB
34. 39.938 93.102 ↓ 2.3 36,339 1

Hash Right Join (cost=4,699.05..7,001.15 rows=15,832 width=345) (actual time=50.016..93.102 rows=36,339 loops=1)

  • Hash Cond: (de.field_id = f.id)
35. 9.653 9.653 ↑ 1.0 101,511 1

Seq Scan on declarations de (cost=0.00..1,763.11 rows=101,511 width=8) (actual time=0.008..9.653 rows=101,511 loops=1)

36. 5.960 43.511 ↑ 1.0 10,037 1

Hash (cost=4,573.59..4,573.59 rows=10,037 width=341) (actual time=43.511..43.511 rows=10,037 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 2,857kB
37. 7.129 37.551 ↑ 1.0 10,037 1

Hash Left Join (cost=3,875.72..4,573.59 rows=10,037 width=341) (actual time=19.947..37.551 rows=10,037 loops=1)

  • Hash Cond: (f.farm_id = fa_1.id)
38. 4.475 29.420 ↑ 1.0 10,037 1

Hash Left Join (cost=3,782.64..4,454.12 rows=10,037 width=330) (actual time=18.910..29.420 rows=10,037 loops=1)

  • Hash Cond: (r.process_id = pre.id)
39. 6.221 24.223 ↑ 1.0 10,037 1

Merge Right Join (cost=3,736.74..4,270.22 rows=10,037 width=169) (actual time=18.170..24.223 rows=10,037 loops=1)

  • Merge Cond: (f.id = r.field_id)
40. 8.481 8.481 ↑ 2.1 30,310 1

Index Scan using fields_pk on fields f (cost=0.29..6,391.59 rows=64,355 width=23) (actual time=0.015..8.481 rows=30,310 loops=1)

41. 5.873 9.521 ↑ 1.0 10,037 1

Sort (cost=1,031.48..1,056.57 rows=10,037 width=150) (actual time=8.667..9.521 rows=10,037 loops=1)

  • Sort Key: r.field_id
  • Sort Method: quicksort Memory: 2,999kB
42. 3.648 3.648 ↑ 1.0 10,037 1

Seq Scan on results r (cost=0.00..364.37 rows=10,037 width=150) (actual time=0.015..3.648 rows=10,037 loops=1)

43. 0.088 0.722 ↑ 1.0 287 1

Hash (cost=42.31..42.31 rows=287 width=165) (actual time=0.721..0.722 rows=287 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 34kB
44. 0.119 0.634 ↑ 1.0 287 1

Hash Join (cost=31.18..42.31 rows=287 width=165) (actual time=0.257..0.634 rows=287 loops=1)

  • Hash Cond: (pr_1.client_id = c.id)
45. 0.102 0.499 ↑ 1.0 287 1

Hash Join (cost=12.18..22.54 rows=287 width=51) (actual time=0.217..0.499 rows=287 loops=1)

  • Hash Cond: (v_1.project_id = pr_1.id)
46. 0.098 0.362 ↑ 1.0 287 1

Hash Join (cost=9.81..19.36 rows=287 width=36) (actual time=0.166..0.362 rows=287 loops=1)

  • Hash Cond: (pre.version_id = v_1.id)
47. 0.103 0.165 ↑ 1.0 287 1

Hash Join (cost=1.43..10.22 rows=287 width=20) (actual time=0.049..0.165 rows=287 loops=1)

  • Hash Cond: (pre.user_id = u_1.id)
48. 0.045 0.045 ↑ 1.0 287 1

Seq Scan on process_results pre (cost=0.00..7.87 rows=287 width=12) (actual time=0.011..0.045 rows=287 loops=1)

49. 0.007 0.017 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=16) (actual time=0.017..0.017 rows=19 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
50. 0.010 0.010 ↑ 1.0 19 1

Seq Scan on users u_1 (cost=0.00..1.19 rows=19 width=16) (actual time=0.007..0.010 rows=19 loops=1)

51. 0.052 0.099 ↑ 1.0 239 1

Hash (cost=5.39..5.39 rows=239 width=24) (actual time=0.099..0.099 rows=239 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
52. 0.047 0.047 ↑ 1.0 239 1

Seq Scan on versions v_1 (cost=0.00..5.39 rows=239 width=24) (actual time=0.007..0.047 rows=239 loops=1)

53. 0.018 0.035 ↑ 1.0 61 1

Hash (cost=1.61..1.61 rows=61 width=23) (actual time=0.035..0.035 rows=61 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
54. 0.017 0.017 ↑ 1.0 61 1

Seq Scan on projects pr_1 (cost=0.00..1.61 rows=61 width=23) (actual time=0.008..0.017 rows=61 loops=1)

55. 0.008 0.016 ↑ 16.7 24 1

Hash (cost=14.00..14.00 rows=400 width=122) (actual time=0.016..0.016 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
56. 0.008 0.008 ↑ 16.7 24 1

Seq Scan on clients c (cost=0.00..14.00 rows=400 width=122) (actual time=0.005..0.008 rows=24 loops=1)

57. 0.553 1.002 ↑ 1.0 2,848 1

Hash (cost=57.48..57.48 rows=2,848 width=15) (actual time=1.002..1.002 rows=2,848 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 165kB
58. 0.449 0.449 ↑ 1.0 2,848 1

Seq Scan on farms fa_1 (cost=0.00..57.48 rows=2,848 width=15) (actual time=0.008..0.449 rows=2,848 loops=1)

59. 0.021 0.046 ↑ 1.0 121 1

Hash (cost=3.21..3.21 rows=121 width=22) (actual time=0.046..0.046 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
60. 0.025 0.025 ↑ 1.0 121 1

Seq Scan on use_concretes u (cost=0.00..3.21 rows=121 width=22) (actual time=0.008..0.025 rows=121 loops=1)

Planning time : 5.732 ms
Execution time : 585.245 ms