I have such vanilla index on a PostGIS table:
CREATE INDEX polygons_gix ON public.polygons USING gist (geom);
Because of this:
If using more than one transformation, it is useful to have a functional index on the commonly used transformations to take advantage of index usage. https://postgis.net/docs/ST_Transform.html
I also need:
CREATE INDEX polygons_gix ON public.polygons USING gist (ST_Transform(geom, XXXX)); -- where XXXX is any EPSG code for your transform
Now, depending on the local CRS, I need many of these transforms, (which I may not know in advance but this is another problem... so let's say I know them; I can write 40x that Index for 40 regions having 40 different CRS).
To reduce this story to a minimum, I have to make it 'stupid', in a way: so let's say, my geometries are given for a "master" region which serves as the default, e.g.
Would the following index be evaluated or not? (and if yes, will this consume some extra time or resources?)
CREATE INDEX polygons_gix ON public.polygons USING gist (ST_Transform(geom, 2154));
I know this story is nonsense, so this is more out of curiosity. I already fixed the code that led me to this strange situation when I noticed it (the EPSG code value was injected automatically).