-1

Could someone suggest on the below query?

One of the queries 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

I have created indexes as per suggestion of @JGH. Somehow I am getting below issues.

CREATE INDEX ON myTable (parental_path text_pattern_ops); ERROR: index row size 2960 exceeds maximum 2712 for index "bundle_parental_path_text_idx_gin1" HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing. ********** Error **********

ERROR: index row size 2960 exceeds maximum 2712 for index "bundle_parental_path_text_idx_gin1" SQL state: 54000 Hint: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing.

CREATE INDEX ON myTable USING gin (parental_path gin_trgm_ops); ==> Created as per instructions.

CREATE INDEX ON myTable (parental_path); ERROR: index row size 2960 exceeds maximum 2712 for index "bundle_parental_path_trgm_idx_gin" HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing. ********** Error **********

ERROR: index row size 2960 exceeds maximum 2712 for index "bundle_parental_path_trgm_idx_gin" SQL state: 54000 Hint: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing.

Even after creating the gin index as per 2nd instruction, taking the same time.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
postgann2020 s
  • 9
  • 2

1 Answers1

5

like queries needs special indexes.

1) if the wildcard char % is at the beginning of the string, use

CREATE INDEX ON myTable (parental_path text_pattern_ops);

2) if the wildcard char % is at the end of the string, use

CREATE EXTENSION pg_trgm;
CREATE INDEX ON myTable USING gin (parental_path gin_trgm_ops);

And let's not forget that these indexes are not suitable for equality, so you also need a 3rd index, a regular btree

 CREATE INDEX ON myTable (parental_path);

You can read more in this article and this post.

JGH
  • 41,794
  • 3
  • 43
  • 89