Re: Optimizing Postgresql ILIKE while query

From: Scottix <scottix(at)gmail(dot)com>
To: Alban Hertroys <haramrae(at)gmail(dot)com>
Cc: aman gupta <amangpt89(at)gmail(dot)com>, pgadmin-hackers(at)lists(dot)postgresql(dot)org, pgsql-general(at)lists(dot)postgresql(dot)org, manoj(dot)t(dot)kumar(at)ericsson(dot)com, rajesh(dot)tiwari(at)ericsson(dot)com
Subject: Re: Optimizing Postgresql ILIKE while query
Date: 2018-10-22 15:22:34
Message-ID: CANKFHZ9s8FAwC_XXbeX0k+KSgcb4eOWsqSYu1OjfCs3JUC8bgw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-hackers pgsql-general

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.
>
>
>

In response to

Browse pgadmin-hackers by date

  From Date Subject
Next Message Aditya Toshniwal 2018-10-24 07:17:48 [pgAdmin4][patch] File Manager replace file minor fix
Previous Message Murtuza Zabuawala 2018-10-22 13:18:18 [pgAdmin4] Fix minor CSS styling issue for IE-11

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Janes 2018-10-22 15:42:50 Re: Replication question
Previous Message Andreas Kretschmer 2018-10-22 14:04:34 Re: Replication question