Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Artur, thanks for help. I managed to add the new strategy to the index. Hurray! I also discovered a bug in the process that I reported via the form. I still have a few questions: 1. Naming - pg_trgm_match, match, threshold, trgm_check_match, ThresholdStrategyNumber - are these good names? 2. I

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

2016-06-11 13:47 GMT+03:00 Greg Navis : > I made some progress but I'm stuck. I'm focused on GiST for now. Please > ignore sloppy naming for now. > > I made the following changes to pg_trgm--1.2.sql: > > CREATE TYPE pg_trgm_match AS (match TEXT, threshold REAL); > > CREATE

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

I made some progress but I'm stuck. I'm focused on GiST for now. Please ignore sloppy naming for now. I made the following changes to pg_trgm--1.2.sql: CREATE TYPE pg_trgm_match AS (match TEXT, threshold REAL); CREATE OR REPLACE FUNCTION trgm_check_match(string TEXT, match pg_trgm_match)

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Jeff Janes writes: > On Fri, Jun 10, 2016 at 9:20 AM, Jeff Janes wrote: >> I think you should pick a new operator name, not try to reuse %. > On second thought, it could use overloading distinguished with > different argument types, so it doesn't need

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 10, 2016 at 9:20 AM, Jeff Janes wrote: > On Thu, Jun 9, 2016 at 1:57 AM, Greg Navis wrote: >> Artur, no worries, I'm not writing any code ;-) >> >> I did the following: >> >> CREATE TYPE trgm_match AS (match TEXT, threshold NUMERIC); > > I

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Thu, Jun 9, 2016 at 1:57 AM, Greg Navis wrote: > Artur, no worries, I'm not writing any code ;-) > > I did the following: > > CREATE TYPE trgm_match AS (match TEXT, threshold NUMERIC); I would probably use REAL, not NUMERIC. But maybe there is good reason to use

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

I wrote: > Greg Navis writes: >> I'm not sure how to make this operator use an index. It seems I need to >> create an operator class but I'm not sure how. > What you'd want to do is add it to the existing operator class and then > teach the class's support functions

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Greg Navis writes: > I'm not sure how to make this operator use an index. It seems I need to > create an operator class but I'm not sure how. What you'd want to do is add it to the existing operator class and then teach the class's support functions (mostly, the

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Artur, no worries, I'm not writing any code ;-) I did the following: CREATE TYPE trgm_match AS (match TEXT, threshold NUMERIC); CREATE OR REPLACE FUNCTION trgm_check_match (string TEXT, match trgm_match) RETURNS bool AS 'SELECT match.match <-> string <= 1 - match.threshold' LANGUAGE SQL;

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On 08.06.2016 12:16, Greg Navis wrote: Would this be a better plan then: 1. Add support for trigram operators. 2. Implement `issimilar(lhs, rhs, threshold)`. 3. Add `issimilar` to the trigram operator classes. I think Tom's proposal with composite type is exelent option. If I understand

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Thanks for the replies. On Sat, Jun 4, 2016 at 8:48 PM, Jeff Janes wrote: > On Sat, Jun 4, 2016 at 2:50 AM, Greg Navis wrote: > > Thanks for your replies. > > > > Sorry for confusion. Instead of `similarity(lhs, rhs) >= show_limit()`, > > which of

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Jeff Janes writes: > I don't know if this would even be appropriate as an addition to > pg_trgm. We might want to fork that code instead. That would be a > shame, because the underlying c code would be the fundamentally the > same, but the alternative would be to force

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Sat, Jun 4, 2016 at 2:50 AM, Greg Navis wrote: > Thanks for your replies. > > Sorry for confusion. Instead of `similarity(lhs, rhs) >= show_limit()`, > which of course is completely equivalent to `lhs % rhs`, I wanted to write > `similarity(lhs, rhs) >=

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Thanks for your replies. Sorry for confusion. Instead of `similarity(lhs, rhs) >= show_limit()`, which of course is completely equivalent to `lhs % rhs`, I wanted to write `similarity(lhs, rhs) >= my_custom_threshold`. It seems that the approach with ternary operators is quite a bit of work. I

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 3, 2016 at 1:02 PM, Tom Lane wrote: > Jeff Janes writes: >> On Fri, Jun 3, 2016 at 12:13 PM, Greg Navis wrote: >>> I'm curious ... would it be difficult to modify PostgreSQL so that it'd use >>> the index for

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Jeff Janes writes: > On Fri, Jun 3, 2016 at 12:13 PM, Greg Navis wrote: >> 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.

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 3, 2016 at 3:27 PM, Jeff Janes wrote: > On Fri, Jun 3, 2016 at 12:13 PM, Greg Navis 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 >

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 3, 2016 at 12:13 PM, Greg Navis 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

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 3, 2016 at 3:13 PM, Greg Navis 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? > ​Not in a way that

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

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? Or even add `is_similar(lhs, rhs, threshold)` that'd allow to change the threshold on a per-query

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Friday, June 3, 2016, Greg Navis wrote: > Hey! > > I'm playing with pg_trgm. It seems that `lhs % rhs` is _almost_ equivalent > to `similarity(lhs, rhs) < show_limit()`. The difference that I noticed is > that `%` uses a GIN index while `similarity` does not. > > ``` >

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Oh, I understand. It is because you want different limits for restaurants and cinemas? I see only one solution. It is custom extension, which will create operator class similar to gin_trgm_ops and will depends on pg_trgm. In gin_trgm_consistent() you can use your own limit variable. As I

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Artur, thanks for your reply. That's right, `%` does use the index. The goal of using `similarity(lhs, rhs) >= show_limit()` was to replace `show_limit()` with a custom, per-query limit. I noticed that the latter approach does _not_ use the index, hence my question: grn=# EXPLAIN ANALYZE SELECT *

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Hello. As I know 'lhs % rhs' is equivalent to 'similarity(lhs, rhs) >= show_limit()'. And so your query should looks like this: SELECT * FROM restaurants WHERE city % 'warsw'; And it should use index. On 03.06.2016 13:35, Greg Navis wrote: Hey! I'm playing with pg_trgm. It seems that

[GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Hey! I'm playing with pg_trgm. It seems that `lhs % rhs` is _almost_ equivalent to `similarity(lhs, rhs) < show_limit()`. The difference that I noticed is that `%` uses a GIN index while `similarity` does not. ``` grn=# \d restaurants Table "public.restaurants" Column | Type