Re: [postgis-users] topology performance improvement purpose
It's not 'topology', but the actual name of the topology. Each topology has data in its own schema... --strk; On Wed, May 09, 2012 at 09:11:54AM +0800, Ben Madin wrote: > Do you need to even change the search_path - surely you could have a function > version with the 'topology' schema hard-coded for the performance > improvement. Obviously that won't work if you have a different schema name, > but if the performance enhancement is worth it, grepping out topology to > whatever you need will also be worth the effort. > > cheers > > Ben > > > On 03/05/2012, at 5:58 AM, Jose Carlos Martinez Llario wrote: > > > Hi, > > wanted to share a though about improving the topology performance in > > postgis. > > > > Many of the functions are using EXECUTE instead of running directly select > > commands. > > In many cases this is need just because of the used of the schema name of > > the topology primitive tables. > > > > I found between 4 and 10 times improving performance some sentences. I > > guessing it could be even better. > > > > My question is if an approach like the following makes sense: > > > > 1.- With one command we can make the current topology schema the default > > schema in search path, > > 2.- then working with topology > > 3.- Change search_path again when finishing the spatial analysis. > > > > (the parameter of the topology name in the functions should be ignored or > > removed) > > I know it requires a lot of changes but currently the performance of the > > persistent topology is too slow. > > > > if one do that many functions can be run without EXECUTE. Spatial analysis > > between different topologies is not possible which fits the procedure. > > > > What do you think Sandro? > > > > Regards, > > Jose > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users -- ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology performance improvement purpose
Do you need to even change the search_path - surely you could have a function version with the 'topology' schema hard-coded for the performance improvement. Obviously that won't work if you have a different schema name, but if the performance enhancement is worth it, grepping out topology to whatever you need will also be worth the effort. cheers Ben On 03/05/2012, at 5:58 AM, Jose Carlos Martinez Llario wrote: > Hi, > wanted to share a though about improving the topology performance in postgis. > > Many of the functions are using EXECUTE instead of running directly select > commands. > In many cases this is need just because of the used of the schema name of > the topology primitive tables. > > I found between 4 and 10 times improving performance some sentences. I > guessing it could be even better. > > My question is if an approach like the following makes sense: > > 1.- With one command we can make the current topology schema the default > schema in search path, > 2.- then working with topology > 3.- Change search_path again when finishing the spatial analysis. > > (the parameter of the topology name in the functions should be ignored or > removed) > I know it requires a lot of changes but currently the performance of the > persistent topology is too slow. > > if one do that many functions can be run without EXECUTE. Spatial analysis > between different topologies is not possible which fits the procedure. > > What do you think Sandro? > > Regards, > Jose > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology performance improvement purpose
On Thu, May 03, 2012 at 12:17:08PM +0200, Jose Carlos Martinez wrote: > Do you think its worth it to try to modify some topology functions > for not using EXECUTE and do some performance tests? Sure, I'll be interested in reading a report about the outcomes. Better to start with a read-only function. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology performance improvement purpose
On 03/05/2012 11:19, Sandro Santilli wrote: On Thu, May 03, 2012 at 12:13:58AM +0200, Jose Carlos Martinez Llario wrote: i meant not the default schema in search_path but the first topology working schema after public. In order for your idea to work reliably the topology schema must really be the first one, even before public. Or existance of an "edge" or "node" or "relation" or "face" or "edge_data" relation in public would break the functions. It's surely an interesting idea worth trying out. I've been thinking that we'd also want "internal" functions for use by other "external" ones, for the same performance reason (for example to reduce checks when the caller knows it already performed them). There could be such "internal" functions taking no topology name but rather assuming the topology schema is the first in search_path. But even changing single functions to do the search_path trick only internally might show up improvements. The testsuite for topology is pretty robust so any implementation should be easy to check for regressions. Of course there's no test for the presence of specially named tables in the public schema which we should add to prevent regressions like the above. Do you feel like producing performance tests ? Such tests should generate common cases of expensive datasets and run the functions over them, reporting timings of each run. Won't be easy to test "hot" runs with mutating functions... I didnt check the testsuite for topology Sandro. I will look at it though. Do you think its worth it to try to modify some topology functions for not using EXECUTE and do some performance tests? If so, I will do when I have a while. Addressing each specific function in turn would likely help managing this process. Maybe starting from one that's known to hit often. --strk; On 02/05/2012 23:58, Jose Carlos Martinez Llario wrote: Hi, wanted to share a though about improving the topology performance in postgis. Many of the functions are using EXECUTE instead of running directly select commands. In many cases this is need just because of the used of the schema name of the topology primitive tables. I found between 4 and 10 times improving performance some sentences. I guessing it could be even better. My question is if an approach like the following makes sense: 1.- With one command we can make the current topology schema the default schema in search path, 2.- then working with topology 3.- Change search_path again when finishing the spatial analysis. (the parameter of the topology name in the functions should be ignored or removed) I know it requires a lot of changes but currently the performance of the persistent topology is too slow. if one do that many functions can be run without EXECUTE. Spatial analysis between different topologies is not possible which fits the procedure. What do you think Sandro? Regards, Jose ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- -- José Carlos Martínez Llario Producción Cartográfica y SIG. Dpto. Ingeniería Cartográfica. Univ. Politécnica de Valencia. E-mail: jomar...@cgf.upv.es Telf: 963877000 ext. 75599 -- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology performance improvement purpose
On Thu, May 03, 2012 at 12:13:58AM +0200, Jose Carlos Martinez Llario wrote: > i meant not the default schema in search_path but the first topology > working schema after public. In order for your idea to work reliably the topology schema must really be the first one, even before public. Or existance of an "edge" or "node" or "relation" or "face" or "edge_data" relation in public would break the functions. It's surely an interesting idea worth trying out. I've been thinking that we'd also want "internal" functions for use by other "external" ones, for the same performance reason (for example to reduce checks when the caller knows it already performed them). There could be such "internal" functions taking no topology name but rather assuming the topology schema is the first in search_path. But even changing single functions to do the search_path trick only internally might show up improvements. The testsuite for topology is pretty robust so any implementation should be easy to check for regressions. Of course there's no test for the presence of specially named tables in the public schema which we should add to prevent regressions like the above. Do you feel like producing performance tests ? Such tests should generate common cases of expensive datasets and run the functions over them, reporting timings of each run. Won't be easy to test "hot" runs with mutating functions... Addressing each specific function in turn would likely help managing this process. Maybe starting from one that's known to hit often. --strk; > > On 02/05/2012 23:58, Jose Carlos Martinez Llario wrote: > >Hi, > >wanted to share a though about improving the topology performance > >in postgis. > > > >Many of the functions are using EXECUTE instead of running > >directly select commands. > >In many cases this is need just because of the used of the schema > >name of the topology primitive tables. > > > >I found between 4 and 10 times improving performance some > >sentences. I guessing it could be even better. > > > >My question is if an approach like the following makes sense: > > > >1.- With one command we can make the current topology schema the > >default schema in search path, > >2.- then working with topology > >3.- Change search_path again when finishing the spatial analysis. > > > >(the parameter of the topology name in the functions should be > >ignored or removed) > >I know it requires a lot of changes but currently the performance > >of the persistent topology is too slow. > > > >if one do that many functions can be run without EXECUTE. Spatial > >analysis between different topologies is not possible which fits > >the procedure. > > > >What do you think Sandro? > > > >Regards, > >Jose > > > >___ > >postgis-users mailing list > >postgis-users@postgis.refractions.net > >http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users -- ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology performance improvement purpose
i meant not the default schema in search_path but the first topology working schema after public. On 02/05/2012 23:58, Jose Carlos Martinez Llario wrote: Hi, wanted to share a though about improving the topology performance in postgis. Many of the functions are using EXECUTE instead of running directly select commands. In many cases this is need just because of the used of the schema name of the topology primitive tables. I found between 4 and 10 times improving performance some sentences. I guessing it could be even better. My question is if an approach like the following makes sense: 1.- With one command we can make the current topology schema the default schema in search path, 2.- then working with topology 3.- Change search_path again when finishing the spatial analysis. (the parameter of the topology name in the functions should be ignored or removed) I know it requires a lot of changes but currently the performance of the persistent topology is too slow. if one do that many functions can be run without EXECUTE. Spatial analysis between different topologies is not possible which fits the procedure. What do you think Sandro? Regards, Jose ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] topology performance improvement purpose
Hi, wanted to share a though about improving the topology performance in postgis. Many of the functions are using EXECUTE instead of running directly select commands. In many cases this is need just because of the used of the schema name of the topology primitive tables. I found between 4 and 10 times improving performance some sentences. I guessing it could be even better. My question is if an approach like the following makes sense: 1.- With one command we can make the current topology schema the default schema in search path, 2.- then working with topology 3.- Change search_path again when finishing the spatial analysis. (the parameter of the topology name in the functions should be ignored or removed) I know it requires a lot of changes but currently the performance of the persistent topology is too slow. if one do that many functions can be run without EXECUTE. Spatial analysis between different topologies is not possible which fits the procedure. What do you think Sandro? Regards, Jose ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
