Skip site navigation (1) Skip section navigation (2)

Re: Question on simulating Enum Data type

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: "Morgan Kita" <mkita(at)verseon(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Question on simulating Enum Data type
Date: 2005-03-18 02:39:46
Message-ID: e7da3356126afd232d110af420441a87@myrealbox.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Mar 18, 2005, at 11:18, Morgan Kita wrote:

> However, I am a little concerned about performance in that case. If I 
> use domain constraints and keep the choices as strings then a string 
> comparison will be done whenver I query on this field right? I know an 
> index will speed this up quite a bit but even so I may have to do 10s 
> of thousands of string compares if there are only 5 choices right?
>
> Ideally wouldn't it be better to store an integer field in the tables, 
> and then keep a seperate small map table? Then the application could 
> use the map table to look up the key and then do a query on the large 
> table using only integer compares?
>
> Am I just being silly or am I not understanding something here? Maybe 
> there is another way to do this?

What you've described are the two common ways to approach this 
situation. To accurately know which will be performant in your 
situation, I think you'll need to run benchmarks on your system. I 
don't know whether any such comparison has been done as a reference. 
(Yours could be a good one :) Perhaps someone else out there might have 
experience with this or knowledge of the backend and chime in.

Michael Glaesemann
grzm myrealbox com


In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2005-03-18 02:58:15
Subject: Re: Question on simulating Enum Data type
Previous:From: Morgan KitaDate: 2005-03-18 02:18:33
Subject: Question on simulating Enum Data type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group