Forum OpenACS Development: Problems with indexes on foreign key columns

I was looking into why deleting threads in bboard was so slow (30 seconds per post when deleting a thread) and discovered that many of the foreign key constraints in OpenACS and dotLRN did not have associated indexes. This can have serious performance implications in terms of query times and also can lead to locking problems (on oracle at least).

Here is an example to make this more concrete:

create table things (
       thing_id integer primary key references acs_objects(object_id),
       thing_blah integer references acs_objects(object_id)
);

When deleting any object from acs_objects you would have to do a table scan of the things table in order to check that no fk constraints would be violated by the delete. If things is a large table, this could be quite slow. To make matters worse, oracle will take a shared lock on acs_objects (meaning no updates inserts or deletes) when doing this check. This will lead to deadlocks and general degredation in performance for the db. I am not certain what the locking situation is on postgres though.

I have written a script to check for these on postgres and generate index statements but need to check through them and make sure they are sensible. There is also a script at http://osi.oracle.com/~tkyte/unindex/index.html to check an oracle tablespace for the same issue.

Given the extent of the problems we have decided to try and get this fixed before we release a 4.6.1 version. It would be helpful if any module owners could look at the missing indexes for their modules and make sure the fix makes sense.

Here are the **260** foreign keys constraints which I found were missing an index. I am going to start working through the important ones but any module owners who would prefer to do it themselves are welcome to take a crack at it as well. Obviously if the parent table is largely or entirely static it is not that important to fix. Things that reference things in the core however should be considered more carefully.

/* fix */ means the automatically generated index name is too long and needs to be shortened (to no more than 30 characters).

create index acs_activity_object_map_object_id_idx ON acs_activity_object_map(object_id); /* fix */
create index acs_attributes_object_type_attribute_name_idx ON acs_attributes(object_type, attribute_name); /* fix */
create index acs_attributes_object_type_table_name_idx ON acs_attributes(object_type, table_name); /* fix */
create index acs_event_party_map_party_id_idx ON acs_event_party_map(party_id); /* fix */
create index acs_mail_bodies_body_from_idx ON acs_mail_bodies(body_from);
create index acs_mail_bodies_body_reply_to_idx ON acs_mail_bodies(body_reply_to); /* fix */
create index acs_mail_bodies_content_item_id_idx ON acs_mail_bodies(content_item_id); /* fix */
create index acs_mail_links_body_id_idx ON acs_mail_links(body_id);
create index acs_mail_multipart_parts_content_item_id_idx ON acs_mail_multipart_parts(content_item_id); /* fix */
create index acs_persons_user_id_idx ON acs_persons(user_id);
create index acs_privilege_hierarchy_index_child_privilege_idx ON acs_privilege_hierarchy_index(child_privilege); /* fix */
create index acs_privilege_hierarchy_index_privilege_idx ON acs_privilege_hierarchy_index(privilege); /* fix */
create index acs_reference_repositories_maintainer_id_idx ON acs_reference_repositories(maintainer_id); /* fix */
create index acs_sc_bindings_contract_id_idx ON acs_sc_bindings(contract_id); /* fix */
create index acs_sc_bindings_impl_id_idx ON acs_sc_bindings(impl_id);
create index acs_sc_impl_aliases_impl_id_idx ON acs_sc_impl_aliases(impl_id); /* fix */
create index acs_sc_msg_type_elements_element_msg_type_id_idx ON acs_sc_msg_type_elements(element_msg_type_id); /* fix */
create index acs_sc_msg_type_elements_msg_type_id_idx ON acs_sc_msg_type_elements(msg_type_id); /* fix */
create index acs_sc_operations_contract_id_idx ON acs_sc_operations(contract_id); /* fix */
create index acs_sc_operations_operation_inputtype_id_idx ON acs_sc_operations(operation_inputtype_id); /* fix */
create index acs_sc_operations_operation_outputtype_id_idx ON acs_sc_operations(operation_outputtype_id); /* fix */
create index ad_monitoring_top_proc_top_id_idx ON ad_monitoring_top_proc(top_id); /* fix */
create index adv_group_map_adv_key_idx ON adv_group_map(adv_key);
create index adv_group_swaps_group_key_idx ON adv_group_swaps(group_key);
create index adv_user_map_adv_key_idx ON adv_user_map(adv_key);
create index affix_acs_persons_map_affix_type_id_idx ON affix_acs_persons_map(affix_type_id); /* fix */
create index apm_package_versions_item_id_idx ON apm_package_versions(item_id); /* fix */
create index attachments_fs_root_folder_map_folder_id_idx ON attachments_fs_root_folder_map(folder_id); /* fix */
create index attachments_item_id_idx ON attachments(item_id);
create index bm_bookmarks_owner_id_idx ON bm_bookmarks(owner_id);
create index bm_bookmarks_url_id_idx ON bm_bookmarks(url_id);
create index bt_bug_actions_actor_idx ON bt_bug_actions(actor);
create index bt_bug_actions_bug_id_idx ON bt_bug_actions(bug_id);
create index bt_bugs_assignee_idx ON bt_bugs(assignee);
create index bt_bugs_component_id_idx ON bt_bugs(component_id);
create index bt_bugs_fixed_in_version_idx ON bt_bugs(fixed_in_version);
create index bt_bugs_fix_for_version_idx ON bt_bugs(fix_for_version);
create index bt_bugs_found_in_version_idx ON bt_bugs(found_in_version);
create index bt_bugs_priority_idx ON bt_bugs(priority);
create index bt_bugs_severity_idx ON bt_bugs(severity);
create index bt_components_maintainer_idx ON bt_components(maintainer);
create index bt_components_project_id_idx ON bt_components(project_id);
create index bt_patch_actions_actor_idx ON bt_patch_actions(actor);
create index bt_patch_actions_patch_id_idx ON bt_patch_actions(patch_id);
create index bt_patch_bug_map_bug_id_idx ON bt_patch_bug_map(bug_id);
create index bt_patches_applied_to_version_idx ON bt_patches(applied_to_version); /* fix */
create index bt_patches_apply_to_version_idx ON bt_patches(apply_to_version); /* fix */
create index bt_patches_component_id_idx ON bt_patches(component_id);
create index bt_patches_generated_from_version_idx ON bt_patches(generated_from_version); /* fix */
create index bt_patches_project_id_idx ON bt_patches(project_id);
create index bt_projects_maintainer_idx ON bt_projects(maintainer);
create index bt_user_prefs_project_id_idx ON bt_user_prefs(project_id);
create index bt_user_prefs_user_version_idx ON bt_user_prefs(user_version);
create index bt_versions_maintainer_idx ON bt_versions(maintainer);
create index bt_versions_project_id_idx ON bt_versions(project_id);
create index bulk_mail_messages_package_id_idx ON bulk_mail_messages(package_id); /* fix */
create index calendars_owner_id_idx ON calendars(owner_id);
create index calendars_package_id_idx ON calendars(package_id);
create index cal_items_on_which_calendar_idx ON cal_items(on_which_calendar); /* fix */
create index cal_items_on_which_calendar_item_type_id_idx ON cal_items(on_which_calendar, item_type_id); /* fix */
create index cal_party_prefs_party_id_idx ON cal_party_prefs(party_id);
create index cal_party_prefs_time_zone_idx ON cal_party_prefs(time_zone);
create index clickthrough_log_package_id_idx ON clickthrough_log(package_id); /* fix */
create index cm_attribute_widget_params_param_id_idx ON cm_attribute_widget_params(param_id); /* fix */
create index cm_attribute_widgets_widget_idx ON cm_attribute_widgets(widget); /* fix */
create index cm_content_type_method_map_content_method_idx ON cm_content_type_method_map(content_method); /* fix */
create index cm_content_type_method_map_content_type_idx ON cm_content_type_method_map(content_type); /* fix */
create index cm_form_widget_params_widget_idx ON cm_form_widget_params(widget); /* fix */
create index country_names_iso_idx ON country_names(iso);
create index country_names_language_code_idx ON country_names(language_code); /* fix */
create index cr_content_mime_type_map_mime_type_idx ON cr_content_mime_type_map(mime_type); /* fix */
create index cr_folders_package_id_idx ON cr_folders(package_id);
create index cr_folder_type_map_content_type_idx ON cr_folder_type_map(content_type); /* fix */
create index cr_item_keyword_map_keyword_id_idx ON cr_item_keyword_map(keyword_id); /* fix */
create index cr_item_rels_related_object_id_idx ON cr_item_rels(related_object_id); /* fix */
create index cr_keywords_parent_id_idx ON cr_keywords(parent_id);
create index cronjobs_user_id_idx ON cronjobs(user_id);
create index cr_revisions_item_id_idx ON cr_revisions(item_id);
create index cr_revisions_lob_idx ON cr_revisions(lob);
create index cr_type_children_child_type_idx ON cr_type_children(child_type); /* fix */
create index cr_type_relations_target_type_idx ON cr_type_relations(target_type); /* fix */
create index cr_wp_presentations_aud_id_idx ON cr_wp_presentations_aud(id);
create index cr_wp_presentations_aud_presentation_id_idx ON cr_wp_presentations_aud(presentation_id); /* fix */
create index cr_wp_presentations_back_id_idx ON cr_wp_presentations_back(id); /* fix */
create index cr_wp_presentations_back_presentation_id_idx ON cr_wp_presentations_back(presentation_id); /* fix */
create index cr_wp_presentations_style_idx ON cr_wp_presentations(style);
create index cr_wp_slides_bullet_items_id_idx ON cr_wp_slides_bullet_items(id); /* fix */
create index cr_wp_slides_bullet_items_slide_id_idx ON cr_wp_slides_bullet_items(slide_id); /* fix */
create index cr_wp_slides_postamble_id_idx ON cr_wp_slides_postamble(id);
create index cr_wp_slides_postamble_slide_id_idx ON cr_wp_slides_postamble(slide_id); /* fix */
create index cr_wp_slides_preamble_id_idx ON cr_wp_slides_preamble(id);
create index cr_wp_slides_preamble_slide_id_idx ON cr_wp_slides_preamble(slide_id); /* fix */
create index cr_wp_slides_style_idx ON cr_wp_slides(style);
create index currency_country_map_codea_idx ON currency_country_map(codea);
create index currency_country_map_country_code_idx ON currency_country_map(country_code); /* fix */
create index currency_names_codea_idx ON currency_names(codea);
create index currency_names_language_code_idx ON currency_names(language_code); /* fix */
create index download_archive_metadata_archive_type_id_idx ON download_archive_metadata(archive_type_id); /* fix */
create index download_archive_metadata_repository_id_idx ON download_archive_metadata(repository_id); /* fix */
create index download_archive_revisions_approved_user_idx ON download_archive_revisions(approved_user); /* fix */
create index download_archives_archive_desc_id_idx ON download_archives(archive_desc_id); /* fix */
create index download_archives_archive_type_id_idx ON download_archives(archive_type_id); /* fix */
create index download_archive_types_repository_id_idx ON download_archive_types(repository_id); /* fix */
create index download_downloads_reason_id_idx ON download_downloads(reason_id); /* fix */
create index download_downloads_revision_id_idx ON download_downloads(revision_id); /* fix */
create index download_downloads_user_id_idx ON download_downloads(user_id);
create index download_metadata_choices_metadata_id_idx ON download_metadata_choices(metadata_id); /* fix */
create index download_reasons_repository_id_idx ON download_reasons(repository_id); /* fix */
create index download_revision_data_choice_id_idx ON download_revision_data(choice_id); /* fix */
create index download_revision_data_metadata_id_idx ON download_revision_data(metadata_id); /* fix */
create index download_revision_data_revision_id_idx ON download_revision_data(revision_id); /* fix */
create index ec_addresses_country_code_idx ON ec_addresses(country_code);
create index ec_addresses_usps_abbrev_idx ON ec_addresses(usps_abbrev);
create index ec_admin_settings_default_template_idx ON ec_admin_settings(default_template); /* fix */
create index ec_admin_settings_last_modifying_user_idx ON ec_admin_settings(last_modifying_user); /* fix */
create index ec_categories_last_modifying_user_idx ON ec_categories(last_modifying_user); /* fix */
create index ec_category_product_map_last_modifying_user_idx ON ec_category_product_map(last_modifying_user); /* fix */
create index ec_creditcards_billing_address_idx ON ec_creditcards(billing_address); /* fix */
create index ec_customer_service_actions_interaction_id_idx ON ec_customer_service_actions(interaction_id); /* fix */
create index ec_customer_service_issues_closed_by_idx ON ec_customer_service_issues(closed_by); /* fix */
create index ec_customer_service_issues_gift_certificate_id_idx ON ec_customer_service_issues(gift_certificate_id); /* fix */
create index ec_customer_service_issues_order_id_idx ON ec_customer_service_issues(order_id); /* fix */
create index ec_customer_serv_interactions_customer_service_rep_idx ON ec_customer_serv_interactions(customer_service_rep); /* fix */
create index ec_custom_product_fields_last_modifying_user_idx ON ec_custom_product_fields(last_modifying_user); /* fix */
create index ec_custom_product_field_values_last_modifying_user_idx ON ec_custom_product_field_values(last_modifying_user); /* fix */
create index ec_email_templates_last_modifying_user_idx ON ec_email_templates(last_modifying_user); /* fix */
create index ec_financial_transactions_refunded_transaction_id_idx ON ec_financial_transactions(refunded_transaction_id); /* fix */
create index ec_financial_transactions_refund_id_idx ON ec_financial_transactions(refund_id); /* fix */
create index ec_financial_transactions_shipment_id_idx ON ec_financial_transactions(shipment_id); /* fix */
create index ec_gift_certificates_issued_by_idx ON ec_gift_certificates(issued_by); /* fix */
create index ec_gift_certificates_last_modifying_user_idx ON ec_gift_certificates(last_modifying_user); /* fix */
create index ec_gift_certificates_purchased_by_idx ON ec_gift_certificates(purchased_by); /* fix */
create index ec_gift_certificates_voided_by_idx ON ec_gift_certificates(voided_by); /* fix */
create index ec_gift_certificate_usage_order_id_idx ON ec_gift_certificate_usage(order_id); /* fix */
create index ec_items_refund_id_idx ON ec_items(refund_id);
create index ec_items_voided_by_idx ON ec_items(voided_by);
create index ec_offers_last_modifying_user_idx ON ec_offers(last_modifying_user); /* fix */
create index ec_offers_product_id_idx ON ec_offers(product_id);
create index ec_offers_retailer_location_id_idx ON ec_offers(retailer_location_id); /* fix */
create index ec_orders_voided_by_idx ON ec_orders(voided_by);
create index ec_picklist_items_last_modifying_user_idx ON ec_picklist_items(last_modifying_user); /* fix */
create index ec_problems_log_gift_certificate_id_idx ON ec_problems_log(gift_certificate_id); /* fix */
create index ec_problems_log_order_id_idx ON ec_problems_log(order_id);
create index ec_problems_log_resolved_by_idx ON ec_problems_log(resolved_by); /* fix */
create index ec_product_comments_last_modifying_user_idx ON ec_product_comments(last_modifying_user); /* fix */
create index ec_product_links_last_modifying_user_idx ON ec_product_links(last_modifying_user); /* fix */
create index ec_product_recommendations_last_modifying_user_idx ON ec_product_recommendations(last_modifying_user); /* fix */
create index ec_product_recommendations_product_id_idx ON ec_product_recommendations(product_id); /* fix */
create index ec_product_reviews_last_modifying_user_idx ON ec_product_reviews(last_modifying_user); /* fix */
create index ec_product_series_map_last_modifying_user_idx ON ec_product_series_map(last_modifying_user); /* fix */
create index ec_products_last_modifying_user_idx ON ec_products(last_modifying_user); /* fix */
create index ec_products_template_id_idx ON ec_products(template_id);
create index ec_product_user_class_prices_last_modifying_user_idx ON ec_product_user_class_prices(last_modifying_user); /* fix */
create index ec_refunds_refunded_by_idx ON ec_refunds(refunded_by);
create index ec_retailer_locations_country_code_idx ON ec_retailer_locations(country_code); /* fix */
create index ec_retailer_locations_last_modifying_user_idx ON ec_retailer_locations(last_modifying_user); /* fix */
create index ec_retailer_locations_retailer_id_idx ON ec_retailer_locations(retailer_id); /* fix */
create index ec_retailer_locations_usps_abbrev_idx ON ec_retailer_locations(usps_abbrev); /* fix */
create index ec_retailers_country_code_idx ON ec_retailers(country_code);
create index ec_retailers_last_modifying_user_idx ON ec_retailers(last_modifying_user); /* fix */
create index ec_retailers_usps_abbrev_idx ON ec_retailers(usps_abbrev);
create index ec_sale_prices_last_modifying_user_idx ON ec_sale_prices(last_modifying_user); /* fix */
create index ec_sales_tax_by_state_last_modifying_user_idx ON ec_sales_tax_by_state(last_modifying_user); /* fix */
create index ec_shipments_address_id_idx ON ec_shipments(address_id);
create index ec_subcategories_last_modifying_user_idx ON ec_subcategories(last_modifying_user); /* fix */
create index ec_subcategory_product_map_last_modifying_user_idx ON ec_subcategory_product_map(last_modifying_user); /* fix */
create index ec_subsubcategories_last_modifying_user_idx ON ec_subsubcategories(last_modifying_user); /* fix */
create index ec_subsubcategory_product_map_last_modifying_user_idx ON ec_subsubcategory_product_map(last_modifying_user); /* fix */
create index ec_templates_last_modifying_user_idx ON ec_templates(last_modifying_user); /* fix */
create index ec_user_classes_last_modifying_user_idx ON ec_user_classes(last_modifying_user); /* fix */
create index ec_user_class_user_map_last_modifying_user_idx ON ec_user_class_user_map(last_modifying_user); /* fix */
create index faq_q_and_as_faq_id_idx ON faq_q_and_as(faq_id);
create index forums_messages_parent_id_idx ON forums_messages(parent_id);
create index forums_messages_user_id_idx ON forums_messages(user_id);
create index fs_simple_objects_folder_id_idx ON fs_simple_objects(folder_id); /* fix */
create index glossaries_owner_id_idx ON glossaries(owner_id);
create index glossaries_package_id_idx ON glossaries(package_id);
create index glossaries_workflow_key_idx ON glossaries(workflow_key);
create index host_node_map_node_id_idx ON host_node_map(node_id);
create index lang_translation_registry_locale_idx ON lang_translation_registry(locale); /* fix */
create index mp3_mp3_playlist_map_playlist_id_idx ON mp3_mp3_playlist_map(playlist_id); /* fix */
create index mp3_mp3_stats_mp3_id_idx ON mp3_mp3_stats(mp3_id);
create index mp3_mp3_stats_user_id_idx ON mp3_mp3_stats(user_id);
create index mp3_playlist_stats_playlist_id_idx ON mp3_playlist_stats(playlist_id); /* fix */
create index mp3_playlist_stats_user_id_idx ON mp3_playlist_stats(user_id);
create index mp3_votes_mp3_id_idx ON mp3_votes(mp3_id);
create index mp3_votes_playlist_id_idx ON mp3_votes(playlist_id);
create index mp3_votes_user_id_idx ON mp3_votes(user_id);
create index notes_owner_id_idx ON notes(owner_id);
create index notification_delivery_methods_sc_impl_id_idx ON notification_delivery_methods(sc_impl_id); /* fix */
create index notification_replies_from_user_idx ON notification_replies(from_user); /* fix */
create index notification_replies_object_id_idx ON notification_replies(object_id); /* fix */
create index notification_replies_type_id_idx ON notification_replies(type_id); /* fix */
create index notification_requests_object_id_idx ON notification_requests(object_id); /* fix */
create index notification_requests_type_id_delivery_method_id_idx ON notification_requests(type_id, delivery_method_id); /* fix */
create index notification_requests_type_id_interval_id_idx ON notification_requests(type_id, interval_id); /* fix */
create index notification_requests_user_id_idx ON notification_requests(user_id); /* fix */
create index notifications_object_id_idx ON notifications(object_id);
create index notifications_response_id_idx ON notifications(response_id);
create index notifications_type_id_idx ON notifications(type_id);
create index notification_types_del_methods_delivery_method_id_idx ON notification_types_del_methods(delivery_method_id); /* fix */
create index notification_types_intervals_interval_id_idx ON notification_types_intervals(interval_id); /* fix */
create index notification_types_sc_impl_id_idx ON notification_types(sc_impl_id); /* fix */
create index notification_user_map_user_id_idx ON notification_user_map(user_id); /* fix */
create index postcard_images_lob_idx ON postcard_images(lob);
create index postcards_card_picture_idx ON postcards(card_picture);
create index rss_gen_subscrs_summary_context_id_idx ON rss_gen_subscrs(summary_context_id); /* fix */
create index site_node_object_mappings_node_id_idx ON site_node_object_mappings(node_id); /* fix */
create index spam_messages_spam_id_idx ON spam_messages(spam_id);
create index sp_extant_files_static_page_id_idx ON sp_extant_files(static_page_id); /* fix */
create index sp_extant_folders_folder_id_idx ON sp_extant_folders(folder_id); /* fix */
create index survey_question_responses_question_id_idx ON survey_question_responses(question_id); /* fix */
create index survey_questions_section_id_idx ON survey_questions(section_id); /* fix */
create index survey_responses_initial_response_id_idx ON survey_responses(initial_response_id); /* fix */
create index survey_responses_survey_id_idx ON survey_responses(survey_id);
create index surveys_package_id_idx ON surveys(package_id);
create index survsimp_choice_scores_choice_id_idx ON survsimp_choice_scores(choice_id); /* fix */
create index survsimp_choice_scores_variable_id_idx ON survsimp_choice_scores(variable_id); /* fix */
create index survsimp_logic_surveys_map_logic_id_idx ON survsimp_logic_surveys_map(logic_id); /* fix */
create index survsimp_logic_surveys_map_survey_id_idx ON survsimp_logic_surveys_map(survey_id); /* fix */
create index survsimp_question_choices_question_id_idx ON survsimp_question_choices(question_id); /* fix */
create index survsimp_question_responses_choice_id_idx ON survsimp_question_responses(choice_id); /* fix */
create index survsimp_question_responses_item_id_idx ON survsimp_question_responses(item_id); /* fix */
create index survsimp_question_responses_question_id_idx ON survsimp_question_responses(question_id); /* fix */
create index survsimp_questions_survey_id_idx ON survsimp_questions(survey_id); /* fix */
create index survsimp_responses_survey_id_idx ON survsimp_responses(survey_id); /* fix */
create index survsimp_surveys_package_id_idx ON survsimp_surveys(package_id); /* fix */
create index survsimp_variables_surveys_map_survey_id_idx ON survsimp_variables_surveys_map(survey_id); /* fix */
create index survsimp_variables_surveys_map_variable_id_idx ON survsimp_variables_surveys_map(variable_id); /* fix */
create index ttl_area_assignments_user_id_idx ON ttl_area_assignments(user_id); /* fix */
create index ttl_assignments_user_id_idx ON ttl_assignments(user_id);
create index ttl_comments_creation_user_idx ON ttl_comments(creation_user);
create index ttl_comments_ticket_id_idx ON ttl_comments(ticket_id);
create index ttl_prefs_area_id_idx ON ttl_prefs(area_id);
create index ttl_tickets_area_id_idx ON ttl_tickets(area_id);
create index ttl_tickets_status_idx ON ttl_tickets(status);
create index us_counties_fips_state_code_idx ON us_counties(fips_state_code); /* fix */
create index us_counties_state_abbrev_idx ON us_counties(state_abbrev);
create index us_zipcodes_fips_state_code_idx ON us_zipcodes(fips_state_code); /* fix */
create index vbs_rates_country_iso_idx ON vbs_rates(country_iso);
create index wf_attribute_value_audit_journal_id_idx ON wf_attribute_value_audit(journal_id); /* fix */
create index wf_case_assignments_workflow_key_role_key_idx ON wf_case_assignments(workflow_key, role_key); /* fix */
create index wf_case_deadlines_workflow_key_transition_key_idx ON wf_case_deadlines(workflow_key, transition_key); /* fix */
create index wf_context_assignments_party_id_idx ON wf_context_assignments(party_id); /* fix */
create index wf_context_role_info_workflow_key_idx ON wf_context_role_info(workflow_key); /* fix */
create index wf_context_role_info_workflow_key_role_key_idx ON wf_context_role_info(workflow_key, role_key); /* fix */
create index wf_context_workflow_info_principal_party_idx ON wf_context_workflow_info(principal_party); /* fix */
create index wf_context_workflow_info_workflow_key_idx ON wf_context_workflow_info(workflow_key); /* fix */
create index wf_tasks_workflow_key_idx ON wf_tasks(workflow_key);
create index wf_tasks_workflow_key_transition_key_idx ON wf_tasks(workflow_key, transition_key); /* fix */
create index wf_tokens_canceled_journal_id_idx ON wf_tokens(canceled_journal_id); /* fix */
create index wf_tokens_consumed_journal_id_idx ON wf_tokens(consumed_journal_id); /* fix */
create index wf_tokens_locked_journal_id_idx ON wf_tokens(locked_journal_id); /* fix */
create index wf_tokens_locked_task_id_idx ON wf_tokens(locked_task_id);
create index wf_tokens_produced_journal_id_idx ON wf_tokens(produced_journal_id); /* fix */
create index wf_tokens_workflow_key_idx ON wf_tokens(workflow_key);
create index wf_tokens_workflow_key_place_key_idx ON wf_tokens(workflow_key, place_key); /* fix */
create index wf_transition_attribute_map_attribute_id_idx ON wf_transition_attribute_map(attribute_id); /* fix */
create index wf_transitions_workflow_key_role_key_idx ON wf_transitions(workflow_key, role_key); /* fix */

Jon, it creates triggers automatically but not indexes (except for unique constraints or primary keys). The performance problems I found were on postgres (and its a well known problem on oracle).

anyway here is an example...

oatest4=# create table a ( i integer primary key, j integer not null);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey' for table 'a'
CREATE
oatest4=# \d a
          Table "a"
 Column |  Type   | Modifiers 
--------+---------+-----------
 i      | integer | not null
 j      | integer | not null
Primary key: a_pkey

oatest4=# create table b(k integer references a(i));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
oatest4=# \d b
          Table "b"
 Column |  Type   | Modifiers 
--------+---------+-----------
 k      | integer | 
Triggers: RI_ConstraintTrigger_7737891

oatest4=# create table c(k  integer unique references a(i));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'c_k_key' for table 'c'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
oatest4=# \d c
          Table "c"
 Column |  Type   | Modifiers 
--------+---------+-----------
 k      | integer | 
Unique keys: c_k_key
Triggers: RI_ConstraintTrigger_7737906

Hi, can u please send me the script to create the indexes?

thnaks in advance
cheers