Details
-
Task
-
Status: Closed (View Workflow)
-
P3
-
Resolution: Done
-
None
-
-
Core: Platform - Sprint 60
-
5
-
Core: Platform
Description
When query instance by location, following query is generated but the performance is very poor. Query explain analyze shows about 50 seconds. Find a way to improve existing query, or come up a different and better query, or propose other solution like re-organize how instance/holdings data are stored. BTW, the query is generated when users click the Inventory app and select a location filter. Note, I adjusted the ~ to LIKE for the location condition, otherwise, it takes about 8 minutes.
Note: No implementation in the scope of this ticket.
WITH headrecords AS
(
SELECT
jsonb,
lower(f_unaccent(jsonb ->> 'title')) AS title
FROM
supertenant_mod_inventory_storage.instance_holding_view
WHERE
(
(((lower(f_unaccent(instance_holding_view.jsonb ->> 'title')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]])).*($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))')))
OR
(
lower(f_unaccent(instance_holding_view.jsonb ->> 'contributors')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))"name":([[:punct:]]|[[:space:]])+".*"($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))'))
)
)
OR
(
lower(f_unaccent(instance_holding_view.jsonb ->> 'identifiers')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))"value":([[:punct:]]|[[:space:]])+".*"($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))'))
)
)
AND
(
lower(f_unaccent(instance_holding_view.ho_jsonb ->> 'permanentLocationId')) LIKE lower(f_unaccent('53cf956f-c1df-410b-8bea-27f712cca7c0'))
)
)
AND lower(f_unaccent(jsonb ->> 'title')) < (
SELECT
lower(f_unaccent(jsonb ->> 'title'))
FROM
supertenant_mod_inventory_storage.instance_holding_view
ORDER BY
lower(f_unaccent(jsonb ->> 'title')) OFFSET 10000 LIMIT 1 )
ORDER BY
lower(f_unaccent(jsonb ->> 'title')) LIMIT 30 OFFSET 0
)
,
allrecords AS
(
SELECT
jsonb,
lower(f_unaccent(jsonb ->> 'title')) AS title
FROM
supertenant_mod_inventory_storage.instance_holding_view
WHERE
(
(((lower(f_unaccent(instance_holding_view.jsonb ->> 'title')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]])).*($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))')))
OR
(
lower(f_unaccent(instance_holding_view.jsonb ->> 'contributors')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))"name":([[:punct:]]|[[:space:]])+".*"($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))'))
)
)
OR
(
lower(f_unaccent(instance_holding_view.jsonb ->> 'identifiers')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))"value":([[:punct:]]|[[:space:]])+".*"($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))'))
)
)
AND
(
lower(f_unaccent(instance_holding_view.ho_jsonb ->> 'permanentLocationId')) LIKE lower(f_unaccent('53cf956f-c1df-410b-8bea-27f712cca7c0'))
)
)
AND
(
SELECT
COUNT(*)
FROM
headrecords
)
< 30
)
SELECT
jsonb,
title,
0 AS count
FROM
headrecords
WHERE
(
SELECT
COUNT(*)
FROM
headrecords
)
>= 30
UNION
(
SELECT
jsonb, title,
(
SELECT
COUNT(*)
FROM
allrecords
)
AS count
FROM
allrecords
ORDER BY
title LIMIT 30 OFFSET 0 )
ORDER BY
title;
TestRail: Results
Attachments
Issue Links
- blocks
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-