<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /></head><body style='font-size: 10pt; font-family: Verdana,Geneva,sans-serif'>
<p>Hi,</p>
<p>This e-mail list is dedicated to Postgis problems and not SQL problems in general.</p>
<p>Your WHERE part in the query seems quite complicated. If you have several "LIKE"s, static and dynamic parts mixed wildly with concatenation in your WHERE filter, I fear that no index covers such a scenario. At the end you are probably forced to sequential scans for such queries.</p>
<p>Besides, I wouldn't use upper case object names in PostgreSQL.</p>
<p>But again: ask in a proper place and you are more likely to get replies. Or get some commercial support. You don't seem to be very familiar with PostgreSQL or SQL in general, so getting some commercial support would probably be a good investment to help you get started.</p>
<p>Greetings,</p>
<p>Andreas</p>
<p id="reply-intro">On 2020-05-22 14:15, postgann2020 s wrote:</p>
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">
<div id="replybody1">
<div>
<div dir="ltr">Hi Andreas,
<div> </div>
<div>
<div>Thanks for the update.</div>
<div> </div>
<div>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..?</div>
</div>
<div> </div>
<div><span style="color: #ff0000;"><strong>CREATE INDEX cable_pair_parental_path_trgm_idx ON schema.table_name USING gin (parental_path gin_trgm_ops);</strong></span></div>
<div> </div>
<div><span style="color: #ff0000;">Explain Plan:</span><br />=============<br /><br />Limit (cost=108111.60..108111.61 rows=1 width=4) (actual time=4597.605..4597.605 rows=0 loops=1)<br /> Output: ((seq_no + 1)), seq_no<br /> Buffers: shared hit=2967 read=69606 dirtied=1<br /> -> Sort (cost=108111.60..108113.09 rows=595 width=4) (actual time=4597.603..4597.603 rows=0 loops=1)<br /> Output: ((seq_no + 1)), seq_no<br /> Sort Key: TABLE_NAME.seq_no DESC<br /> Sort Method: quicksort Memory: 25kB<br /> Buffers: shared hit=2967 read=69606 dirtied=1<br /> -> <strong><span style="color: #ff0000;">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)</span></strong><br /> Output: (seq_no + 1), seq_no<br /> <span style="color: #ff0000;">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))</span><br /> Rows Removed by Filter: 1930188<br /> Buffers: shared hit=2967 read=69606 dirtied=1 </div>
<div> </div>
<div>Could you please suggest on this.</div>
<div> </div>
<div>Regards,</div>
<div>PostgAnn.</div>
<div> </div>
<div> </div>
</div>
<br />
<div class="v1gmail_quote">
<div class="v1gmail_attr" dir="ltr">On Fri, May 22, 2020 at 5:43 PM postgann2020 s <<a href="mailto:postgann2020@gmail.com" rel="noreferrer">postgann2020@gmail.com</a>> wrote:</div>
<blockquote class="v1gmail_quote" style="margin: 0px 0px 0px 0.8ex; border-left: 1px solid #cccccc; padding-left: 1ex;">
<div dir="ltr">Hi Andreas,<br />
<div> </div>
<div>Thanks for update.</div>
<div> </div>
<div>I have created index as per suggestion</div>
</div>
<br />
<div class="v1gmail_quote">
<div class="v1gmail_attr" dir="ltr">On Fri, May 22, 2020 at 4:26 PM Andreas Neumann <<a href="mailto:a.neumann@carto.net" rel="noreferrer">a.neumann@carto.net</a>> wrote:</div>
<blockquote class="v1gmail_quote" style="margin: 0px 0px 0px 0.8ex; border-left: 1px solid #cccccc; padding-left: 1ex;">
<div style="font-size: 10pt; font-family: Verdana,Geneva,sans-serif;">
<p>Hi,</p>
<p>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.</p>
<p>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.</p>
<p>Best to avoid LIKE whenever possible and also have a look at pg_trgrm and use a GIST or GIN index.</p>
<p>Have a look at <a href="https://niallburkley.com/blog/index-columns-for-like-in-postgres/" target="_blank" rel="noopener noreferrer">https://niallburkley.com/blog/index-columns-for-like-in-postgres/</a></p>
<p>Greetings,</p>
<p>Andreas</p>
<p id="v1gmail-m_-5891412856806519617gmail-m_-1732667963858542748reply-intro">On 2020-05-22 12:45, postgann2020 s wrote:</p>
<blockquote style="padding: 0px 0.4em; border-left: 2px solid #1010ff; margin: 0px;">
<div id="v1gmail-m_-5891412856806519617gmail-m_-1732667963858542748replybody1">
<div>
<div dir="ltr"> Hi Team,<br /><br />Thanks for your support.<br /><br />Could someone please suggest on the below query.<br /><br />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.<br /><br /><span style="color: #ff0000;">Database Stack:</span><br />===============<br />PostgreSQL : 9.5.15<br />Postgis: 2.2.7<br /><br /><span style="color: #ff0000;">Table Structure:</span><br />===================<br /><br />ALTER TABLE SCHEMA.TABLE_NAME ADD COLUMN parental_path text;<br /><br /><span style="color: #ff0000;">Created Indexes on column parental_path:</span><br />=================================<br /><br />CREATE INDEX cable_pair_parental_path_idx<br /> ON SCHEMA.TABLE_NAME<br /> USING btree<br /> (md5(parental_path) COLLATE pg_catalog."default");<br /> <br />CREATE INDEX cable_pair_parental_path_idx_fulltext<br /> ON SCHEMA.TABLE_NAME<br /> USING gist<br /> (parental_path COLLATE pg_catalog."default");<br /> <br /><span style="color: #ff0000;">Sample data in "parental_path" column:</span><br />======================================<br /> <br />'route--2309421/2951584/3373649/2511322/1915187/2696397/2623291/2420708/2144348/2294454,circuit--88458/88460,sheath--8874'<br /><br /><span style="color: #ff0000;">Actual Query:</span><br />=============<br /><br />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;<br /><br /><span style="color: #ff0000;">Explain Plan:</span><br />=============<br /><br />Limit (cost=108111.60..108111.61 rows=1 width=4) (actual time=4597.605..4597.605 rows=0 loops=1)<br /> Output: ((seq_no + 1)), seq_no<br /> Buffers: shared hit=2967 read=69606 dirtied=1<br /> -> Sort (cost=108111.60..108113.09 rows=595 width=4) (actual time=4597.603..4597.603 rows=0 loops=1)<br /> Output: ((seq_no + 1)), seq_no<br /> Sort Key: TABLE_NAME.seq_no DESC<br /> Sort Method: quicksort Memory: 25kB<br /> Buffers: shared hit=2967 read=69606 dirtied=1<br /> -> <strong><span style="color: #ff0000;">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)</span></strong><br /> Output: (seq_no + 1), seq_no<br /> <span style="color: #ff0000;">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))</span><br /> Rows Removed by Filter: 1930188<br /> Buffers: shared hit=2967 read=69606 dirtied=1<br />
<div> </div>
<div>Please share your suggestion.</div>
<div>Thanks & Regards,</div>
<div>PostgAnn.</div>
</div>
</div>
</div>
<br />
<div style="margin: 0px; padding: 0px; font-family: monospace;">_______________________________________________<br />postgis-users mailing list<br /><a href="mailto:postgis-users@lists.osgeo.org" rel="noreferrer">postgis-users@lists.osgeo.org</a><br /><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank" rel="noopener noreferrer">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div>
</blockquote>
<p><br /></p>
</div>
</blockquote>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
<p><br /></p>
</body></html>