From: | Vitalii Tymchyshyn <tivv00(at)gmail(dot)com> |
---|---|
To: | Łukasz Walkowski <lukasz(dot)walkowski(at)homplex(dot)pl> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Varchar vs foreign key vs enumerator - table and index size |
Date: | 2013-09-01 03:10:45 |
Message-ID: | CABWW-d28Y9HiLAvnj5BDm-yeMXr61aWv3HiUQtKj1_V-+DoYnA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2013/8/31 Łukasz Walkowski <lukasz(dot)walkowski(at)homplex(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2013-09-02 01:38:21 | Re: How clustering for scale out works in PostgreSQL |
Previous Message | Craig James | 2013-09-01 01:31:06 | Re: Varchar vs foreign key vs enumerator - table and index size |