On Thu, Feb 2, 2012 at 10:21 PM, Robert Haas
<robertmhaas@gmail.com> wrote:
I'm having trouble figuring out under what set of circumstances spgist
is expected to be the best available alternative. It only supports a
small subset of the data types that GiST does, so I suppose the point
is that it should be faster for the cases that it does handle. And,
considering that this is all brand-new code, the fact that it's almost
keeping pace with btree on both pattern-matching and equality
comparisons is certainly respectable -- but I so far haven't found any
cases where it's a clear win. There's limited glory in being the
almost-fastest way of indexing for a certain class of queries.
I think that current implementation of suffix tree (in particular implementation of consistent methods) in spgist is far from reveal full potential of suffix tree. I see at least two additional search types which can be efficiently evaluated using suffix tree:
1) Search by prefix regular expression. For example, search for /^a(bc|d)e[fgh]/ in single index scan.
2) Search by levenshtein distance, i.e. "SELECT * FROM tbl WHERE levenshtein(col, 'some constant') < k;" using index (surely actual syntax for such index scan would be anouther).
This is only two additional applications which first comes to my mind, there could be other. Unfortunately, I don't have enough of time for it now. But I like to put my hands on this in future.
Admittedly, I haven't tried the point-in-box stuff yet.
In short, I believe spgist for geometrical data-types is more CPU-effective and less IO-effective than gist. Gist have to call consistent method for all index tuples of the page it uses in scan, while spgist scans smaller nodes. Sure, comprehensive testing is required for doing final conclusion about that.
-----
With best regards,
Alexander Korotkov.