I'm attempting to perform a query on some foreign spatial tables which is taking several orders of magnitude longer than running on the normal spatial table because the index is not used.

Indexes seem to be working on non-spatial queries.

Is this something that should work or is this not supported?

  • 2
    Obviously, you need to provide your version of Postgres and PostGIS (local and foreign). And it would help a lot to provide query and table definition as well. Commented Jun 26, 2017 at 15:52

1 Answer 1


Assuming at least Postgres 9.6 for local and remote server, and the same version of PostGIS installed, running this on the local server should do the trick:

ALTER SERVER your_foreign_server_name OPTIONS (ADD extensions 'postgis');

Postgres can use indexes on a remote Postgres server. You may have to do more than for local tables, autovacuum does not run ANALYZE for remote tables automatically. Start by reading the chapter "Remote Query Optimization" in the manual.

Related answer with more details:

Since PostGIS is an extension, this new feature of Postgres 9.6 is particularly relevant:

Allow extension-provided operators and functions to be sent for remote execution, if the extension is whitelisted in the foreign server's options (Paul Ramsey)

Users can enable this feature when the extension is known to exist in a compatible version in the remote database. It allows more efficient execution of queries involving extension operators.

Bold emphasis mine.

The use of indexes is bound to operators. Various PostGIS spatial functions are transformed to use operators internally when the query is planned. This related answer on SO has more explanation:

  • 1
    It is hard to put into words how amazing this development is. I have had queries using full scans of large tables on foreign Postgres boxes go from many hours to seconds thanks to this. Commented Oct 17, 2018 at 10:28
  • @Erwin: why you couldn't simply answer with "execute ALTER SERVER your_foreign_server_name OPTIONS (ADD extensions 'postgis'); " ? Commented Aug 2, 2020 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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