Hi po 22. 10. 2018 v 7:57 odesÃlatel aman gupta <amangp...@gmail.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 > amangp...@gmail.com > >
