Project

General

Profile

Revision 7455

additional db indexes for pathquery performance
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5696

View differences:

xmltables-postgres.sql
170 170
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
171 171
CREATE INDEX xml_documents_idx2 ON xml_documents (lower(user_owner));
172 172
CREATE INDEX xml_documents_idx3 ON xml_documents (rootnodeid);
173
CREATE INDEX xml_documents_idx5 ON xml_documents (docid, rev);
173 174

  
174 175
/*
175 176
 * Revised Documents -- table to store XML documents saved after an UPDATE
......
225 226
CREATE INDEX xml_access_idx3 ON xml_access (perm_type);
226 227
CREATE INDEX xml_access_idx4 ON xml_access (perm_order);
227 228
CREATE INDEX xml_access_idx5 ON xml_access (subtreeid);
229
CREATE INDEX xml_access_idx6 on xml_access(guid);
228 230
/*
229 231
 * ALTER TABLE xml_access ADD COLUMN guid text;
230 232
*/
......
283 285
   rev    INT8,          -- the revision part of the local identifier
284 286
   CONSTRAINT identifier_pk PRIMARY KEY (guid)
285 287
);
288
CREATE INDEX identifier_guid on identifier(guid);
289
CREATE INDEX identifier_docid on identifier(docid);
290
CREATE INDEX identifier_rev on identifier(rev);
291
CREATE INDEX identifier_docid_rev on identifier(docid, rev);
286 292

  
287 293
/*
288 294
 * Table used to store all document identifiers for system metadata objects
......
425 431
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
426 432
CREATE INDEX xml_path_index_idx4 ON xml_path_index (upper(nodedata));
427 433
CREATE INDEX xml_path_index_idx5 ON xml_path_index (nodedatadate);
434
CREATE INDEX xml_path_index_idx6 ON xml_path_index (docid);
428 435

  
429 436
/*
430 437
 * harvest_site_schedule -- table to store harvest sites and schedule info

Also available in: Unified diff