[RASTER] ST_MapAlgebraExpr : use prepared queries

[strk]

SPI_Prepare and SPI_ExecutePrepared would be safer and faster in executing the provided expression.

[pracine]

Just to make sure: The two raster map algebra use prepared queries and the one raster does not. That's it?

[Bborie Park]

Correct. Hopefully dzwarg can take care of this next week at the code sprint.

[pracine]

On the track of optimization: Is there any result caching for when the current pixel has the same value as the previous one (so we don't have to call the interpreter twice)? Are prepared query so smart as they do the caching themselves?

Is there any other ways to optimize the one raster mapalgebra?

[Bborie Park]

I don't believe there is any caching of results, regardless of using prepared statements. Prepared statements cache the execution plan.

The low-hanging fruit for one-raster expression-based mapalgebra is the use of prepared statements.

[dzwarg]

The Postgresql docs state that SPI_Execute will: "Parse, plan, and execute a query string".

Looking at the internals, it seems like SPI_execute creates a plan and executes it the same way that I would go about preparing the plan in order to make this change. Is there something that I'm missing about how prepared statements work that would make explicit plan preparation necessary?

See ​http://doxygen.postgresql.org/spi_8c.html#af7dc8e377b122d8c8758426f14d38889 for sourcecode docs for postgresql.

[Bborie Park]

Take a look at the 2-raster MapAlgebra's use of SPI_Prepare. By preparing the plan, saving it and reusing it, the creation of the plan only happens once instead of every call to SPI_Execute.

[dzwarg]

Added in r9068.