Also leading wildcards can inhibit the use of indexes. Best to try to avoid LIKE queries similar to '%TERM'
On Mon, Oct 22, 2018 at 12:23 AM Alban Hertroys <haram...@gmail.com> wrote: > > > > On 22 Oct 2018, at 7:56, aman gupta <amangp...@gmail.com> wrote: > > > > 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%'; > > Perhaps, when you have a question about timing, you shouldn't turn off the > timing in the query plan? Now we can't see where the time is spent. > > > <LIKE_Clause_ILIKE_Clause_Postgres_Response.txt> > > That's all sequential scans that each remove a significant amount of rows. > That probably costs a significant amount of time to do. > > It looks like you don't have any indices on the underlying table(s) at > all. I'd start there and then look at the ILIKE problem again. By that > time, Pavel's suggestion for a trigram index on that text field is probably > spot-on. > > Alban Hertroys > -- > If you can't see the forest for the trees, > cut the trees and you'll find there is no forest. > > >
