Hello list,
An idea for possible improvement of query speed on gist indexes with a
costly compression function. We have a gist index that uses a compressed
internal datatype. The compression is with some cost (it involves a
syscache lookup). The functions called by the consistent functions are
binary functions on the internal type. Since the query parameter passed
to consistent is the 'external' datatype, it must be compressed by the
consistent function. When performing a query, the same data value (the
query) is compressed repeatedly while traversing the nodes in the index
tree. The query speed increased substantially after building in an ad
hoc caching (a hash) in the compression function.
We believe that every gist index that uses compression could benefit
from a cache, even a small one. So it would make sense to add the
caching to the gist support in the backend, instead of having to build
one in the user defined functions gist functions for every datatype.
Any thoughts?
regards,
Yeb Havinga