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