Re: Request to help on Query improvement suggestion.

On Sun, May 24, 2020, 11:48 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>
>
>
>