Re: Optimizing Postgresql ILIKE while query

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

po 22. 10. 2018 v 7:57 odesílatel aman gupta <amangpt89@xxxxxxxxx> 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@xxxxxxxxx