On Fri, Jun 3, 2016 at 12:13 PM, Greg Navis <contact@gregnavis.com> wrote: > Thanks for answers and sorry for not searching hard enough. > > I'm curious ... would it be difficult to modify PostgreSQL so that it'd use > the index for `similarity(lhs, rhs) >= show_limit()` too?
Yes, that would be very difficult. The project has kind of painted itself into a corner on that.
If it were easy, I doubt we would have added the % operator with the ugly set_limit() wart in the first place (although I was not around at the time that was done--maybe there were other considerations).
Can you clarify?
As far pg_trgm goes its only option was/is to use a GUC if it wants the benefit of indexing. The set/show limit API is merely a syntactic convenience.
The cleanest API I can come up with giving present limitations is:
SELECT * FROM get_restaurants_by_similarity('warsw', 70)
-- you could make the second parameter optional or disallowed depending on how you want to enforce your selection policy.
The SQL queries in that SQL language function would be:
SET LOCAL .... = 70;
SELECT * FROM restaurants WHERE city % $1;
The later being returned as "SETOF restaurants"
You main problem here, then, is loss of optimization options.
The best solution would depend very much on how you plan to use these queries. You also have an option to execute dynamic SQL within a pl/pgsql function.