[postgis-users] speed of query

[Michael Smedberg]

It looks like maybe you don't have an index on your the_geom column?  If so,
you might want to read the "Indexing the data" section of
http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01

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/1d965cb6/attachment.html>