Please suggest me on my table design (indexes!)

From: DaNieL <daniele(dot)pignedoli(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Please suggest me on my table design (indexes!)
Date: 2009-06-23 07:47:15
Message-ID: c52c7f0f-2e62-4e24-a372-406956c0c6be@y17g2000yqn.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)sunday(dot)com | company
2 | 1 |E001| Sunday Inc. |Paul | Smith | 77777|
smith(at)sunday(dot)com| employee
3 | NULL |P001| NULL | Rose | Mary | 66666|
rose(at)mary(dot)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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message DaNieL..! 2009-06-23 07:51:49 Re: Please suggest me on my table design (indexes!)
Previous Message Prasad, Venkat 2009-06-23 07:38:35 Integrity check