Question 40444 in PHP Question posted in
Official manual can be found here.

Best optimization for PGSQL Similarity – PHP

I am beginner php developer. I need use in my project function similarity from pgsql.

I have table:

CREATE TABLE IF NOT EXISTS table_name
    (id serial primary key, 
     prg_id TEXT,  
     code TEXT,  
     city TEXT,  
     area TEXT, 
     street TEXT, 
     numer TEXT,, 
     address TEXT)

This is very big table.

Now i need search in this table.

I have SQL ex:

SELECT street,  
     similarity('street'  , 'name street') AS similarity,  
     row_number() OVER () AS rank  
     FROM   table_name  
     where street% 'name street'  
     order by rank LIMIT  1;

It’s work, but slow.

Is it possible to write this question better to make it more efficient?

Tags:

2

Answers


  1. 0

    Install the extension pg_trgm, create an index on this column:

    CREATE INDEX trgm_idx ON table_name USING GIST (street gist_trgm_ops);

    And use EXPLAIN(ANALYZE, VERBOSE, BUFFERS, SETTINGS) for your SQL statement to get the query plan. Did it improve or not?

    Login or Signup to reply.
  2. 0

    If you rewrite the query slightly and create a GiST index, it can be fast:

    CREATE INDEX ON table_name USING gist (street gist_trgm_ops);

    Then you have to use the distance operator <-> instead of the similarity function:

    SELECT street,  
       street <-> 'name street' AS distance
       row_number() OVER () AS rank
FROM table_name  
ORDER BY distance
LIMIT 1;

    That will give you the row where street is most similar to 'name street'. You can add a WHERE condition if you want to exclude results that are not similar enough.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search