Using Postgis spatial index in st_distance operation based upon explicit SRID column

Question

I have two large tables with data that spans over multiple regions of the U.S and needs a defined SRID column in atleast one the tables to denote which coordinate system should be used for area/distance calculations:

CREATE TABLE table_a(
  id uuid,
  geom geometry(MultiPolygon,4326)
);
CREATE INDEX ON table_a USING GIST (geom);
CREATE INDEX ON table_a USING GIST (st_transform(geom,2876));
CREATE INDEX ON table_a USING GIST (st_transform(geom,2877));
CREATE INDEX ON table_a USING GIST (st_transform(geom,2926));
CREATE INDEX ON table_a USING GIST (st_transform(geom,...));

CREATE TABLE table_b(
  id uuid,
  geom geometry(MultiPolygon,4326)
);
CREATE INDEX ON table_b USING GIST (geom);
CREATE INDEX ON table_b USING GIST (st_transform(geom,2876));
CREATE INDEX ON table_b USING GIST (st_transform(geom,2877));
CREATE INDEX ON table_b USING GIST (st_transform(geom,2926));
CREATE INDEX ON table_b USING GIST (st_transform(geom,...));

--note there can be multiple applicable SRIDs for a given geometry
CREATE TABLE table_a_srid(
  id uuid,
  table_a_id uuid REFERENCES table_a,
  srid integer
);
CREATE INDEX ON table_a_srid(table_a_id)

The following query is designed to apprehend the closest record of table_b for each record of table_a within a distance of 500ft using the SRID from table_a_srid:

SELECT DISTINCT ON (a.id)

  a.id,
  b.geom

FROM

  table_a a
  JOIN table_a_srid c ON (a.id = c.table_a_id)
  JOIN table_b b ON (st_dwithin(st_transform(a.geom,c.srid),st_transform(b.geom,c.srid),500))

ORDER BY

  a.id, st_distance(st_transform(a.geom.c.srid),st_transform(b.geom,c.srid))

Query plan shows that a sequential scan is used instead of the relevant spatial transformation index for the given SRID -resulting in completely unacceptable performance.

How can I perform this operation efficiently without resorting to UNION ALL for every single SRID defined?

postgis_full_version = "POSTGIS="2.2.0dev r12887" GEOS="3.5.0dev-CAPI-1.9.0 r3995" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.9.1" LIBJSON="0.11.99" TOPOLOGY RASTER"

Answer 1

I think that your index does not work if the geometry is transformed (temporarily) in your join statement. It is in general a bad idea to use the transform function in such an extensive manner since it is computational very expensive. If you know which SRIDs you are going to use, and you know that your table would not get too big, it would be better to create multiple geometry columns a-priori. I am sure that also your index would work than.