Opened 12 years ago

Closed 12 years ago

#1522 closed enhancement (fixed)

[RASTER] ST_MapAlgebraExpr : use prepared queries

Reported by: strk Owned by: dzwarg
Priority: medium Milestone: PostGIS 2.0.0
Component: raster Version: master
Keywords: Cc:

Description

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

Change History (8)

comment:1 by Bborie Park, 12 years ago

Owner: changed from pracine to dzwarg

comment:2 by pracine, 12 years ago

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

comment:3 by Bborie Park, 12 years ago

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

comment:4 by pracine, 12 years ago

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?

comment:5 by Bborie Park, 12 years ago

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.

comment:6 by dzwarg, 12 years ago

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.

comment:7 by Bborie Park, 12 years ago

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.

comment:8 by dzwarg, 12 years ago

Resolution: fixed
Status: newclosed

Added in r9068.

Note: See TracTickets for help on using tickets.