[postgis-users] speed of query

[Puneet Kishor]

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