From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | devchef2020 d <devchef2020(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Request to help on Query improvement suggestion. |
Date: | 2020-05-25 06:47:59 |
Message-ID: | 5f26f28471cd4d0e9cdcf98d93238c29fb344e8b.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Wim Bertels | 2020-05-26 13:29:09 | Re: viewing connectioninfo used by subscriber on the publication server when inactive |
Previous Message | Laurenz Albe | 2020-05-25 06:42:07 | Re: Lag clarification with Sync Replication |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2020-05-25 07:44:55 | Re: Query returns no rows in pg_basebackup cluster |
Previous Message | David G. Johnston | 2020-05-25 06:42:10 | Re: Query returns no rows in pg_basebackup cluster |
From | Date | Subject | |
---|---|---|---|
Next Message | Kenneth Marshall | 2020-05-28 15:56:59 | PostgreSQL performance problem moving from 9.6.17 to 12.3 |
Previous Message | Tom Lane | 2020-05-25 04:48:23 | Re: Date vs Timestamp without timezone Partition Key |