Re:Cost of a Join
by davidgould on Wednesday August 15, 2018 @05:30AM
(#17764730)
It is logically a join on oid to the pg_enum table. The implementation takes a few shortcuts but really the basic join machinery has been hand polished for decades so that doesn't make much difference.
Because enums are stored in pg_enum which is just a table like the rest of the catalogs you can join to it with your own SQL queries. If you are very bold and a bit bad superuser you can even update them.
Except enum abstracts away the need to create a join. Plus performance of enum is a tad bit better.
Re:Cost of a Join
by gm-conspiracy on Wednesday August 15, 2018 @09:29AM
(#17765852)
How is performance when adding a new, additional enumerated value to the table that already has millions of rows?
edit: It is different, as all enum values are rows in the same table and there is a third column to identify which type they belong to. The actual key used to refer to an enum value is not in the table; it's the oid of the table entry. So, I guess retrieving the data from the enum table is technically not a join.