Investigation: mod-orders/mod-orders-storage performance
1. Performance testing methods (general information)
Performance testing methods for mod-orders-storage should cover common operations: search (by one/several* fields), filtering (by one/several* fields), filtering & sorting (by one/several* fields). Corresponding endpoints for GET requests for covering common operations are presented in the Table 1.1. JMeter script for measurement performance markers can be founded here . Default script configuration designed to perform 1K requests in one thread for each endpoint from Table 1.1. This configuration can be simple improved/changed in future to obtain more precise performance markers.
Table 1.1 - Endpoints for reference GET requests
Search | |
/orders-storage/purchase-orders?query=poNumber==${poNumber} | Searching purchase order/po-line records by unique value for both direct endpoints and po+po-line view. One need manually set poNumber/poLineNumber value whenever possible in the middle of the range of corresponding values. |
/orders-storage/orders?query=poNumber==${poNumber} | |
/orders-storage/po-lines?query=poLineNumber==${poLineNumber} | |
/orders-storage/order-lines?query=poLineNumber==${poLineNumber} | |
Filtering | |
/orders-storage/purchase-orders?query=workflowStatus==Open | Filtering purchase order/po-line records by multiple same value for both direct endpoints and po+po-line view. |
/orders-storage/orders?query=workflowStatus==Open | |
/orders-storage/po-lines?query=orderFormat==Physical Resource | |
/orders-storage/order-lines?query=orderFormat==Physical Resource | |
Sorting | |
/orders-storage/orders?query=workflowStatus==Open AND orderFormat=Physical Resource sortBy metadata.createdDate/sort.ascending | Filtering and sorting purchase order/po-line records by multiple same value for po+po-line view. |
/orders-storage/order-lines?query=workflowStatus==Open AND orderFormat=Physical Resource sortBy metadata.createdDate/sort.ascending | |
* At the present moment searching and filtering performance testing implemented only for one field. It is interesting to add scenarios for several fields searching/filtering.
2. Indexes
2.1 General information
Current RMB implementation contains some PostgreSQL indexes. Correspondence between RMB indexes and standard PostgreSQL indexes is presented in the Table 2.1.
Table 2.1 - Correspondence between RMB indexes and standard PostgreSQL indexes
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} | 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} | Due to | ||
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 | This index can be used for used for full text searching, for example, comments, notes, etc. by using function | ||
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 |
|
|
2.2 Purchase order, purchase order line, piece indexes
In scope of https://folio-org.atlassian.net/browse/MODORDSTOR-85 the following indexes were introduced for PO, PO Line, Piece (Table 2.2)
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, | |
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:
SELECT
tablename,
indexname,
indexdef
FROM
pg_indexes
WHERE
schemaname = 'diku_mod_orders_storage'
ORDER BY
tablename,
indexname;