Why is pgraster much slower?

Question

This is a follow up question to my other question: How to speed up raster/map algebra in PostGIS?

I am much attracted to the way how PostGIS handle vectors and rasters in an interoperable manner. A practical issue though (as shown in the link above), is that pqraster is often much slower in many operations when compared to other mainstream packages such as ArcGIS.

My question is specific about the algorithms and methods implementing pgraster (e.g. map algebra):

Are there specific, technical reasons that make pgraster necessarily much slower than, e.g. ArcGIS, or is the slowness only because of a temporary issue or lack of interests/optimization?

For example, is there something inherently more costly (time consuming) in storing regular grid data in a relational database (as with PostGIS) than using a file system (as with ArcGIS)?

I remember reading somewhere that someone joked about pgraster was going to remain slow for the next 10 years, which is why I am curious.

Can someone with the expertise shed some light into this?

Answer 1

It's a little bit of all you mentioned.

ST_MapAlgebra is particularly slow because, it works in a single threaded mode (because of PostgreSQL), and it's just kind of inefficient. It also depends which PL language you use. I've found PL/V8 to be the best (better than plpgsql and sql). sql is a better function language than plpgsql for map algebra. Expression MapAlgebra is the slowest (which the link you provided was using) so I would just avoid that.

That said - people always jump to ST_MapAlgebra when they can achieve the same with ST_Reclass or ST_Union which are both orders of magnitude faster than ST_MapAlgebra mostly because more time has been spent optimizing them.

Now as to if storing raster in db is less efficient. Yes. Mostly because of the toast / detoast operations. Some of that you avoid by using the out-db mode, but that has trade-offs too depending on which functions you use with it.

There is also a limitation in terms of tile size and matrix size caused by limitations in PostgreSQL architecture which you don't have with file / ArcGIS.

A lot of these will improve without any effort on PostGIS side as PostgreSQL improves. For example though raster can't really use the parallel support to any great degree introduced in PostgreSQL 9.6, this may very well change in PostgreSQL 10, which could easily make PostGIS raster 10 times faster.

I should add that PostGIS raster got orders of magnitude faster between 2.0 and 2.1 for many operations.