| Table of Contents |
|---|
...
| PostgreSQL index | Description | RMB index type | RMB-generated snippet | Folio schemas application area |
|---|---|---|---|---|
| B-tree | B-trees fit the most common situations. B-trees can handle equality and range queries on data that can be sorted into some ordering. | index | CREATE INDEX IF NOT EXISTS ${table.tableName}_${indexes.fieldName}_idx ON ${myuniversity}_${mymodule}.${table.tableName} | B-trees can handle equality and range queries on data that can be sorted into some ordering. This is most common case. |
| uniqueIndex | CREATE UNIQUE INDEX IF NOT EXISTS ${table.tableName}_${indexes.fieldName}_idx_unique ON ${myuniversity}_${mymodule}.${table.tableName} ( ${indexes.fieldPath} ) | Unique indexes. This should be used for unique fields, for example, poNumber. *Currently, only B-tree indexes can be declared unique https://www.postgresql.org/docs/11/indexes-unique.html. | ||
| likeIndex | CREATE INDEX IF NOT EXISTS ${table.tableName}_${indexes.fieldName}_idx_like ON ${myuniversity}_${mymodule}.${table.tableName} ((${indexes.fieldPath}) text_pattern_ops) | Due to text_pattern_ops the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions. | ||
| Hash | Hash indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the = operator. | Not supported | ||
| GiST | GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. The standard distribution of PostgreSQL includes GiST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators: <<, &<, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=, &&. GiST indexes are also capable of optimizing “nearest-neighbor” searches. | Not supported | ||
| SP-GiST | SP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches. SP-GiST permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (tries). As an example, the standard distribution of PostgreSQL includes SP-GiST operator classes for two-dimensional points, which support indexed queries using these operators: <<, >>, ~=, <@, <^, >^. | Not supported | ||
| GIN | GIN indexes are “inverted indexes” which are appropriate for data values that contain multiple component values, such as arrays. An inverted index contains a separate entry for each component value, and can efficiently handle queries that test for the presence of specific component values. | ginIndex | CREATE INDEX IF NOT EXISTS ${table}_${field}_idx_gin ON ${tenant}_${module}.${table} USING GIN ((${indexes.fieldPath}) gin_trgm_ops) | The gin_trgm_ops module provides GIN index operator classes that allows to create an index over a text column for the purpose of very fast similarity searches. These indexes do not support equality nor simple comparison operators. |
| fullTextIndex | CREATE INDEX IF NOT EXISTS ${table}_${indexes.fieldName}_idx_ft ON ${myuniversity}_${mymodule}.${table.tableName} USING GIN ( to_tsvector('${ft_defaultDictionary}', ${indexes.fieldPath}) ); | This index can be used for used for full text searching, for example, comments, notes, etc. by using function to_tsvector that converts a document to the tsvector data type. | ||
| BRIN | BRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table. | Not supported |
...
In scope of
| Jira Legacy | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Table 2.2 - Correspondence between RMB indexes and PO, PO Line, Piece fields
| RMB index | Field | |
|---|---|---|
| index | PO | assignedTo, metadata.createdDate, metadata.createdByUserId, workflowStatus, approved, dateOrdered, orderType, reEncumber, renewal.manualRenewal, renewal.renewalDate, renewal.reviewPeriod |
| PO Line | eresource.accessProvider, eresource.activated, eresource.expectedActivation, eresource.trial, eresource.materialType, details.subscriptionFrom, details.subscriptionTo, location.locationId, physical.receiptDue, physical.expectedReceiptDate, physical.volumes, physical.materialType, claim.grace, claim.sent, collection, receiptStatus, paymentStatus, orderFormat, receiptDate, metadata.createdDate, rush, source | |
| Piece | format, itemId, locationId, poLineId, receivingStatus, receivedDate, supplement | |
| uniqueIndex | PO | poNumber |
| PO Line | - | |
| Piece | - | |
| ginIndex | PO | - |
| PO Line | fundDistribution.code, details.productIds, title, vendor.vendorAccount, vendor.refNumber, tags, contributors, poLineNumber, requester | |
| Piece | caption | |
| fullTextIndex | PO | closeReason.reason, closeReason.note |
| PO Line | publisher, donor, selector | |
| Piece | comment, caption | |
Indexes can be verified by using query:
...
Virtual users arrive in a certain amount at equal time intervals and cyclically perform the sequence of operations C-U-R-D, C-U-R-D, C-U-R-D. This load mode corresponds to an open system. Open system have no control over the number concurrent users: users keep on arriving even though applications has some trouble serving them. This This load mode allows to study the dependence of the response time of the system depending on the number of clients/number of active requests in the conditions of a real service workload and peak service throughtput for which service fails.
Constant load for a fixed time / number of iterations.
Such a load scenario implies the cyclic execution of sequential operations C-U-R-D, C-U-R-D , C-U-R-D, ... for virtual users, the number of which is constant during the test. This load scenario is used for the main purpose - measuring the throughput of services in the case of one (or several) request flows. Additionally, it can be used to check the stability of the system over time when the service is used continuously for a long time.
...
4. Performance-related stories list
| Jira Legacy | ||||||||
|---|---|---|---|---|---|---|---|---|
|