As for the indexing stuff we already thought many times about key&value mixing, but real solution, probably, could come from spgist and gin combination. I mean, spgist (suffix array) instead of btree for avoiding key duplication, which is real stopper for key.value mixing, especially, for deep nesting structures. We'll research further and probably will develop a prototype of such hybrid search tree.
Oleg
On Mon, Aug 26, 2013 at 6:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The combined entry is used to support "contains (@>)" queries, and the key >> only item is used to support "key contains (?)" queries. This change seems >> to help especially with hstore keys that have high cardinalities. Downsides >> of this change is that it requires an index rebuild, and the index will be >> larger in size.
> Index rebuild would be a problem only for minor releases,
That's completely false; people have expected major releases to be on-disk-compatible for several years now. While there probably will be future releases in which we are willing to break storage compatibility, a contrib module doesn't get to dictate that.
What might be a practical solution, especially if this isn't always a win (which seems likely given the index-bloat risk), is to make hstore offer two different GIN index opclasses, one that works the traditional way and one that works this way.
Another thing that needs to be taken into account here is Oleg and Teodor's in-progress work on extending hstore: https://www.pgcon.org/2013/schedule/events/518.en.html I'm not sure if this patch would conflict with that at all, but it needs to be considered.