Speeding up a Query

Question

I am using PostGIS/Tiger and I would like to find all locations within ~2 miles (~3218 meters) of a given location.

The location in question is obtained by just doing

select geography(the_geom) FROM tract WHERE tract_id = 'x'

So putting this query all together, we get:

SELECT tract_id FROM tract, 
        (SELECT geography(the_geom) FROM tract 
         WHERE tract_id = '36047055600') AS c(x) 
WHERE  ST_DWithin(c.x, geography(the_geom), 3218)

So I'm using ST_DWithin to find all the neighbors.

My query works great but is taking 14 seconds. I put an index on 'the_geom' column in my tract table, but it doesn't seem to help.

How can I improve my query?

Answer 1

The index on the_geom is close to useless. Since you are using geography(the_geom), you should index this.

CREATE INDEX geog_idx ON tract  USING gist (geography(the_geom));

Alternatively, you could just have a geography column.