Обсуждение: Request to help on GIS 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

Your indexes and operators are not compatible. You have added a btree index on md5 function result and are not using md5 in your query, and also using LIKE operator not one of the supported ones. I believe it might use a btree operator (plain value, not md5 result) if you are always searching for "string starts with ____ but I don't know what it ends with" but you can't possibly use a btree index where you are putting a wild card at the front.

Your indexes and operators are not compatible. You have added a btree index on md5 function result and are not using md5 in your query, and also using LIKE operator not one of the supported ones. I believe it might use a btree operator (plain value, not md5 result) if you are always searching for "string starts with ____ but I don't know what it ends with" but you can't possibly use a btree index where you are putting a wild card at the front.

Your indexes and operators are not compatible. You have added a btree index on md5 function result and are not using md5 in your query, and also using LIKE operator not one of the supported ones. I believe it might use a btree operator (plain value, not md5 result) if you are always searching for "string starts with ____ but I don't know what it ends with" but you can't possibly use a btree index where you are putting a wild card at the front.