/*
*
* CDDL HEADER START
*
* The contents of this file are subject to the terms of the
* Common Development and Distribution License, Version 1.0 only
* (the "License"). You may not use this file except in compliance
* with the License.
*
* You can obtain a copy of the license at license/ESCIDOC.LICENSE
* or http://www.escidoc.de/license.
* See the License for the specific language governing permissions
* and limitations under the License.
*
* When distributing Covered Code, include this CDDL HEADER in each
* file and include the License file at license/ESCIDOC.LICENSE.
* If applicable, add the following below this CDDL HEADER, with the
* fields enclosed by brackets "[]" replaced with your own identifying
* information: Portions Copyright [yyyy] [name of copyright owner]
*
* CDDL HEADER END
*/

/*
* Copyright 2006-2010 Fachinformationszentrum Karlsruhe Gesellschaft
* für wissenschaftlich-technische Information mbH and Max-Planck-
* Gesellschaft zur Förderung der Wissenschaft e.V.
* All rights reserved. Use is subject to license terms.
*/

/**
 * Script for creation of indexes and performance optimization of the filter cache
 *
 * @author Natasa Bulatovic
 * @created: 27.04.2009
 *NOTE: run this script after create_trigger.sql script

**/

--CHECK FIRST IF THERE IS TABLESPACE tbl_index created. If not created, use the command below (note: location depends on the database server configuration)
--CREATE TABLESPACE tbl_index OWNER postgres LOCATION '/var/lib/pgsql/data/tablespaces';


CREATE INDEX id_local_path_position
  ON list.property
  USING btree
  (resource_id, local_path, "position")
TABLESPACE tbl_index;

-- Index: list.id_property_context_index

-- DROP INDEX list.id_property_context_index;

CREATE INDEX id_property_context_index
  ON list.property
  USING btree
  (resource_id, local_path, value)
TABLESPACE tbl_index
  WHERE local_path = '/properties/context/id'::text;

-- Index: list.id_property_path_value

-- DROP INDEX list.id_property_path_value;

CREATE INDEX id_property_path_value
  ON list.property
  USING btree
  (resource_id, local_path, value)
TABLESPACE tbl_index;

-- Index: list.path_value_position

-- DROP INDEX list.path_value_position;

CREATE INDEX path_value_position
  ON list.property
  USING btree
  (local_path, value, "position")
TABLESPACE tbl_index;

-- Index: list.property_path_value_id

-- DROP INDEX list.property_path_value_id;

CREATE INDEX property_path_value_id
  ON list.property
  USING btree
  (local_path, value, resource_id)
TABLESPACE tbl_index;