[postgis-users] selected aligned tiles in pgraster between several large raster tables.

[dustymugs]

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