[postgis-users] speed of query

[Mark Cave-Ayland]

On 01/03/11 21:48, Michael Smedberg wrote:

> OK, next guess.  It looks like your SQL might be pretty inefficient.
>   For example, consider this SQL statement:
>
> SELECT a, b FROM ll
> WHERE
> ST_Within(
> ST_Point(
> ST_X(ST_Transform(the_geom, 4326)),
> ST_Y(ST_Transform(the_geom, 4326))
> ),
> ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007))
> )
>
> I think that says something like:
> Look through the II table
> For each row, transform the_geom to 4326, and take the X
> For each row, transform the_geom to 4326, and take the Y
> Make a point from that X,Y
> Check if that point is within a box
>
> I don't think that'll use an index, and I think it will do a bunch of
> transformation work for every row.
>
> I think that instead of transforming every row in II to 4326, you'd
> probably be better served by transforming your bounding box to 2163 one
> time.  I think the SQL would look something like this:
>
> SELECT
> a,
> b
> FROM
> ll
> WHERE
> ST_Within(
> the_geom,
> ST_Transform(
> ST_MakeBox2D(
> ST_Point(
> -91.048,
> 45.956
> ),
> ST_Point(
> -90.973,
> 46.007
> )
> ),
> 2163
> )
> )
>
>
> In any case, you should probably try looking at the output of EXPLAIN or
> EXPLAIN ANALYZE to understand whether your index is being used, etc.

Hi Michael,

Yes indeed - I think you summarised this brilliantly :)  If your 
geometries are stored as SRID 2163, then the index bounding boxes will 
also be stored in SRID 2163 - hence the index can only be used for 
queries involving other SRID 2163 bounding boxes. Otherwise PostgreSQL 
assumes it has to convert your entire geometry column to SRID 4326 first 
in order to calculate the intersection, which involves scanning the 
entire table and converting all the geometries on the fly...


ATB,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs