Query taking long with levenshtein function

From: Teju Jakkidi vlogs <teja(dot)jakkidi05(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Query taking long with levenshtein function
Date: 2023-02-28 23:11:55
Message-ID: CAKA2XvZ_zFgfnavkEDMvfLtHzQeH-3qGROfAp+=CnrLF8TTvyQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello Admins,

We have a query as below which uses the levenshtein function to compare
strings.

SELECT "name", levenshtein("name",'some string') as p FROM table1
where levenshtein("name",'some string') <= 2 order by p desc;

We have a GIST index built on top of this table as below:
CREATE INDEX gist_idx ON table1 USING GIST("name");

The table has 70million rows.

The above query when executed in postgres, takes around 3 minutes to return
the result whereas a similar query in BQ takes only 10 seconds.
We made sure that data is not being spilled to disks as the query has order
by clause.
work_mem looks good (4 MB) as no spilling to temp is observed.
Table is analyzed and vacuumed.
shared_buffer size is 5GB. (Instance has total of 15 GB mem)

We are not sure what else needs to be checked for improving the query
performance. Please advise if levenshtein needs any sort of other indexes.

Thanks,
Teja. J.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2023-02-28 23:30:51 Re: Query taking long with levenshtein function
Previous Message Chris Hoover 2023-02-27 16:25:04 Re: New PG14 server won't start with >2GB shared_buffers