Re: LIKE CLAUSE on VIEWS

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, Jan 22, 2023 at 6:34 AM 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.

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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux