Slow query when hydra_oauth2_consent_request growth

[purmac]

Hydra Version: v1.0.0+oryOS.12
Database: CockroachDB 19.2

Hi Team,

We've observed the slow consent sql query similar to the issue #1454
When running

SELECT h.*
FROM hydra_oauth2_consent_request_handled AS h
         JOIN hydra_oauth2_consent_request AS r ON (h.challenge = r.challenge)
WHERE (((r.subject = $1 ) AND (r.client_id = $2)) AND (r.skip = true))
  AND ((h.error = '{}') AND (h.remember = true))
ORDER BY h.requested_at DESC limit 1;

The query time growth linearly with the number of records for a subject in hydra_oauth2_consent_request

For a subject that has 1040 records in hydra_oauth2_consent_request. It take 14ms to complete the query
For a subject that has 305683 records in hydra_oauth2_consent_request. It takes 23 sec to complete.

Purging the hydra_oauth2_consent_request table might mitigate the issue. However, but there is no API or shell scripts to do the cleanup. As discussed in #1574

I'm not sure Ory team is aware of this behavior. There might be a SQL optimization can be done in for this problem. Thanks.

[aeneasr]

Version 1.0 is over a year old, we are now at 1.6 and the addressed issue has long been reported and resolved :) Consider signing up to the newsletter and stay informed about updates: https://ory.us10.list-manage.com/subscribe?u=ffb1a878e4ec6c0ed312a3480&id=f605a41b53&MERGE0=

[purmac]

Thanks @aeneasr