explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7m9k

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=20,050.79..21,654.12 rows=1 width=4) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=20,050.79..21,654.12 rows=1 width=4) (actual rows= loops=)

  • Join Filter: ((fi.id = f.id) AND (fa_1.id = fa.id))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,789.30..2,868.33 rows=1 width=12) (actual rows= loops=)

  • Join Filter: (projects.client_id = clients.id)
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,780.99..2,854.40 rows=9 width=16) (actual rows= loops=)

  • Join Filter: (tsrange((fi.date_from)::timestamp without time zone, (fi.date_to)::timestamp without time zone) @> (dbpf2.date)::timestamp without time zone)
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,780.70..2,801.24 rows=9 width=16) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,780.42..2,783.45 rows=1 width=16) (actual rows= loops=)

  • Merge Cond: (pf.id = dbpf2.process_f2_id)
7. 0.000 0.000 ↓ 0.0

Sort (cost=60.73..60.74 rows=1 width=12) (actual rows= loops=)

  • Sort Key: pf.id
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=13.94..60.72 rows=1 width=12) (actual rows= loops=)

  • Join Filter: (pf.map_id = re.map_id)
9. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = 1,071)
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=13.79..52.51 rows=4 width=16) (actual rows= loops=)

  • Hash Cond: (pf.process_result_id = pr.id)
11. 0.000 0.000 ↓ 0.0

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

12. 0.000 0.000 ↓ 0.0

Hash (cost=13.78..13.78 rows=1 width=12) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.56..13.78 rows=1 width=12) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.41..9.61 rows=1 width=8) (actual rows= loops=)

  • Hash Cond: (v.project_id = projects.id)
15. 0.000 0.000 ↓ 0.0

Seq Scan on versions v (cost=0.00..7.18 rows=6 width=8) (actual rows= loops=)

  • Filter: (id = ANY ('{283,302,300,228,247,227}'::integer[]))
16. 0.000 0.000 ↓ 0.0

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

17. 0.000 0.000 ↓ 0.0

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

  • Filter: (id = ANY ('{55,56,47,73,48,59,54,67,61}'::integer[]))
18. 0.000 0.000 ↓ 0.0

Index Scan using process_results_version_id_idx on process_results pr (cost=0.15..4.17 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (version_id = v.id)
19. 0.000 0.000 ↓ 0.0

Sort (cost=2,719.69..2,721.19 rows=602 width=8) (actual rows= loops=)

  • Sort Key: dbpf2.process_f2_id
20. 0.000 0.000 ↓ 0.0

Seq Scan on date_by_process_f2 dbpf2 (cost=0.00..2,691.89 rows=602 width=8) (actual rows= loops=)

  • Filter: ((date)::timestamp without time zone <@ '["2019-06-01 00:00:00","2020-06-30 00:00:00"]'::tsrange)
21. 0.000 0.000 ↓ 0.0

Index Scan using farms_version_id_idx on farms fa (cost=0.28..17.67 rows=12 width=8) (actual rows= loops=)

  • Index Cond: (v.id = version_id)
22. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (fa.id = farm_id)
23. 0.000 0.000 ↓ 0.0

Materialize (cost=8.31..13.66 rows=2 width=4) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on clients (cost=8.31..13.65 rows=2 width=4) (actual rows= loops=)

  • Recheck Cond: (id = ANY ('{21,22}'::integer[]))
25. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on clients_pk (cost=0.00..8.31 rows=2 width=0) (actual rows= loops=)

  • Index Cond: (id = ANY ('{21,22}'::integer[]))
26. 0.000 0.000 ↓ 0.0

Unique (cost=17,261.49..18,337.47 rows=17,933 width=371) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=17,261.49..17,306.32 rows=17,933 width=371) (actual rows= loops=)

  • 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
28. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,930.77..12,928.49 rows=17,933 width=371) (actual rows= loops=)

  • Hash Cond: (dc.use_concrete_id = u.id)
29. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=7,926.05..12,785.38 rows=17,933 width=361) (actual rows= loops=)

  • Hash Cond: (dc.declaration_id = de.id)
30. 0.000 0.000 ↓ 0.0

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

31. 0.000 0.000 ↓ 0.0

Hash (cost=7,001.15..7,001.15 rows=15,832 width=345) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=4,699.05..7,001.15 rows=15,832 width=345) (actual rows= loops=)

  • Hash Cond: (de.field_id = f.id)
33. 0.000 0.000 ↓ 0.0

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

34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

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

  • Hash Cond: (f.farm_id = fa_1.id)
36. 0.000 0.000 ↓ 0.0

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

  • Hash Cond: (r.process_id = pre.id)
37. 0.000 0.000 ↓ 0.0

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

  • Merge Cond: (f.id = r.field_id)
38. 0.000 0.000 ↓ 0.0

Index Scan using fields_pk on fields f (cost=0.29..6,391.59 rows=64,355 width=23) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

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

  • Sort Key: r.field_id
40. 0.000 0.000 ↓ 0.0

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

41. 0.000 0.000 ↓ 0.0

Hash (cost=42.31..42.31 rows=287 width=165) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash Join (cost=31.18..42.31 rows=287 width=165) (actual rows= loops=)

  • Hash Cond: (pr_1.client_id = c.id)
43. 0.000 0.000 ↓ 0.0

Hash Join (cost=12.18..22.54 rows=287 width=51) (actual rows= loops=)

  • Hash Cond: (v_1.project_id = pr_1.id)
44. 0.000 0.000 ↓ 0.0

Hash Join (cost=9.81..19.36 rows=287 width=36) (actual rows= loops=)

  • Hash Cond: (pre.version_id = v_1.id)
45. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.43..10.22 rows=287 width=20) (actual rows= loops=)

  • Hash Cond: (pre.user_id = u_1.id)
46. 0.000 0.000 ↓ 0.0

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

47. 0.000 0.000 ↓ 0.0

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

48. 0.000 0.000 ↓ 0.0

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

49. 0.000 0.000 ↓ 0.0

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

50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

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

52. 0.000 0.000 ↓ 0.0

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

53. 0.000 0.000 ↓ 0.0

Hash (cost=14.00..14.00 rows=400 width=122) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Seq Scan on clients c (cost=0.00..14.00 rows=400 width=122) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

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

56. 0.000 0.000 ↓ 0.0

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

57. 0.000 0.000 ↓ 0.0

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

58. 0.000 0.000 ↓ 0.0

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