Indexing DB data using MVT Indices

Question

I have a dataset of ~5M records that I want to be able to filter and display dynamically on a map in a web application. Based on When to use GiST and when to use SP-GiST index? I have used a few different spatial indices to test performance and it seems like SP-GiST represents the fastest performance from practical tests.

However, based on the way the data is being queried (by MVT extent), it seems like indexing the data on that scheme would produce much faster performance. Is there a way to (relatively) easily implement this, and test this hypothesis?

Answer 1

The query for a tile is a bbox and index like GiST is pretty much made for that purpose but you seem to think that because all the possible bbox:es are known beforehand by the tiling schema it could be even faster.

I would test the hypothesis by creating a table that contains the tile ID and a list of IDs of all the features that intersect with the tile. Then you will have a direct access from tile ID into corresponding feature IDs.

I suppose that finding the data even with standard index is much faster than it is to create the MVT so the overall gain in performance is probably small but it would be interesting to see your results.