I am currently working on an application that requires column-based decryption for a few thousand of rows on a regular basis. The queries are decrypted using pgp_sym_decrypt, where several columns are decrypted for each select.
For a few thousand records, the queries are unfortunately quite slow and I found out that the CPU and the RAM were not quite used.
top gives 6.6% CPU usage and 14 GB RAM available out of 16 GB. Therefore, a "standard query" takes around 30s to proceed, while an acceptable performance would be rather around 5 seconds.
I tried changing a few parameters in postgres.conf, but I didn't get any performance improvement. The version of Postgres is 10.6.
Is there a possibility to increase the hardware usage of Postgres to make the decryption faster?
EDIT: Here is the result of the EXPLAIN ANALYZE:
Workers Planned: 1 Workers Launched: 1 Single Copy: true -> Sort (cost=5.34..5.35 rows=1 width=403) (actual time=28390.586..28391.014 rows=4999 loops=1) Sort Key: (pgp_sym_decrypt("namaApp_nprole".name, 'nada123456'::text)) -> Nested Loop (cost=0.84..5.33 rows=1 width=403) (actual time=7.291..28372.724 rows=4999 loops=1) -> Index Scan using "namaApp_npaccount_bank_id_7de78b3c" on "nadaApp_npaccount" (cost=0.42..2.64 rows=1 width=4) (actual time=0.060..15.340 rows=4999 loops=1) Index Cond: (bank_id = 1) -> Index Scan using "namaApp_nprole_account_id_2ad7bcd8" on "nadaApp_nprole" (cost=0.42..2.64 rows=1 width=884) (actual time=0.008..0.011 ro ws=1 loops=4999) Index Cond: (account_id = "namaApp_npaccount".id) Planning time: 1.733 ms Execution time: 28400.053 ms```