Re: [Geoserver-users] Request to help on GIS Query improvement suggestion.
It's possible to create an index on the transformed data without creating a second table, though it you need to use the same transform often it might be worth creating a second geometry column in the same table. Postann - you are using LIKE queries - these are rarely fast. I would break that column apart once and for all to allow quick indexed queries on just the part you want. If more data is being added to the table then consider modifying the inserting program or add a trigger to do it on insert. Ian On Fri, 22 May 2020 at 16:25, Vera Green wrote: > 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, > 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:
Re: [Geoserver-users] Request to help on GIS Query improvement suggestion.
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, 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
[Geoserver-users] Request to help on GIS Query improvement suggestion.
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
