4

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. EPSG:2154.

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).

2
  • 3
    I don't have a postgis database handy on my current machine but I think you could check something like this by inspecting the execution plan by prepending the query with EXPLAIN (postgresql.org/docs/13/sql-explain.html)
    – wfgeo
    Sep 24 at 22:34
  • 3
    While possible, and indeed useful for singular cases, having many (functional) indexes on the same column(s) is in many ways something to avoid. Naturally, each of them need a physical storage and equal resources to maintain and update. Additionally, transformations may not be lossless. I suggest to instead only ever transform to a (close or underlying) geographic CRS (e.g. EPSG:4326) and use the GEOGRAPHY type, either directly as column, or via a single functional index (... ON (geom::GEOGRAPHY);).
    – geozelot
    Sep 25 at 7:37
4

No, the index would be considered, as you invoke a matching expression, and that expression will get executed with all consequences (luckily ST_Transform would make an early exit in this case).

An index on an expression stores the computed value of the given expression, not the computation. It doesn't care if ST_Transform internally determines that it won't need to do anything, and just returns a copy of the input geometry as its retrun value.

Furthermore, PG expects a new object of type GEOMETRY from that expression, so the 'vanilla' index cannot get considered!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.