How to force Postgres to use an index?

Question

I have the following query that finds all id inside POLYGON(({points}))

SELECT id
FROM t
WHERE ST_DWithin('POLYGON(({points}))', ST_Point(latitude, longitude), 0);

The query is slow because it doesn't use the (latitude, longitude) index and must compute the formula for every possible pair of points.

How can I change my query to force Postgres to use the (latitude, longitude) index (I need to change the query because I cannot add some other index or create/change the table)?

I have the following index:

"index_latitude_longitude" btree (latitude, longitude)

latitude, longitude has double precision type.

I think that Postgres will use the index if we add something like: latitude <= ... and longitude <= .... How can we do this?

Answer 1

You should be able to create an index on ST_Point(longitude, latitude):

CREATE INDEX index_longitude_latitude
ON t
USING GIST (ST_Point(longitude, latitude))

---

Update: (Updated again after suggestion from @JGH)

As you commented, you have no write access to the database, there is another possibility to use the existing Indexes to filter the possible points using the Minimum Bounding Rectangle (MBR). Then use ST_DWithin on the rest (no indexes are used).

SELECT id
FROM t, ( SELECT ST_GeomFromText('POLYGON(({points}))') as geom ) as polygon
WHERE longitude >= ST_XMIN(polygon.geom)
AND longitude <= ST_XMAX(polygon.geom)
AND latitude >= ST_YMIN(polygon.geom)
AND latitude <= ST_YMAX(polygon.geom)
AND ST_DWithin(polygon.geom, ST_Point(longitude, latitude), 0);

I would (still) suggest not doing this but rather asking the database owner to add the index from above.