Holy Water, because it has been blessed, has efficacy independent of the faith of the one using the sacramental. Of course, the efficacy of the Holy Water does not come from the water itself but from the prayers attached to it. Using Holy Water, for example by sprinkling it on the grave of a departed loved one, is not necessarily superstitious if one understands that the water itself is not helping the person but the prayer of the Church that the water has been imbued with.