Grokbase › Groups › PostgreSQL › pgsql-performance › June 2011
FAQ
Hi,

We did a benchmark comparing a Key-Value-Pairs stored as EAV db schema
versus hstore.
The results are promising in favor of hstore but there are some
question which remain.

1. Obviously the '@>' has to be used in order to let use the GiST index.
Why is the '->' operator not supported by GiST ('->' is actually
mentioned in all examples of the doc.)?

2. Currently the hstore elements are stored in order as they are
coming from the insert statement / constructor.
Why are the elements not ordered i.e. why is the hstore not cached in
all hstore functions (like hstore_fetchval etc.)?

3. In the source code 'hstore_io.c' one finds the following enigmatic
note: "... very large hstore values can't be output. this could be
fixed, but many other data types probably have the same issue."
What is the max. length of a hstore (i.e. the max. length of the sum
of all elements in text representation)?

4. Last, I don't fully understand the following note in the hstore
doc. (http://www.postgresql.org/docs/current/interactive/hstore.html
):
Notice that the old names are reversed from the convention
formerly followed by the core geometric data types!
Why names? Why not rather 'operators' or 'functions'?
What does this "reversed from the convention" mean concretely?

Yours, Stefan

Search Discussions

  • Robert Haas at Jul 19, 2011 at 8:08 pm ⇧

    On Sun, Jun 19, 2011 at 2:59 PM, Stefan Keller wrote:
    1. Obviously the '@>' has to be used in order to let use the GiST index.
    Why is the '->' operator not supported by GiST ('->' is actually
    mentioned in all examples of the doc.)?
    Because it's not a comparison operator.
    2. Currently the hstore elements are stored in order as they are
    coming from the insert statement / constructor.
    Why are the elements not ordered i.e. why is the hstore not cached in
    all hstore functions (like hstore_fetchval etc.)?
    Putting the elements in order wouldn't really help, would it? I mean,
    you'd need some kind of an index inside the hstore... which there
    isn't.
    3. In the source code 'hstore_io.c' one finds the following enigmatic
    note: "... very large hstore values can't be output. this could be
    fixed, but many other data types probably have the same issue."
    What is the max. length of a hstore (i.e. the max. length of the sum
    of all elements in text representation)?
    I think that anything of half a gigabyte or more is at risk of falling
    down there. But probably it's not smart to use such big hstores
    anyway.
    4. Last, I don't fully understand the following note in the hstore
    doc. (http://www.postgresql.org/docs/current/interactive/hstore.html
    ):
    Notice that the old names are reversed from the convention
    formerly followed by the core geometric data types!
    Why names? Why not rather 'operators' or 'functions'?
    It's referring to the operator names.
    What does this "reversed from the convention" mean concretely?
    That comment could be a little more clear, but I think what it's
    saying is that hstore's old @ is like the core geometic types old ~,
    and visca versa.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Stefan Keller at Jul 19, 2011 at 9:07 pm ⇧
    Hi Robert

    Many thanks for your answers.

    2011/7/19 Robert Haas <robertmhaas@gmail.com>:
    Putting the elements in order wouldn't really help, would it? I mean,
    you'd need some kind of an index inside the hstore... which there
    isn't.
    Sorry for my inprecise question. In fact elements of a hstore are
    stored in order of (keylength,key) with the key comparison done
    bytewise (not locale-dependent). See e.g. function hstoreUniquePairs
    in http://doxygen.postgresql.org/ . This ordered property is being
    used by some hstore functions but not all - and I'm still wondering
    why.

    Yours, Stefan


    2011/7/19 Robert Haas <robertmhaas@gmail.com>:
    On Sun, Jun 19, 2011 at 2:59 PM, Stefan Keller wrote:
    1. Obviously the '@>' has to be used in order to let use the GiST index.
    Why is the '->' operator not supported by GiST ('->' is actually
    mentioned in all examples of the doc.)?
    Because it's not a comparison operator.
    2. Currently the hstore elements are stored in order as they are
    coming from the insert statement / constructor.
    Why are the elements not ordered i.e. why is the hstore not cached in
    all hstore functions (like hstore_fetchval etc.)?
    Putting the elements in order wouldn't really help, would it?  I mean,
    you'd need some kind of an index inside the hstore... which there
    isn't.
    3. In the source code 'hstore_io.c' one finds the following enigmatic
    note: "... very large hstore values can't be output. this could be
    fixed, but many other data types probably have the same issue."
    What is the max. length of a hstore (i.e. the max. length of the sum
    of all elements in text representation)?
    I think that anything of half a gigabyte or more is at risk of falling
    down there.  But probably it's not smart to use such big hstores
    anyway.
    4. Last, I don't fully understand the following note in the hstore
    doc. (http://www.postgresql.org/docs/current/interactive/hstore.html
    ):
    Notice that the old names are reversed from the convention
    formerly followed by the core geometric data types!
    Why names? Why not rather 'operators' or 'functions'?
    It's referring to the operator names.
    What does this "reversed from the convention" mean concretely?
    That comment could be a little more clear, but I think what it's
    saying is that hstore's old @ is like the core geometic types old ~,
    and visca versa.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Robert Haas at Jul 22, 2011 at 5:08 pm ⇧

    On Tue, Jul 19, 2011 at 5:06 PM, Stefan Keller wrote:
    2011/7/19 Robert Haas <robertmhaas@gmail.com>:
    Putting the elements in order wouldn't really help, would it?  I mean,
    you'd need some kind of an index inside the hstore... which there
    isn't.
    Sorry for my inprecise question. In fact elements of a hstore are
    stored in order of (keylength,key) with the key comparison done
    bytewise (not locale-dependent). See e.g. function hstoreUniquePairs
    in http://doxygen.postgresql.org/ . This ordered property is being
    used by some hstore functions but not all - and I'm still wondering
    why.
    Not sure, honestly. Is there some place where it would permit an
    optimization we're not currently doing?

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJun 19, '11 at 6:59p
activeJul 22, '11 at 5:08p
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Stefan Keller: 2 posts Robert Haas: 2 posts

Content

People

Support

Translate

site design / logo © 2022 Grokbase