Thread

1. LIKE CLAUSE on VIEWS

   aditya desai <admad123@gmail.com> — 2023-01-22T11:33:46Z

   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.
   

2. Re: LIKE CLAUSE on VIEWS

   Samed YILDIRIM <samed@reddoc.net> — 2023-01-22T11:40:58Z

   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@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.
   >
   

3. Re: LIKE CLAUSE on VIEWS

   Rick Otten <rottenwindfish@gmail.com> — 2023-01-22T15:55:13Z

   >
   >
   > 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
   

4. Re: LIKE CLAUSE on VIEWS

   Jeff Janes <jeff.janes@gmail.com> — 2023-01-22T17:49:51Z

   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
   
   >