If you are transforming the geom in your query this could be the issue. I prefer to create views in PG, rather than using GeoServer queries but I believe it is essentially the same. If I create a PG view where I am altering the geom, basically any st_ function or aggrigation, this results in poor performance as a view does not have a spatial index. Because the geom was altered the spatial index of the parents table doesn't apply to the view. Therefore, my solution is to create a secondary table (not a view) with a spatial index. You can use a function to keep the tables in sync if the source table is altered.
Vera On Fri., May 22, 2020, 4:51 a.m. postgann2020 s, <postgann2...@gmail.com> 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. > _______________________________________________ > Geoserver-users mailing list > > Please make sure you read the following two resources before posting to > this list: > - Earning your support instead of buying it, but Ian Turton: > http://www.ianturton.com/talks/foss4g.html#/ > - The GeoServer user list posting guidelines: > http://geoserver.org/comm/userlist-guidelines.html > > If you want to request a feature or an improvement, also see this: > https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer > > > Geoserver-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geoserver-users >
_______________________________________________ Geoserver-users mailing list Please make sure you read the following two resources before posting to this list: - Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/ - The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
