[postgis-users] Request to help on GIS Query improvement suggestion.

[postgann2020 s]

Hi Andreas,

Thanks for update.

I have created index as per suggestion

On Fri, May 22, 2020 at 4:26 PM Andreas Neumann <a.neumann at carto.net> wrote:

> Hi,
>
> It seems that this query has nothing to do with the spatial aspect of
> Postgis. I therefore recommend to look also at PostgreSQL support channels.
>
> But in general: queries using LIKE, esp. when the wildcard characters are
> at the beginning or in between, or worst case at the start and end often
> can't use an index, exp. btree indexes.
>
> Best to avoid LIKE whenever possible and also have a look at pg_trgrm and
> use a GIST or GIN index.
>
> Have a look at
> https://niallburkley.com/blog/index-columns-for-like-in-postgres/
>
> Greetings,
>
> Andreas
>
> On 2020-05-22 12:45, postgann2020 s wrote:
>
>   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,
> PostgAnn.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200522/59c8584d/attachment.html>