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@xxxxxxxxxx | company 2 | 1 |E001| Sunday Inc. |Paul | Smith | 77777| smith@xxxxxxxxxx| employee 3 | NULL |P001| NULL | Rose | Mary | 66666| rose@xxxxxxxx | 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
