Horton

Re: LIKE CLAUSE on VIEWS

Rick Otten <rottenwindfish@gmail.com>

View thread
From: Rick Otten <rottenwindfish@gmail.com>
To: Samed YILDIRIM <samed@reddoc.net>
Cc: aditya desai <admad123@gmail.com>, Pgsql Performance <pgsql-performance@lists.postgresql.org>
Date: 2023-01-22T15:55:13Z
Lists: pgsql-performance 
>
>
> On Sun, 22 Jan 2023 at 13:34, aditya desai <admad123@gmail.com> 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.
>>
>
You could try using the `text_pattern_ops` operator class on your index on
the `status` column:
https://www.postgresql.org/docs/current/indexes-opclass.html