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

[postgann2020 s]

Hi Andreas,

Thanks for the update.

I have created an index as per suggestion. But it seems the query is not
utilizing the index on the column. Is am I missing something?.Is I have to
add expression somethings like..?

*CREATE INDEX cable_pair_parental_path_trgm_idx ON schema.table_name USING
gin (parental_path gin_trgm_ops);*

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

Could you please suggest on this.

Regards,
PostgAnn.



On Fri, May 22, 2020 at 5:43 PM postgann2020 s <postgann2020 at gmail.com>
wrote:

> 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/c11e07d4/attachment.html>