Request to help on GIS Query improvement suggestion.

[postggen2020 s <postggen2020@xxxxxxxxx>]

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 if I have to change or add new objects to the table etc..

Thanks & Regards,

PostgAnn.