Re: Optimizing Postgresql ILIKE while query

Hi

po 22. 10. 2018 v 7:57 odesílatel aman gupta <amangpt89(at)gmail(dot)com> napsal:

> Hi Team,
>
> Greetings for the day!!
>
> Platform:
>
> PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-11), 64-bit
>
>
> Issue:
>
>
> We have the base table which contains 22M records and we created a view on
> top of it while querying the view with ILIKE clause it took 44 seconds and
> with LIKE Clause 20 Seconds
>
> Query:
>
> fm_db_custom_db=# EXPLAIN (ANALYZE, TIMING OFF)
> select
> destination,hostname,inputfilename,inputtime,logicalservername,outputfilename,outputtime,processinglink,source,totalinputbytes,totalinputcdrs,totaloutputbytes,totaloutputcdrs
> from mmsuper.test_20m_view where inputfilename ilike
> '%SDPOUTPUTCDR_4001_BLSDP09_ADM_4997_18-10-15-02549.ASN%';
>
> Also attaching the comparison for both ILIKE and LIKE test performed.
>
> Expectation:
>
> How can we optimize our ILIKE query, since it is hardcoded in the
> application and we can't use any other keyword than ILIKE .
>

look on trigram index

https://www.postgresql.org/docs/11/static/pgtrgm.html

Regards

Pavel

>
> BR//
> Aman Gupta
> +918447611183
> amangpt89(at)gmail(dot)com
>
>