Thread: LIKE CLAUSE on VIEWS
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.
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.
Best regards.
Samed YILDIRIM
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.
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
On Sun, Jan 22, 2023 at 6:34 AM 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.
You would need to have an expression index over upper(status) to support such a query, not an index on status itself. It would probably be better to just use ILIKE rather than upper(), so `status ILIKE '%captured%'`, which can benefit from an index on "status" itself.
Also as this is VIEW TRIGRAM nor normal indexes don't get used.
There is no problem in general using trigram indexes (or any other index types) on views. Maybe your view has particular features which inhibit the use of the index, but you haven't given any information which would be useful for assessing that. Did you try an index, or just assume it wouldn't work without trying?
Cheers,
Jeff