Обсуждение: Request to help on Query improvement suggestion.

Поиск

Список

Период

Сортировка

Request to help on Query improvement suggestion.

От

devchef2020 d

Дата:

22 мая 2020 г., 13:45:03

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.

Thanks & Regards,

Devchef.

Re: Request to help on Query improvement suggestion.

От

Laurenz Albe

Дата:

25 мая 2020 г., 09:47:59

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_pathLIKE '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=0loops=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

Re: Request to help on Query improvement suggestion.

От

Laurenz Albe

Дата:

25 мая 2020 г., 09:47:59

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_pathLIKE '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=0loops=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

Re: Request to help on Query improvement suggestion.

От

Laurenz Albe

Дата:

25 мая 2020 г., 09:47:59

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_pathLIKE '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=0loops=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

Re: Request to help on Query improvement suggestion.

От

Marlene Villanueva

Дата:

11 июля 2020 г., 20:15:27

On Sun, May 24, 2020, 11:48 PM Laurenz Albe <laurenz.albe@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

Re: Request to help on Query improvement suggestion.

От

Marlene Villanueva

Дата:

11 июля 2020 г., 20:15:27

On Sun, May 24, 2020, 11:48 PM Laurenz Albe <laurenz.albe@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

Re: Request to help on Query improvement suggestion.

От

Marlene Villanueva

Дата:

11 июля 2020 г., 20:15:27

On Sun, May 24, 2020, 11:48 PM Laurenz Albe <laurenz.albe@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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Request to help on Query improvement suggestion.

Request to help on Query improvement suggestion.

Re: Request to help on Query improvement suggestion.

Re: Request to help on Query improvement suggestion.

Re: Request to help on Query improvement suggestion.

Re: Request to help on Query improvement suggestion.

Re: Request to help on Query improvement suggestion.

Re: Request to help on Query improvement suggestion.