Please suggest me on my table design (indexes!)

Поиск
Список
Период
Сортировка
От DaNieL
Тема Please suggest me on my table design (indexes!)
Дата
Msg-id c52c7f0f-2e62-4e24-a372-406956c0c6be@y17g2000yqn.googlegroups.com
обсуждение исходный текст
Ответы Re: Please suggest me on my table design (indexes!)  (justin <justin@emproshunts.com>)
Список pgsql-general
Hi guys, im tryin to optimize a simple table, suited for contain
users.
So, my table at the moment is:

-----
CREATE TABLE contacts(
 id BIGSERIAL PRIMARY KEY NOT NULL UNIQUE,
 company_id BIGINT,
 code varchar(10),
 company_name varchar(120),
 name varchar(120),
 surname varchar(120),
 phone varchar(80),
 email varchar(80),
 kind varchar(8)
);
-----

I use this layout in order to store 3 kind of users: Private, Company
and Company's Employee.. the col 'kind' infact will contain just
'private', 'company' or 'employee', but is unnecessary, i can
understand what kind a user is by those rules:
Private are stand-alone users, company_name and company_id are always
NULL;
Company have the company_name;
Employees have the company name and the company_id (with the id of the
company's row);

Example:

id|company_id|code| company_name|name|surname|phone|
email               |   kind
1 |NULL         |C001| Sunday Inc.     | John | Doe      |88888 |
j@sunday.com | company
2 | 1              |E001| Sunday Inc.     |Paul  | Smith   | 77777|
smith@sunday.com| employee
3 | NULL        |P001| NULL              | Rose | Mary   | 66666|
rose@mary.com |  private

So, first of all, does this layout looks good?
Before i used to keep employees in a different table, becose usually
employees have just few data (name, surname, direct email and direct
phone.. all the addresses, bank data, etc.. belongs tot he company),
but noe i preferred this way to avoid constant inner joins.

Now, i aspect that my users will search the contact table just for the
fields company_name, name, surname, email, code.
That kind of query cant be as
WHERE company_name = '$x'
but will be much like
WHERE company_name LIKE '$x%',
both becose i use an autocomplete field for the quick search, both
becose.. well, that's how users search data's (in my experience).

So i created those index, to let the query planner use the indexes in
the LIKE query:
-----
CREATE INDEX contact_companyname_i ON contact USING btree (lower
(company_name::text) varchar_pattern_ops)
#this kind of index is the same for name, surname
#the email must be unique:
CREATE UNIQUE INDEX contact_email_i ON contact USING btree (lower
(email::text) varchar_pattern_ops);
-----

Therefore the phone column will be searched with the LIKE condition,
but dont need the case-sensitive-loser-trick:
-----
CREATE INDEX contact_n_phone_i ON contact USING btree (phone
varchar_pattern_ops)
-----

However for the email, code, id and company_id columns i want to
permit an index search even with the exact pattern, so i added those
indexes too:
-----
CREATE INDEX contact_n_email_i ON contact USING btree (email);
CREATE INDEX contact_n_code_i ON contact USING btree (code);
CREATE UNIQUE INDEX contact_pkey ON contact USING btree (id); #PRIMARY
KEY
CREATE INDEX contact_n_idcompany_i ON contact USING btree (id_company)
-----

Again, feel free to suggest/insult me if this behavior looks bad (im
here to learn ;)
Well, the table will have other fields that dont need to be searched
directly (like the addresses) and so dont need indexes.

My doubt is: am i using too many indexes?
Will my insert/delete/update queryes be too slow, and does the select
optimization worth the price? (and, does this way really optimize the
selects queryes?)

Consider that my application wont do many insert-delete-update
sequentially.

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

Предыдущее
От: Grzegorz Jaśkiewicz
Дата:
Сообщение: Re: after vacuum analyze, explain still wrong
Следующее
От: "DaNieL..!"
Дата:
Сообщение: Re: Please suggest me on my table design (indexes!)