Re: LIKE CLAUSE on VIEWS

[Samed YILDIRIM <samed@xxxxxxxxxx>]

Hi Aditya,

If you share your view's query and the query you run against the view, it would help all of us to understand better.

pg_trgm would be the life saver option for you, of course if you created it on the right column, with the right _expression_, and by using the right indexing method. It doesn't mean you can't use any index and indexes won't be used because it is a view, well, if you do it right.

https://www.postgresql.org/docs/current/pgtrgm.html

Best regards.

Samed YILDIRIM

On Sun, 22 Jan 2023 at 13:34, aditya desai <admad123@xxxxxxxxx> wrote:

Hi,

Is there any way to improve performance of LIKE clause on VIEWS.

select * From request_vw where upper(status) like '%CAPTURED%' - 28 seconds.

select * from  request_vw where status='CAPTURED'

Application team is reluctant to change queries from the Application side to = instead of LIKE.

Also as this is VIEW TRIGRAM nor normal indexes don't get used.

Regards,

Aditya.