On Sun, May 24, 2020, 11:48 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote:
> On Fri, 2020-05-22 at 16:15 +0530, devchef2020 d wrote: > > PostgreSQL : 9.5.15 > > > 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"); > > > 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 > > An index on an expression can only be used if the expression is exactly > the same as on one > side of an operator in a WHERE condition. > > So your only chance with that query is to hope for a bitmap OR with an > index on "parental path". > > Two things to try: > > 1) CREATE INDEX ON table_name (parental_path text_pattern_ops); > > 2) CREATE EXTENSION pg_trgm; > CREATE INDEX ON table_name USING GIN (parental_path gin_trgm_ops); > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > > >
