I am using PostgreSQL 9.5 and I'm trying to understand how to implement a GiST index where I have a representation that is a lossily compressed version of the indexed type. For instance, say I have images stored in
BYTEA type, and for the index I store the colour ranges (rmin, rmax, gmin, gmax, bmin, bmax), and I want to compare images based upon colour similarity - e.g. with a
=== operator that returns true when the colours ranges are exactly the same, allowing me to facilitate queries like:
SELECT COUNT(*) FROM icons, avatars WHERE icon.image === avatar.image AND avatar.id = 123;
avatars are both tables with an
image field of type
Having looked through the implementation documentation is looks like this should be possible. Using the above example situation, I think I could do as follows:
unionmethod would generate the bounding range of all entries
penaltywould just try to minimise the ranges, similar to a R-Tree
compresswould take the BYTEA data and calculate the colour range
decompresswould be an identity function
===operator) would return true if the entry's colour range contained the query range for internal nodes, and only if the ranges exactly matched for leaf nodes.
Is this the right approach? I'm not clear about when the compression steps take place. For instance,
consistent is presumably called multiple times on different nodes of the tree. So does this mean that the query will have re-calculate the colour range of the query data every time? And in the index, will the leaf nodes contain a copy of the image data or just its colour range?
Note The example given is just for illustrative purposes. My question is about lossy representations in GiST, not indexing images.