[postgis-users] speed of query

[Puneet Kishor]

On Tuesday, March 1, 2011 at 3:30 PM, Michael Smedberg wrote: 
> 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
> 

I should have added the following information --

CREATE INDEX ll_the_geom_gist
ON ll
USING gist
(the_geom);

I do have an index. Ironically, I don't have an index on longitude and latitude columns, yet, searching on those columns is faster than on the_geom.

> 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
>