GIST Index Expression based on Geography Type Column Problems

4
votes

I have question about how postgresql use indexes. I have problems with Gist Index Expression based on Geography Type Column in Postgresql with Postgis enabled database.

I have the following table:

CREATE TABLE place
(
  id serial NOT NULL,
  name character varying(40) NOT NULL,
  location geography(Point,4326),
  CONSTRAINT place_pkey PRIMARY KEY (id )
)

Then I created Gist Index Expression based on column "location"

CREATE INDEX place_buffer_5000m ON place
USING GIST (ST_BUFFER(location, 5000));

Now suppose that in table route I have column shape with Linestring object and I want to check which 5000m polygons (around the location) the line crosses.

The query below in my opinion shoud use the "place_buffer_5000m" index but does not use it.

SELECT place.name
FROM place, route
WHERE
  route.id=1 AND
  ST_CROSSES(route.shape::geometry, ST_BUFFER(place.location, 5000)::geometry))

Table place have about 76000 rows. Analyze and Vacuum was run on this table with recreating "place_buffer_5000m" index but the index is not used during the above query.

What is funny when I create another column in table place named "area_5000m" (geograpthy type) and update the table like:

UPDATE place SET area_5000m=ST_BUFFER(location, 5000)

And then create gist index for this column like this:

CREATE INDEX place_area_5000m ON place USING GIST (area_5000m)

Then using the query:

SELECT place.name
FROM place, route
WHERE
  route.id=1 AND
  ST_CROSSES(route.shape::geometry, place.area_5000m::geometry))

The index "place_area_5000m" is used. The question is why the Index expression that is calculated based on location column is not used?

2
postgresqlindexingpostgis
I've never seen st_buffer() used successfully within an index...would appear postGIS is struggling with using a function in an index. The place_area_5000m index makes more sense as you are indexing on a set field and not a function (derived) field. It almost seems as behaving as intended but I may be off...would be really curious if anyone has managed to successfully index by a GIS function before. – Twelfth

2 Answers

1
votes

Did you try to add a cast to your "functional index"? This could help to determine the data type. It should work with geometry and probably also for geography, like this:

CREATE INDEX place_buffer_5000m ON place
USING GIST(ST_BUFFER(location, 5000)::geometry);
0
votes

Ultimately, you want to know what routes are within 5 km of places, which is a really simple and common type of query. However, you are falling into a common trap: don't use ST_Buffer to filter! It is expensive!

Use ST_DWithin, which will use a regular GiST index (if available):

SELECT place.name
FROM place, route
WHERE route.id = 1 AND ST_DWithin(route.shape::geography, place.location, 5000);