Re: [otrs] PostgreSQL performance

2 Nov 2004

      Robert L Mathews schrieb:
...
If the patch is accepted to change the schema, I will gladly provide a 
script that can be used to alter the existing database in place, speeding 
up existing PostgreSQL installations dramatically.
(In my case -- now at about 70,000 tickets in the system -- the changes 
sped up the "show me my [small number of] locked tickets" view almost 
20-fold, from about ten seconds to about half a second.)
Same goes for one of my customers installations with about 100000 
articles and 10000 tickets. Yes, it comes in that fast. The customer is 
running OTRS on a Xeon-based 4-processor machine with a 16Gig chunk of 
ram and pleeenty of storage space available. Times were not bearable 
anymore, due to the numerous and highly cost-intensive seuqential scans, 
a QueueView was up to 20 seconds. So long for the story.

We solved this by altering the schema and especially implementing a few 
new indexes. These were:

CREATE INDEX index_article_ticket_id
ON public.article USING btree (ticket_id);
CREATE INDEX index_article_plain_article_id
ON public.article_plain USING btree (article_id);
CREATE INDEX index_group_user_group_id
ON public.group_user USING btree (group_id);
CREATE INDEX index_group_user_user_id
ON public.group_user USING btree (user_id);
VACUUM ANALYZE;

Afterwards, working speed was bearable again.

Additionally, I created the following indexes in my PostgreSQL 
installation (with much fewer tickets/articles) and am experiencing 
speed improvements, or, better: No laps anymore when it comes to FAQ, 
Attachments and stuff.

CREATE INDEX article_attachment_filename_index
ON public.article_attachment USING btree (filename);
CREATE INDEX article_attachment_content_type_index
ON public.article_attachment USING btree (content_type);
CREATE INDEX article_attachment_content_size_index
ON public.article_attachment USING btree (content_size);
CREATE INDEX article_plain_article_id_index
ON public.article_plain USING btree (article_id);
CREATE INDEX generic_agent_jobs_job_name_index
ON public.generic_agent_jobs USING btree (job_name);
CREATE INDEX group_customer_user_user_id_index
ON public.group_customer_user USING btree (user_id);
CREATE INDEX group_customer_user_group_id_index
ON public.group_customer_user USING btree (group_id);
CREATE INDEX group_role_role_id_index
ON public.group_role USING btree (role_id);
CREATE INDEX group_user_user_id_index
ON public.group_user USING btree (user_id);
CREATE INDEX group_user_group_id_index
ON public.group_user USING btree (group_id);
CREATE INDEX notifications_notification_type_index
ON public.notifications USING btree (notification_type);
CREATE INDEX personal_queues_user_id_index
ON public.personal_queues USING btree (user_id);
CREATE INDEX personal_queues_queue_id_index
ON public.personal_queues USING btree (queue_id);
CREATE INDEX postmaster_filter_f_name_index
ON public.postmaster_filter USING btree (f_name);
CREATE INDEX queue_group_id_index
ON public.queue USING btree (group_id);
CREATE INDEX queue_system_address_id_index
ON public.queue USING btree (system_address_id);
CREATE INDEX queue_auto_response_queue_id_index
ON public.queue_auto_response USING btree (queue_id);
CREATE INDEX queue_auto_response_auto_response_id_index
ON public.queue_auto_response USING btree (auto_response_id);
CREATE INDEX queue_standard_response_queue_id_index
ON public.queue_standard_response USING btree (queue_id);
CREATE INDEX queue_standard_response_standard_response_id_index
ON public.queue_standard_response USING btree (standard_response_id);
CREATE INDEX role_user_role_id_index
ON public.role_user USING btree (role_id);
CREATE INDEX role_user_user_id_index
ON public.role_user USING btree (user_id);
CREATE INDEX search_profile_login_index
ON public.search_profile USING btree (login);
CREATE INDEX search_profile_name_index
ON public.search_profile USING btree (profile_name);
CREATE INDEX session_session_id_index
ON public.session USING btree (session_id);
CREATE INDEX standard_response_attachment_standard_attachment_id_index
ON public.standard_response_attachment USING btree (standard_attachment_id);
CREATE INDEX standard_response_attachment_standard_response_id_index
ON public.standard_response_attachment USING btree (standard_response_id);
CREATE INDEX system_address_queue_id_index
ON public.system_address USING btree (queue_id);
CREATE INDEX ticket_queue_id_index
ON public.ticket USING btree (queue_id);
CREATE INDEX ticket_group_id_index
ON public.ticket USING btree (group_id);
CREATE INDEX ticket_history_article_id_index
ON public.ticket_history USING btree (article_id);
CREATE INDEX ticket_index_queue_id_index
ON public.ticket_index USING btree (queue_id);
CREATE INDEX ticket_index_group_id_index
ON public.ticket_index USING btree (group_id);
CREATE INDEX ticket_link_ticket_id_master_index
ON public.ticket_link USING btree (ticket_id_master);
CREATE INDEX ticket_link_ticket_id_slave_index
ON public.ticket_link USING btree (ticket_id_slave);
CREATE INDEX ticket_state_for_type_id_index
ON public.ticket_state USING btree (type_id);
CREATE INDEX time_accounting_article_id_index
ON public.time_accounting USING btree (article_id);
VACUUM ANALYZE;

Robert, what do you think of these? It may well be I'm doing too much 
here, but I guess too much indexes won't hurt.

With kind regards,

Robert Kehl