Does the value of a indexed field affects performance?

user_15868598 0
Viewed 38

I have the following table:

create type size_type as enum('tiny', 'small', 'tall');

CREATE TABLE IF NOT EXISTS people ( 
    size size_type NOT NULL 
);

Imagine it has tons of data. If I index the size field, the length of the strings on the enum will affect the performance of the database when executing queries? For example, ('ti','s',ta') will be more performatic than ('tiny', 'small', 'tall') or it doesn't matter?

postgresql
1 Answers

It does not matter at all. Under the hood, enum values are stored as real (4-byte floating point numbers), regardless of the label text.

The documentation offers the following explanation:

An enum value occupies four bytes on disk. The length of an enum value's textual label is limited by the NAMEDATALEN setting compiled into PostgreSQL; in standard builds this means at most 63 bytes.

The translations from internal enum values to textual labels are kept in the system catalog pg_enum. Querying this catalog directly can be useful.

user_64643080
Related