explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UIfw : spo

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

Nested Loop (cost=68,329.28..69,729.85 rows=1 width=89) (actual rows= loops=)

  • Join Filter: ((ft.codice_dataset)::text = (ft2.codice_dataset)::text)
2. 0.000 0.000 ↓ 0.0

Merge Join (cost=68,329.15..69,729.68 rows=1 width=89) (actual rows= loops=)

  • Merge Cond: ((fm.reporting_year = fm2.reporting_year) AND (fm.region_fk = fm2.region_fk))
  • Join Filter: (fm2.feature_member_version = fm.feature_member_version)
3. 0.000 0.000 ↓ 0.0

Unique (cost=20,581.58..21,960.04 rows=1,470 width=38) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=20,581.58..20,926.20 rows=137,846 width=38) (actual rows= loops=)

  • Sort Key: fm.reporting_year, fm.region_fk, ft.codice_dataset, fm.feature_member_version DESC
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.23..5,045.07 rows=137,846 width=38) (actual rows= loops=)

  • Hash Cond: ((fm.feature_member_type)::text = (ft.codice)::text)
6. 0.000 0.000 ↓ 0.0

Seq Scan on feature_members fm (cost=0.00..3,148.46 rows=137,846 width=18) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash (cost=1.10..1.10 rows=10 width=48) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on feature_member_types ft (cost=0.00..1.10 rows=10 width=48) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=47,747.57..47,747.57 rows=1 width=73) (actual rows= loops=)

  • Sort Key: fm2.reporting_year, fm2.region_fk
10. 0.000 0.000 ↓ 0.0

Merge Join (cost=47,747.53..47,747.56 rows=1 width=73) (actual rows= loops=)

  • Merge Cond: ((fm2.feature_member_version = (max(fm2_2.feature_member_version))) AND (fm2.local_id = fm2_2.local_id))
11. 0.000 0.000 ↓ 0.0

Sort (cost=24,647.18..24,647.19 rows=1 width=143) (actual rows= loops=)

  • Sort Key: fm2.feature_member_version, fm2.local_id
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=24,554.99..24,647.17 rows=1 width=143) (actual rows= loops=)

  • Join Filter: (q1.local_id = fm2.local_id)
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=24,554.57..24,643.05 rows=1 width=82) (actual rows= loops=)

  • Hash Cond: (f.local_id = q1.local_id)
14. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,454.23..1,491.48 rows=3,725 width=39) (actual rows= loops=)

  • Group Key: f.local_id
15. 0.000 0.000 ↓ 0.0

Merge Join (cost=940.21..1,444.92 rows=3,725 width=39) (actual rows= loops=)

  • Merge Cond: (f.feature_member_id = s.feature_member_fk)
16. 0.000 0.000 ↓ 0.0

Index Scan using feature_members_feature_member_id_idx on feature_members f (cost=0.42..10,505.07 rows=137,846 width=43) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=526.11..535.45 rows=3,736 width=4) (actual rows= loops=)

  • Sort Key: s.feature_member_fk
18. 0.000 0.000 ↓ 0.0

Seq Scan on sampling_points s (cost=0.00..304.43 rows=3,736 width=4) (actual rows= loops=)

  • Filter: sampling_point_used_aqd
19. 0.000 0.000 ↓ 0.0

Hash (cost=23,100.33..23,100.33 rows=1 width=43) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Subquery Scan on q1 (cost=23,100.31..23,100.33 rows=1 width=43) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

HashAggregate (cost=23,100.31..23,100.32 rows=1 width=43) (actual rows= loops=)

  • Group Key: fm2_1.local_id
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=20,720.14..23,100.31 rows=1 width=43) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=20,581.58..21,997.92 rows=7 width=38) (actual rows= loops=)

  • Join Filter: ((ft2_1.codice_dataset)::text = (ft_1.codice_dataset)::text)
24. 0.000 0.000 ↓ 0.0

Unique (cost=20,581.58..21,960.04 rows=1,470 width=38) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=20,581.58..20,926.20 rows=137,846 width=38) (actual rows= loops=)

  • Sort Key: fm_1.reporting_year, fm_1.region_fk, ft_1.codice_dataset, fm_1.feature_member_version DESC
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.23..5,045.07 rows=137,846 width=38) (actual rows= loops=)

  • Hash Cond: ((fm_1.feature_member_type)::text = (ft_1.codice)::text)
27. 0.000 0.000 ↓ 0.0

Seq Scan on feature_members fm_1 (cost=0.00..3,148.46 rows=137,846 width=18) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=1.10..1.10 rows=10 width=48) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on feature_member_types ft_1 (cost=0.00..1.10 rows=10 width=48) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.13 rows=1 width=48) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on feature_member_types ft2_1 (cost=0.00..1.12 rows=1 width=48) (actual rows= loops=)

  • Filter: ((codice)::text = 'SPO'::text)
32. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on feature_members fm2_1 (cost=138.56..157.29 rows=19 width=57) (actual rows= loops=)

  • Recheck Cond: ((region_fk = fm_1.region_fk) AND (feature_member_version = fm_1.feature_member_version))
  • Filter: (((feature_member_type)::text = 'SPO'::text) AND (fm_1.reporting_year = reporting_year))
33. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=138.56..138.56 rows=547 width=0) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on feature_members_region_fk_idx (cost=0.00..50.69 rows=6,564 width=0) (actual rows= loops=)

  • Index Cond: (region_fk = fm_1.region_fk)
35. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on feature_members_feature_member_version_idx (cost=0.00..87.61 rows=11,487 width=0) (actual rows= loops=)

  • Index Cond: (feature_member_version = fm_1.feature_member_version)
36. 0.000 0.000 ↓ 0.0

Index Scan using feature_members_local_id_idx on feature_members fm2 (cost=0.42..4.07 rows=4 width=61) (actual rows= loops=)

  • Index Cond: (local_id = f.local_id)
  • Filter: (local_id <> ALL ('{SPO.IT0502A_9_chemi_2005-12-08_00:00:00,SPO.IT1678A_38_chemi_2004-01-13_00:00:00,SPO.IT1678A_9_chemi_2004-01-13_00:00:00,SPO.IT2279A_38_chemi_2018-01-01_00:00:00,SPO.IT2279A_8_chemi_2018-06-06_00:00:00,SPO.IT2279A_9_chemi_2018-06-06_00:00:00,SPO.IT0502A_38_chemi_2005-12-08_00:00:00,SPO.IT0507A_9_chemi_2004-01-01_00:00:00,SPO.IT1800A_21_other_2006-02-01_00:00:00,SPO.IT1802A_21_other_2006-02-01_00:00:00,SPO.IT1802A_482_other_2006-02-01_00:00:00,SPO.IT1800A_482_other_2006-02-01_00:00:00,SPO.IT1537A_38_chemi_2010-06-15_00:00:00,SPO.IT1137A_10_IR-GFC_1997-09-02_00:00:00,SPO.IT1137A_38_chemi_1997-09-02_00:00:00,SPO.IT1137A_5_BETA_2002-02-19_00:00:00,SPO.IT1137A_8_chemi_1997-09-02_00:00:00,SPO.IT2280A_38_chemi_2017-05-24_00:00:00,SPO.IT2280A_6001_BETA_2017-05-23_00:00:00,SPO.IT1905A_5_gravi_2009-12-30_00:00:00,SPO.IT1041A_38_chemi_1996-03-01_00:00:00,SPO.IT1487A_9_chemi_2015-09-01_00:00:00,SPO.IT1488A_38_chemi_1996-03-02_00:00:00,SPO.IT1488A_9_chemi_2015-08-19_19:00:00,SPO.IT1496A_38_chemi_1994-01-02_00:00:00,SPO.IT1041A_9_chemi_1996-03-01_00:00:00,SPO.IT1488A_38_chemi_2015-08-19_19:00:00,SPO.IT0934A_38_chemi_1994-01-02_00:00:00,SPO.IT0934A_9_chemi_1994-01-02_00:00:00,SPO.IT1486A_38_chemi_1994-07-02_00:00:00,SPO.IT2198A_1_UV-FL_2014-05-23_00:00:00,SPO.IT2197A_1_UV-FL_2014-05-26_00:00:00,SPO.IT1486A_9_chemi_1994-07-02_00:00:00,SPO.IT2199A_1_UV-FL_2014-02-14_00:00:00,SPO.IT2196A_1_UV-FL_2014-07-17_00:00:00,SPO.IT1041A_5018_GF-AAS_1996-03-01_00:00:00,SPO.IT1041A_5015_GF-AAS_1996-03-01_00:00:00,SPO.IT0898A_9_chemi_1993-06-02_00:00:00,SPO.IT1496A_9_chemi_1994-01-02_00:00:00,SPO.IT1041A_5014_GF-AAS_1996-03-01_00:00:00,SPO.IT0898A_38_chemi_1993-06-02_00:00:00,SPO.IT1041A_12_GF-AAS_1996-03-01_00:00:00,SPO.IT1041A_5029_HPLC-FLD_1996-03-01_00:00:00,SPO.IT2277A_1_UV-FL_2017-09-01_00:00:00,SPO.IT2277A_10_NDIR_2017-09-01_00:00:00,SPO.IT1491A_6001_BETA_2016-03-09_20:00:00,SPO.IT1476A_4_BETA_2004-01-01_00:00:00,SPO.IT1477A_4_BETA_2004-01-09_00:00:00,SPO.IT1476A_38_chemi_2001-12-12_00:00:00,SPO.IT1477A_38_chemi_2001-12-12_00:00:00,SPO.IT1475A_4_BETA_2005-01-01_00:00:00,SPO.IT1882A_5_BETA_2008-01-01_00:00:00,SPO.IT0441A_8_chemi_2015-01-01_00:00:00,SPO.IT0441A_9_chemi_2015-01-01_00:00:00,SPO.IT0441A_7_UV-P_2015-01-01_00:00:00,SPO.IT0441A_38_chemi_2015-01-01_00:00:00,SPO.IT0441A_10_NDIR_2015-01-01_00:00:00,SPO.IT0441A_5_BETA_2015-01-01_00:00:00,SPO.IT1343A_38_chemi_1994-01-02_00:00:00,SPO.IT1343A_9_chemi_1994-01-02_00:00:00,SPO.IT2183A_9_chemi_2014-09-26_00:00:00,SPO.IT1553A_38_chemi_2000-12-01_00:00:00,SPO.IT1553A_9_chemi_2000-12-01_00:00:00,SPO.IT1654A_9_chemi_1997-11-04_00:00:00,SPO.IT2183A_38_chemi_2014-09-26_00:00:00,SPO.IT1592A_38_chemi_2016-01-14_00:00:00,SPO.IT2154A_9_chemi_2014-04-10_00:00:00,SPO.IT2154A_38_chemi_2014-04-10_00:00:00,SPO.IT2153A_9_chemi_2014-01-01_00:00:00,SPO.IT0860A_9_chemi_1992-12-18_00:00:00,SPO.IT0862A_9_chemi_1992-12-22_00:00:00,SPO.IT2153A_38_chemi_2014-01-01_00:00:00,SPO.IT0860A_38_chemi_1992-12-18_00:00:00,SPO.IT1110A_38_chemi_1998-03-10_00:00:00,SPO.IT0063A_9_chemi_2000-04-16_00:00:00,SPO.IT1560A_38_chemi_2002-01-15_00:00:00,SPO.IT0862A_38_chemi_1992-12-22_00:00:00,SPO.IT1557A_9_chemi_2002-01-01_00:00:00,SPO.IT1557A_38_chemi_2002-01-01_00:00:00,SPO.IT1571A_9_chemi_2000-12-01_00:00:00,SPO.IT1654A_38_chemi_1997-11-04_00:00:00,SPO.IT0063A_38_chemi_2000-04-16_00:00:00,SPO.IT1592A_9_chemi_2016-01-14_00:00:00,SPO.IT1110A_9_chemi_1998-03-10_00:00:00,SPO.IT2264A_8_chemi_2012-05-06_00:00:00,SPO.IT2255A_8_chemi_2018-06-04_00:00:00,SPO.IT2257A_5_BETA_2016-05-26_00:00:00,SPO.IT0524A_38_chemi_1984-12-10_00:00:00,SPO.IT0524A_8_chemi_1984-12-10_00:00:00,SPO.IT0524A_7_UV-P_2006-01-01_00:00:00,SPO.IT0524A_9_chemi_1984-12-10_00:00:00,SPO.IT0623A_7_UV-P_1989-10-01_00:00:00,SPO.IT0524A_1_UV-FL_1983-01-01_00:00:00,SPO.IT2162A_5_BETA_2017-01-01_00:00:00,SPO.IT0852A_9_chemi_1995-01-01_00:00:00,SPO.IT0858A_9_chemi_1993-08-01_00:00:00,SPO.IT0858A_431_other_2008-01-01_00:00:00,SPO.IT0858A_482_other_2008-01-01_00:00:00,SPO.IT0858A_81_other_2012-01-01_00:00:00,SPO.IT0858A_21_other_1998-05-05_00:00:00,SPO.IT0858A_80_other_2012-01-01_00:00:00,SPO.IT1465A_7_UV-P_1997-10-01_00:00:00,SPO.IT1718A_5012_spectro_2013-01-01_00:00:00,SPO.IT1718A_5015_spectro_2013-01-01_00:00:00,SPO.IT1718A_5014_spectro_2013-01-01_00:00:00,SPO.IT1718A_5018_spectro_2013-01-01_00:00:00,SPO.IT0983A_21_FID_1996-06-01_00:00:00,SPO.IT0983A_78_FID_1996-06-01_00:00:00,SPO.IT1029A_431_GC-FID_2017-03-01_00:00:00,SPO.IT1159A_431_GC-FID_2017-03-01_00:00:00,SPO.IT1587A_10_IR-GFC_2003-09-17_00:00:00,SPO.IT1393A_7_UV-P_1999-09-01_00:00:00}'::text[]))
37. 0.000 0.000 ↓ 0.0

Sort (cost=23,100.34..23,100.35 rows=1 width=47) (actual rows= loops=)

  • Sort Key: (max(fm2_2.feature_member_version)), fm2_2.local_id
38. 0.000 0.000 ↓ 0.0

HashAggregate (cost=23,100.31..23,100.32 rows=1 width=47) (actual rows= loops=)

  • Group Key: fm2_2.local_id
39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=20,720.14..23,100.31 rows=1 width=47) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=20,581.58..21,997.92 rows=7 width=38) (actual rows= loops=)

  • Join Filter: ((ft2_2.codice_dataset)::text = (ft_2.codice_dataset)::text)
41. 0.000 0.000 ↓ 0.0

Unique (cost=20,581.58..21,960.04 rows=1,470 width=38) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Sort (cost=20,581.58..20,926.20 rows=137,846 width=38) (actual rows= loops=)

  • Sort Key: fm_2.reporting_year, fm_2.region_fk, ft_2.codice_dataset, fm_2.feature_member_version DESC
43. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.23..5,045.07 rows=137,846 width=38) (actual rows= loops=)

  • Hash Cond: ((fm_2.feature_member_type)::text = (ft_2.codice)::text)
44. 0.000 0.000 ↓ 0.0

Seq Scan on feature_members fm_2 (cost=0.00..3,148.46 rows=137,846 width=18) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash (cost=1.10..1.10 rows=10 width=48) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on feature_member_types ft_2 (cost=0.00..1.10 rows=10 width=48) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.13 rows=1 width=48) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on feature_member_types ft2_2 (cost=0.00..1.12 rows=1 width=48) (actual rows= loops=)

  • Filter: ((codice)::text = 'SPO'::text)
49. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on feature_members fm2_2 (cost=138.56..157.29 rows=19 width=57) (actual rows= loops=)

  • Recheck Cond: ((region_fk = fm_2.region_fk) AND (feature_member_version = fm_2.feature_member_version))
  • Filter: (((feature_member_type)::text = 'SPO'::text) AND (fm_2.reporting_year = reporting_year))
50. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=138.56..138.56 rows=547 width=0) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on feature_members_region_fk_idx (cost=0.00..50.69 rows=6,564 width=0) (actual rows= loops=)

  • Index Cond: (region_fk = fm_2.region_fk)
52. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on feature_members_feature_member_version_idx (cost=0.00..87.61 rows=11,487 width=0) (actual rows= loops=)

  • Index Cond: (feature_member_version = fm_2.feature_member_version)
53. 0.000 0.000 ↓ 0.0

Index Scan using feature_member_types_codice_idx on feature_member_types ft2 (cost=0.14..0.15 rows=1 width=48) (actual rows= loops=)

  • Index Cond: ((codice)::text = (fm2.feature_member_type)::text)