join by char(16) or by bytea?
One more newbie question.
Due to limitations of both pg and php I gave up on the attempts to
make bigint keys work; I have a choice between 16-byte character
strings or "8-byte" bytea (decode(string_from_above, "hex")) for an
index column for a table.
Which one will be faster for exact matches and joins? Tables will be
big,
there will be no range queries and no partial matching.
Again I cannot test it because I lack production data, I wonder if
it's common knowledge?
"Sergei Shelukhin" <realgeek@gmail.com> writes:
Due to limitations of both pg and php I gave up on the attempts to
make bigint keys work;
Which limitations would those be?
I have a choice between 16-byte character
strings or "8-byte" bytea (decode(string_from_above, "hex")) for an
index column for a table.
Which one will be faster for exact matches and joins?
bytea, likely, especially if you are using a non-C locale. In C locale
the charstring comparisons would degenerate to memcmp and probably not
be measurably different from bytea, but in other locales strcoll is
used and that can be pretty slow. OTOH, the pain-in-the-neck factor
for using decode in all your queries seems pretty bad, so I'm really
wondering what problem you've got with bigint.
regards, tom lane