Обсуждение: DATATYPE for HEX

Hello!

I am choosing the best (fastest and smallest) datatype for
storing some 16-byte length application-level binary
identifiers.

This data probably must be indexed. The only operation
I really need is comparision (equal or not) and, probably,
ORDER BY.

Most natural type BINARY(16) is absent in PostgreSQL, so
my first choice was BYTEA. After that I discovered BIT(128)
is about 15% faster then BYTEA and NAME is approximately
10% faster then BIT(128).

NUMERIC is slower then BYTEA and CHAR is much slower (I use UNICODE).

Since NAME is intended for internal usage only, I am about
to switch from BYTEA to BIT.

My question are
1. What is the best datatype for my purposes?
2. Should I expect any hidden dangers here (e.g. some special types
of queries are significantly slower, say, for BIT than for BYTEA)?
3. Are there any general guidelines how to choose most suitable datatype?

Thanks a lot.

--
Best regards
Ilja Golshtein

>I am choosing the best (fastest and smallest) datatype for
>storing some 16-byte length application-level binary
>identifiers.

Addition.
I've SET STORAGE of this column to PLAIN and speed increased about 20%. Any drawbacks of this trick?

PG Doc 49.2 says: "Each TOAST-able data type specifies a default strategy for columns of that data type". How one can
findout these default strategies? 


Thanks.

--
Best regards
Ilja Golshtein

"Ilja Golshtein" <ilejn@yandex.ru> writes:
> PG Doc 49.2 says: "Each TOAST-able data type specifies a default strategy for columns of that data type". How one can
findout these default strategies? 

Look in pg_type, specifically the typstorage column.

Offhand I think NUMERIC is the only one that doesn't default to full
EXTENDED storage.

            regards, tom lane