On 06/12/2013 11:52 AM, Graeme B. Bell wrote: > Hi again bborie, > > "in-db and out-db does matter. Your SQL asks the database to fetch some > data and the size of said data (and where that data is) will affect the > performance of the SQL." > > I agree that in-DB vs out-DB has a general effect upon performance depending > on the type of work being done. > > But the question I am asking is about the relevant > convenience/wisdom/performance/robustness of different query structures, > which will access all metadata values and all pixel data values. > > In terms of robustness, for example, geometry data is floating point. I am > naturally wary about stability of floating point algorithms and comparisons > in terms of the final decimal position, and moreso when I'm dealing with > giga-rows of comparisons. How would I know if a row went missing? > > So I'm asking about the relative merits of e.g. addressing each pixel by > geographic coordinates vs. calling st_extent vs. comparing top-left corners > (and now vs the ::geometry cast you suggest). > > Your suggestion of testing geometry equality is really nice, I like it. > > I'll need to benchmark it to see what the performance is like. > (A.rast::geometry = B.rast::geometry) > > I don't think I agree with this statement though: > > "This check will be faster on out-db vs in-db as there's less data > for postgres to load regardless of your SQL." > > For this work, I'm going to be hitting every pixel in the data either way, as > well as the metadata. > > So I think it will probably make not a great deal of difference unless the > query is badly planned and gets all the metadata first, clears the data out > of cache, and then reloads the rows again to get all the pixel values. > > My choice of in-DB is particular influenced by this post by Pierre Racine, > which states in-DB rasters yielded better performance for accessing pixel > values through SQL, which is what is being done here. > > http://lists.osgeo.org/pipermail/postgis-users/2012-May/033882.html > > "I think most functions accessing the pixel value now works seamlessly but > they should be slower than if the complete raster (with pixel values) would > be stored in the DB." > > It seems to contradict your opinion about faster performance for out-DB > rasters in my use case. > > However it dates back to a year ago, so I don't know if it describes the > status quo. > > Thanks again for your suggestions, very thought-provoking. > > Graeme. >
The reality is that you'll need to test and see what works best on your hardware. I know of situations where in-db is faster and others where out-db wins. The same goes for tile size where bigger is sometimes better. -bborie _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
