query slow issue

[baegjae]

Hi.
We applied aca-py and askar on our service.
I found performance issues when the issuer issues credential.

Environment

• aca-py 0.7.5 (multitenant, wallet_type: askar-profile, wallet_storage_type: postgres_storage)
• askar 0.2.8
• postgres 13.3

Problem

• When issuer searches revocation registries with cred_def_id and state to issue credential, it takes 15 seconds.
• the number of revocation registry that matches cred_def_id was 7000.
• the number of revocation registry that matches cred_def_id and state was 3.

Problem replay

• I launched a docker postgres 13.3 and aca-py 0.7.5 (askar 0.2.8).
• I excuted a script that issues credentials for making many revocation registries.
• I called /revocation/registries/created with cred_def_id and state (active).
• 1 out of 10 times I experienced slow latency.
• I've tested several versions of postgres, but it doesn't seem to be a problem specific to postgres 13.3.

Slow query log

2023-04-12 01:50:00.221 UTC [37] LOG:  execute sqlx_s_4: SELECT id, name, value,
	    (SELECT ARRAY_TO_STRING(ARRAY_AGG(it.plaintext || ':'
	        || ENCODE(it.name, 'hex') || ':' || ENCODE(it.value, 'hex')), ',')
	        FROM items_tags it WHERE it.item_id = i.id) tags
	    FROM items i WHERE profile_id = $1 AND kind = $2 AND category = $3
	    AND (expiry IS NULL OR expiry > CURRENT_TIMESTAMP) AND (i.id IN (SELECT item_id FROM items_tags WHERE name = $4 AND value = $5 AND SUBSTR(value, 1, 12) = $6 AND plaintext = 0) AND i.id IN (SELECT item_id FROM items_tags WHERE name = $7 AND value = $8 AND SUBSTR(value, 1, 12) = $9 AND plaintext = 0))
2023-04-12 01:50:00.221 UTC [37] DETAIL:  parameters: $1 = '16', $2 = '2', $3 = '\x9a2c206353248a4f0f6a958bc658d021a6d607ac3d6125fb146dc1c2294ebbf3b3a38b79fbc120bc7bd8', $4 = '\xb4d378247975703df0ea38b8cc88d1b7a6f8bf1e0b1ce74767fbacbb1a6e7f321e52674c88b5fd', $5 = '\xf1f55020413f9aafc54ffd1ae62c149ab44bf3d9a8db301fe6f2ff0ae40978ba4aeb0cbaa5d7c06fc33ae292f4b381d8c5de875cd87e1735b355ce449eea81e68741ef2326a09f13353a71edeb9ca0a409bbbbc85aedfdc3fa51c45175eb17b81a813f449a414c', $6 = '\xf1f55020413f9aafc54ffd1a', $7 = '\xd97e6af09b332939e31df0cecd3c60b27cdb031fbd6e49e6fc0292d9f3fd74732f', $8 = '\x0fb954f2366b4fe199fbe5e049a8ca8e11d0535cef41b42310dc50653967e5c043a2', $9 = '\x0fb954f2366b4fe199fbe5e0'
2023-04-12 01:50:06.968 UTC [37] LOG:  duration: 6746.526 ms