0

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. – Erwin Brandstetter Jun 26 '17 at 15:52
6

Assuming current Postgres 9.6, it can work.

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:

Assuming 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');

Here is a related blog post by Michael Paquier.

  • 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. – John Powell Oct 17 '18 at 10:28

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.