GIST Index Expression based on Geography Type Column Problems

Question

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?

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
– Twelfth, Commented Jun 26, 2012 at 20:38

Stefan · Accepted Answer · 2012-06-27 12:54:15Z

1

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

answered Jun 27, 2012 at 12:54

Stefan

1,0461 gold badge10 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike T · Accepted Answer · 2012-06-28 07:02:22Z

0

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

answered Jun 28, 2012 at 7:02

Mike T

44.3k18 gold badges166 silver badges213 bronze badges

2 Comments

Marcin Kapusta Over a year ago

Mike thanks for Your reply. I know that. This is not the solution for my situation. Some places need to have buffer smaller than 5000m in some cases and larger in some special cases. With ST_DWithin I have only one value (distance from the route) along the route. I know I didn't mention about this but the question is about why postgres didn't use the gist index based on expression.

Mike T Over a year ago

Got it: when the third parameter of ST_DWithin (the distance) is variable, the use of the index is lost. Interesting.

Collectives™ on Stack Overflow

GIST Index Expression based on Geography Type Column Problems

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related