[postgis-users] speed of query

[Michael Smedberg]

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.





On Tue, Mar 1, 2011 at 11:40 AM, Puneet Kishor <punk.kish at gmail.com> wrote:

> I have a table with ~ 13.25 million points.
>
> CREATE TABLE ll (
> gid serial NOT NULL,
> latitude double precision,
> longitude double precision,
> a integer,
> b integer,
> the_geom geometry,
> CONSTRAINT ll_pkey PRIMARY KEY (gid),
> CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
> CONSTRAINT enforce_geotype_the_geom CHECK (
> geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL
> ),
> CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163)
> )
> WITH (
> OIDS=FALSE
> );
>
> I want to select the columns a,b for the rows that lie within a box made by
> points [-91.048, 45.956] and [-90.973, 46.007]. Here are my results --
>
> Query 1
>
> 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))
> )
>
> 31 rows returned in 46125 ms
>
> Query 2
>
> SELECT a, b FROM ll
> WHERE
> ST_X(ST_Transform(the_geom, 4326)) >= -91.048 AND
> ST_X(ST_Transform(the_geom, 4326)) <= -90.973 AND
> ST_Y(ST_Transform(the_geom, 4326)) >= 45.956 AND
> ST_Y(ST_Transform(the_geom, 4326)) <= 46.007
>
> 31 rows returned in 25729 ms
>
> Query 3
>
> SELECT a, b FROM ll
> WHERE
> longitude >= -91.048 AND
> longitude <= -90.973 AND
> latitude >= 45.956 AND
> latitude <= 46.007
>
> 31 rows returned in 4011 ms
>
> Query 4
>
> I also have the same data in a SQLite database with an R*Tree index on
> lat/lon. A query analogous to Query 3 returns fast enough to not even
> register a time... a few milliseconds; effectively 0 seconds.
>
> What gives?
> --
> Puneet Kishor
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110301/42354d6a/attachment.html>