Re: Varchar vs foreign key vs enumerator - table and index size

Поиск
Список
Период
Сортировка
От Vitalii Tymchyshyn
Тема Re: Varchar vs foreign key vs enumerator - table and index size
Дата
Msg-id CABWW-d28Y9HiLAvnj5BDm-yeMXr61aWv3HiUQtKj1_V-+DoYnA@mail.gmail.com
обсуждение исходный текст
Ответ на Varchar vs foreign key vs enumerator - table and index size  (Łukasz Walkowski <lukasz.walkowski@homplex.pl>)
Ответы Re: Varchar vs foreign key vs enumerator - table and index size  (Łukasz Walkowski <lukasz.walkowski@homplex.pl>)
Список pgsql-performance
Дерево обсуждения



2013/8/31 Łukasz Walkowski <lukasz.walkowski@homplex.pl>

3. And this part is most interesting for me. Columns browser, eventsource, eventtype, devicetype, operatingsystem contain a small pool of strings - for example for devicetype this is set to Computer, Mobile, Tablet or Unknown. Browser is set to normalized browser name. In every case I can store those data using one of 3 different methods:


Well, there are some more options:
a) Store int keys and do mapping in the application (e.g. with java enums). This can save you a join, that is especially useful if you are going to do paged output with limit/offset scenario. Optimizer sometimes produce suboptimal plans for join in offset/limit queries.
b) Store small varchar values as keys (up to "char" type if you really want to save space) and do user display mapping in application. It's different from (a) since it's harder to mess with the mapping and values are still more or less readable with simple select. But it can be less efficient than (a).
c) Do mixed approach with mapping table, loaded on start into application memory. This would be an optimization in case you get into optimizer troubles.

Best regards, Vitalii Tymchyshyn

В списке pgsql-performance по дате отправления:

Предыдущее
От: Craig James
Дата:
Сообщение: Re: Varchar vs foreign key vs enumerator - table and index size
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: How clustering for scale out works in PostgreSQL