Any performance penalties for PostGIS queries?

[boris-glumpler]

My current app extensively uses PostGIS for proximity search, especially to only return search results within a given distance or within a given city, postcode area or state.

The docs state that filters should be done within pg_search methods, so I'm wondering what performance penalties I'm looking at once I add distance filters to a search query?

Cheers!

[neilyio]

To get the best search performance, you should leverage pg_search/Tantivy's index as much as possible. If you don't filter within pg_search, then you're searching across your entire dataset, which is likely much bigger than the geographic region that you're interested in.

Is it possible to try something like adding the coordinates of your results to the index as numeric_fields? That would allow you to use Tantivy's filtering to make sure that only coordinates within the range that you're interested in are returned.

[boris-glumpler]

There isn't much info online about Tantivy, tbh, so it's hard to know what it can do out of the box or not. I would obviously already have an appropriate index on the coordinates, so would it make sense to first get all the results within a given distance and then perform a search query across an already reduced dataset?

[neilyio]

There's not really a way to perform a search query across a reduced dataset, such as with a CTE. You're always searching over your search index.

You don't need to interact with Tantivy directly. I suggest you try to add lat and long as numeric_fields on when you create_bm25, that would let you query them like this:
https://docs.paradedb.com/search/full-text/bm25#efficient-filtering

[boris-glumpler]

Ok, that makes sense. What about the other way round? First I get all the results form the bm25 index and then I reduce the results by distance? Seems a bit weird to me to have to re-implement various PostGIS functions within Tantivy.

[neilyio]

You could do that, but you'd have to scan through all the search results in your dataset, which is presumably much bigger than the region you're interested in.

It depends on how you have your tables and indexes set up, but an example would be wanting to search for "pizza" in Dublin. If you don't use Tantivy's efficient filtering... then you're going to have search for "pizza" all over the entire world, and then scan through those results to further filter down to results that are only from Dublin.

[neilyio]

The biggest factor on search speed with pg_search is the number of results you're returning, so this would cause your text search to be much slower than necessary.

[boris-glumpler]

Ok, got it!