Table Inheritance / VARCHAR search question
От | Jonathan Vanasco |
---|---|
Тема | Table Inheritance / VARCHAR search question |
Дата | |
Msg-id | 0640DDC8-CF6F-4095-BA2F-6D1E1CD86E06@2xlp.com обсуждение исходный текст |
Ответы |
Re: Table Inheritance / VARCHAR search question
Re: Table Inheritance / VARCHAR search question |
Список | pgsql-general |
Hi, I'm hoping someone on this list can save me some unnecessary benchmarking today I have the following table in my system BIGSERIAL , INT , INT, VARCHAR(32) There are currently 1M records , it will grow to be much much bigger. It's used as a search/dispatch table, and gets the most traffic on my entire app. I'm working on some new functionality, which will require the same 3 colums as above but with 3 new VARCHAR(32) columns BIGSERIAL , INT , INT, +VARCHAR(32) , +VARCHAR(32) , +VARCHAR(32) ie, the new function shares the same serial and the the 2 INT columns I'm trying to get this to work efficiently on speed and on disk space. i've figured that my options are: a) one table with everything in it pro: simple possible con: when i had something similar in mysql 4 years ago, i had to make all the varchars chars , because speed was awful. under this system, 80% of the 3 new VARCHAR fields will always be null, so that disk waste will be noticable. thats only IF there is a speed issue with VARCHAR searching. b) keep current table, create new table that inherits and has the 3 new fields pro: simple possible con: i can't find any documentation on how an inherit works behind the scenes. is the data cloned into the new table? is there a join on every search? if this is constantly doing a join behind the scenes, thats probably not going to work for me c) move to a 3 table structure table1- serial table2 - current table, bigserial is not bigint table3- bigint + 3 varchars pro: obviously will work con: a lot of restructuring i was going to have both table share a seqeunce, but then i remembered that the id is foreign keyed by other tables if anyone can offer a suggestion, i'd be greatly appreciative
В списке pgsql-general по дате отправления: