Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Create indexes on metadata.updatedDate for instances, holdings, and items

Description

Can you please create descending indexes on metadata.updatedDate for instances, holdings, and items. I want to be able to query those tables using the web API and get back the most recently updated objects.

I noticed that holdings and items already have an index on that field.

CREATE INDEX holdings_record_pmh_metadata_updateddate_idx ON diku_mod_inventory_storage.holdings_record USING btree (strtotimestamp((jsonb -> 'metadata'::text) ->> 'updatedDate'::text) timestamptz_ops); CREATE INDEX item_pmh_metadata_updateddate_idx ON diku_mod_inventory_storage.item USING btree (strtotimestamp((jsonb -> 'metadata'::text) ->> 'updatedDate'::text) timestamptz_ops);

However, it doesn't appear to be picked up by a web API query such as the following which took over a minute to complete on our server which has around 10 million items loaded.

http://myserver:9130/item-storage/items?query=cql.allrecords=1 sortby metadata.updatedDate&offset=0&limit=100

I think this is because RMB treats date/time values as text.

Environment

None

Potential Workaround

None

Checklist

hide

TestRail: Results

Activity

Show:

Jon MillerFebruary 29, 2024 at 1:57 PM

This is one of the most important fields that I always use in all my web applications. It allows for a default grid view of objects with the most recently added items at the top. If this is not something you can accommodate, then, I’m going not bother filing anymore JIRAs. I basically gave up on them a long time ago because the virtually none of what I submitted was ever acted upon. A lot of them are never even assigned to anyone and those that are just get passed on to future releases indefinitely. They were all shot down, just like this. I have come to the conclusion that the web APIs are not general purpose APIs for use with different applications. They are built only to accommodate the FOLIO web application itself and nothing more.

Pavlo SmahinFebruary 29, 2024 at 6:58 AM

Hey .

There are a lot of requests I saw for creating indexes for different fields in mod-inventory-storage, but we cannot create it for each field because it will affect a performance of records saving. So I think we have to think twice before adding any new indexes. RMB-742 is related to other fields.

As for mod-search it could help only with retrieving instances. It will not help with getting items or holdings sorted by any field because in response we will get instances with all their holdings and items.

As for priority I’m not sure. I just can say that I don’t know any flow in the FOLIO that requires performant sorting by updated date.

Khalilah GambrellFebruary 28, 2024 at 11:21 PM

Hey and - do we need the need these indexes for mod-inventory-storage. If so

1- What is the feature and/or work that we can link this issue too?

2-Should https://folio-org.atlassian.net/browse/RMB-742 be done too?

3-Is the work needed since we have mod-search?

4-What is the priority of this work?

Jon MillerJuly 29, 2021 at 5:54 PM

The index should be in descending order. That's why I titled the JIRA that way. Performance-wise, it may not make that big a difference, but, in most cases, you will be sorting in descending order because you want to see the most recently edited objects at the top of the list.

Jon MillerOctober 22, 2020 at 8:48 PM
Edited

RMB is doing the following. So, the indexes need to match the left(lower(f_unaccent())) for it to work. Or, RMB needs to be changed to handle date/times as date/times, not as text.

22 Oct 2020 20:44:44:285 INFO CQLWrapper [] CQL >>> SQL: cql.allrecords=1 sortby metadata.updatedDate >>>WHERE true ORDER BY left(lower(f_unaccent(item.jsonb->'metadata'->>'updatedDate')),600), lower(f_unaccent(item.jsonb->'metadata'->>'updatedDate')) LIMIT 100 OFFSET 0

Details

Assignee

Reporter

Labels

Priority

Development Team

Spitfire

Affected Institution

Cornell
MO State

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs

Created October 22, 2020 at 8:40 PM
Updated March 24, 2025 at 1:53 PM
TestRail: Cases
TestRail: Runs

Flag notifications