search

Apicurio / apicurio-registry

An API/Schema registry - stores APIs and Schemas.
https://www.apicur.io/registry/
Apache License 2.0
609 stars 269 forks source link

Search API count query takes a lot of db time #4723

Open wolfchimneyrock opened 6 months ago

wolfchimneyrock commented 6 months ago

Description

Registry Version: 2.5.7 Persistence type: sql - postgres 13

We have noticed during review of db query metrics that one particular query which accounts for about 10% of all queries is taking over 95% of the database's time - the count query in searchArtifacts()

https://github.com/Apicurio/apicurio-registry/blob/1b63b543fdff174c1f1e9c20c4840c2c957c77e4/app/src/main/java/io/apicurio/registry/storage/impl/sql/AbstractSqlRegistryStorage.java#L1397

Screenshot 2024-05-31 at 9 04 39 AM Screenshot 2024-05-31 at 9 05 01 AM

We are not even using the outcome of this "count" query, only the results themselves. If the query can't be optimized, maybe a parameter can be added to the search api to skip running this count query?

Environment

openjdk17 - rhel8

Steps to Reproduce

Expected vs Actual Behaviour

Logs

EricWittmann commented 3 weeks ago

In Registry 3.0 we have made some significant changes to the REST API and internal DB organization that I think may make this no longer relevant.

@wolfchimneyrock would it be possible for you to give 3.0 a try and report back?