Request to help on Query improvement suggestion.

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