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 wrote: > > > > On 22 Oct 2018, at 7:56, aman gupta 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. > > > > > 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. > > >
Re: Optimizing Postgresql ILIKE while query
> On 22 Oct 2018, at 7:56, aman gupta 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. > 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.
Re: Optimizing Postgresql ILIKE while query
Hi po 22. 10. 2018 v 7:57 odesÃlatel aman gupta 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 > >
Optimizing Postgresql ILIKE while query
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 . BR// Aman Gupta +918447611183 amangp...@gmail.com fm_db_custom_db=# \d+ test_20m_view View "mmsuper.test_20m_view" Column |Type | Modifiers | Storage | Description ---+-+---+--+- inputfilename | character varying(300) | | extended | source| character varying(80) | | extended | hostname | character varying(300) | | extended | configname| character varying(300) | | extended | logicalservername | character varying(300) | | extended | clustername | character varying(80) | | extended | inputtime | timestamp without time zone | | plain| totalinputbytes | bigint | | plain| totalinputcdrs| bigint | | plain| destination | character varying(80) | | extended | outputfilename| character varying(300) | | extended | processinglink| character varying(80) | | extended | totaloutputbytes | bigint | | plain| totaloutputcdrs | bigint | | plain| outputtime| timestamp without time zone | | plain| View definition: SELECT a67.sourceid AS inputfilename, a67.innodename AS source, a67.hostname, a67.configname, a67.logicalservername, ci.layered_clustername AS clustername, a67.intime AS inputtime, a73.bytes AS totalinputbytes, a73.cdrs AS totalinputcdrs, a68.outnodename AS destination, a68.destinationid AS outputfilename, a68.outnodeid AS processinglink, a68.bytes AS totaloutputbytes, a68.cdrs AS totaloutputcdrs, a68.outtime AS outputtime FROM test_report_auditlog2 a67 LEFT JOIN test_report_auditlog2 a73 ON a67.destinationid::text = a73.sourceid::text AND a67.event = 67 AND a73.event = 73 LEFT JOIN test_report_auditlog2 a80 ON a73.sourceid::text = a80.sourceid::text AND a80.event = 80 LEFT JOIN test_report_auditlog2 a68 ON a80.destinationid::text = a68.sourceid::text AND a68.event = 68 LEFT JOIN clusterinfo ci ON a68.hostname::text = ci.hostname::text; fm_db_custom_db=# fm_db_custom_db=# EXPLAIN (ANALYZE, TIMING OFF) fm_db_custom_db-# select destination, fm_db_custom_db-# hostname, fm_db_custom_db-# inputfilename, fm_db_custom_db-# inputtime, fm_db_custom_db-# logicalservername, fm_db_custom_db-# outputfilename, fm_db_custom_db-# outputtime, fm_db_custom_db-# processinglink, fm_db_custom_db-# source, fm_db_custom_db-# totalinputbytes, fm_db_custom_db-# totalinputcdrs, fm_db_custom_db-# totaloutputbytes, fm_db_custom_db-# totaloutputcdrs fm_db_custom_db-# from mmsuper.test_20m_view fm_db_custom_db-# where inputfilename ilike '%SDPOUTPUTCDR_4001_BLSDP09_ADM_4997_18-10-15-02549.ASN%';
