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```
  • If a query running that long uses very little CPU and you have plenty of RAM to spare, then those are very unlikely to be your problem - the DB would use more if it could. It is more likely that IO is the issue due to index use issues. More detail of the table design(s) and the query would be needed to give any meaningful advice. The output of EXPLAIN ANALYSE <query> would likely be very useful. Use the edit option to add these details to the question. – David Spillett Sep 10 '20 at 15:25
  • Please edit your question and add the execution plan (for a slow query needing 30 seconds) generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text and make sure you preserve the indention of the plan. Paste the text, then put ``` on the line before the plan and on a line after the plan. Please also include complete create index statements for all indexes as well. – a_horse_with_no_name Sep 10 '20 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.