Re: Optimizing Postgresql ILIKE while query

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 <haramrae(at)gmail(dot)com> wrote:

>
>
> > On 22 Oct 2018, at 7:56, aman gupta <amangpt89(at)gmail(dot)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.
>
>
>