In PostGIS why does spatial index still matter for a table with only points?

Question

According to this Boundless link, it turns out that in PostGIS, spatial query on table with spatial index uses two pass system: with the first pass approximates spatial relationship with bounding box.

Now i was wondering if such approach will be effective for a PostGIS table with geometry type "ST_POINT", as bounding box of a point IS essentially the point itself.

With this in mind, I run an ST_INTERSECT query by Jmeter to 2 tables with exactly the same set of points, and exactly the same intersect BOX. The only difference is one table has spatial index and the other don't.

The result surprises me: the one with spatial index performed much faster.

In my machine, the average response times are:

• with spatial index: 911 ms
• without spatial index: 62038 ms

Now the question: why does spatial index still matter for a table with only points?

Answer 1

The description from Boundless is very simplified. It somehow misses the first important step: The index will be able to filter most points/bounding boxes before running any calculation because of their location in the R-Tree.

R-Trees break up data into rectangles, and sub-rectangles, and sub-sub rectangles, etc. The index will look in which rectangle the data lies, and filters any points that are outside this rectangle, then the same with the sub-rectangles inside that rectangle only and so on...

For a better understanding of Indexes/R-Trees yo might start reading the wikipedia article on that topic.

Answer 2

In your case points do not have any other index and query probably does not even try to use other indexes. BBOX queries can be pretty fast if X and Y coordinates are stored into separate, indexed fields and queries are made to include where X > xmin and X < xmax and Y > ymin and Y < ymax.

There are still loads of old databases which have location points stored only as X and Y coordinates. However, using geometry data type also for points makes it much easier to use the advanced spatial ST-functions. It is not only the speed of BBOX selection that counts.