[postgis-users] Request to help on GIS Query improvement suggestion.

[Andreas Neumann]

Hi, 

It seems that this query has nothing to do with the spatial aspect of
Postgis. I therefore recommend to look also at PostgreSQL support
channels. 

But in general: queries using LIKE, esp. when the wildcard characters
are at the beginning or in between, or worst case at the start and end
often can't use an index, exp. btree indexes. 

Best to avoid LIKE whenever possible and also have a look at pg_trgrm
and use a GIST or GIN index. 

Have a look at
https://niallburkley.com/blog/index-columns-for-like-in-postgres/ 

Greetings, 

Andreas 

On 2020-05-22 12:45, postgann2020 s wrote:

> Hi Team,
> 
> Thanks for your support.
> 
> Could someone please suggest on the below query.
> 
> One of the query which was created on GIS data is taking a long time and even it is not taking the index as well. I have included all the required details for reference.
> 
> Database Stack:
> ===============
> PostgreSQL : 9.5.15
> Postgis: 2.2.7
> 
> Table Structure:
> ===================
> 
> ALTER TABLE SCHEMA.TABLE_NAME ADD COLUMN parental_path text;
> 
> Created Indexes on column parental_path:
> =================================
> 
> CREATE INDEX cable_pair_parental_path_idx
> ON SCHEMA.TABLE_NAME
> USING btree
> (md5(parental_path) COLLATE pg_catalog."default");
> 
> CREATE INDEX cable_pair_parental_path_idx_fulltext
> ON SCHEMA.TABLE_NAME
> USING gist
> (parental_path COLLATE pg_catalog."default");
> 
> Sample data in "parental_path" column:
> ======================================
> 
> 'route--2309421/2951584/3373649/2511322/1915187/2696397/2623291/2420708/2144348/2294454,circuit--88458/88460,sheath--8874'
> 
> Actual Query:
> =============
> 
> SELECT seq_no + 1 FROM SCHEMA.TABLE_NAME WHERE (parental_path LIKE '%,sheath--' || cable_seq_id || ',%' OR parental_path LIKE 'sheath--' || cable_seq_id || ',%' OR parental_path LIKE '%,sheath--' || cable_seq_id OR parental_path = 'sheath--' || cable_seq_id) ORDER BY seq_no DESC LIMIT 1;
> 
> Explain Plan:
> =============
> 
> Limit  (cost=108111.60..108111.61 rows=1 width=4) (actual time=4597.605..4597.605 rows=0 loops=1)
> Output: ((seq_no + 1)), seq_no
> Buffers: shared hit=2967 read=69606 dirtied=1
> ->  Sort  (cost=108111.60..108113.09 rows=595 width=4) (actual time=4597.603..4597.603 rows=0 loops=1)
> Output: ((seq_no + 1)), seq_no
> Sort Key: TABLE_NAME.seq_no DESC
> Sort Method: quicksort  Memory: 25kB
> Buffers: shared hit=2967 read=69606 dirtied=1
> ->  Seq Scan on SCHEMA.TABLE_NAME  (cost=0.00..108108.63 rows=595 width=4) (actual time=4597.595..4597.595 rows=0 loops=1)
> Output: (seq_no + 1), seq_no
> Filter: ((TABLE_NAME.parental_path ~~ '%,sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~ 'sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~ '%,sheath--64690'::text) OR (TABLE_NAME.parental_path = 'sheath--64690'::text))
> Rows Removed by Filter: 1930188
> Buffers: shared hit=2967 read=69606 dirtied=1
> 
> Please share your suggestion. 
> Thanks & Regards, 
> PostgAnn. 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200522/2cd5eac2/attachment.html>