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 .