extensible enum types
in [PgSql]
|
From: Peter Geoghegan on 20 Jun 2010 12:32 >> People sometimes represent things like US states as enums. This is >> probably a mistake, because you cannot control or predict if >> there'll be a new US state, unlikely though that me be. > > More importantly, you're likely to need to associate properties with > the state. �Sales tax info, maybe a sales manager, etc. �A state > table can be a handy place to store things like that. That's probably true, but if there was any question of needing to associate such values with US states, it ought to be perfectly obvious to everyone that enums are totally inappropriate. If that wasn't the case, then their use is only highly questionable, at least IMHO. What you're describing isn't really a lookup table as I understand the term. It's just a table. Lookup tables typically have things in them like the various possible states of another table's tuples. In my experience, lookup tables generally have two columns, an integer PK and a description/state. >> I don't like the idea of having values in a table that aren't so >> much data as an integral part of your application/database. > > Yep, exactly why natural keys should be used when possible. The "not having to remember lookup value PK" point I made was very much ancillary to my main point. Ideally, if you restore a schema-only dump of your database, you shouldn't be missing anything that is schema. Things like the possible states of a table's tuples are often schema, not data, and should be treated as such. -- Regards, Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 21 Jun 2010 11:01 Andrew Dunstan <andrew(a)dunslane.net> writes: > Tom Lane wrote: >> Well, having to do a cache lookup already makes it a couple orders of >> magnitude more expensive than an OID comparison. However, it's hard to >> say how much that matters in terms of total application performance. >> We really could do with a bit of performance testing here ... > I have done some. The performance hit is fairly horrible. Adding cache > lookups for the enum rows to the comarison routines made a REINDEX on a > 1m row table where the index is on an enum column (the enum has 500 > randomly ordered labels) jump from around 10s to around 70s. Hmmm... that's bad, but I bet it's still less than the cost of comparing NUMERICs. Also, did you make any attempt to avoid repetitive cache lookups by storing a pointer in fn_extra (cf array comparisons)? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Dunstan on 21 Jun 2010 12:28 Tom Lane wrote: >> Adding cache >> lookups for the enum rows to the comarison routines made a REINDEX on a >> 1m row table where the index is on an enum column (the enum has 500 >> randomly ordered labels) jump from around 10s to around 70s. >> > > Hmmm... that's bad, but I bet it's still less than the cost of comparing > NUMERICs. Also, did you make any attempt to avoid repetitive cache > lookups by storing a pointer in fn_extra (cf array comparisons)? > > > No. Will work on that. Thanks. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Kevin Grittner" on 21 Jun 2010 13:04 Peter Geoghegan <peter.geoghegan86(a)gmail.com> wrote: > In my experience, lookup tables generally have two columns, an > integer PK and a description/state. Eek. If that's what you consider a lookup table, I wouldn't advocate their use for anything. Ever. Period. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 21 Jun 2010 13:11
On Mon, 2010-06-21 at 12:04 -0500, Kevin Grittner wrote: > Peter Geoghegan <peter.geoghegan86(a)gmail.com> wrote: > > > In my experience, lookup tables generally have two columns, an > > integer PK and a description/state. > > Eek. If that's what you consider a lookup table, I wouldn't > advocate their use for anything. Ever. Period. Do you mean you don't use relational modelling, or do you mean you would never implement your physical database that way because of the performance impact of RI on PostgreSQL? Or? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |