0

I want to ORDER BY some column in such a query:

SELECT "created", 
        pgp_pub_decrypt(username, dearmor('-----BEGIN PGP PRIVATE KEY BLOCK----- XXXXXXX -----END PGP PRIVATE KEY BLOCK-----'), 'password') AS "username" 
FROM "users" 
ORDER BY created

Whatever column I use to sort it takes very long time to compute. I know that I can wrap this select in another one and do the sorting there, but this cannot be solution from other reasons. Why this is taking so long? Am I doing something wrong?

This is query plan:

QUERY PLAN
-----------------------------------------------------------------------
 Sort  (cost=1261.92..1273.00 rows=4433 width=40)
  Sort Key: created
   ->  Seq Scan on users  (cost=0.00..993.41 rows=4433 width=40)

Result of EXPLAIN (analyze, buffers):

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1261.92..1273.00 rows=4433 width=40) (actual time=313515.489..313516.446 rows=4433 loops=1)
   Sort Key: created
   Sort Method: quicksort  Memory: 531kB
   Buffers: shared hit=2037
   ->  Seq Scan on users  (cost=0.00..993.41 rows=4433 width=40) (actual time=70.300..313499.510 rows=4433 loops=1)
         Buffers: shared hit=2037
 Planning time: 0.115 ms
 Execution time: 313517.322 ms
sennin
  • 8,552
  • 10
  • 32
  • 47
  • Please **[edit]** your question and add the execution plan generated using **`explain (analyze, buffers)`** (not just simple "explain"). [**Formatted text**](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). –  May 22 '18 at 10:39
  • 1
    I think that time is spent on decrypting, not on sorting. if you `EXPLAIN ANALYSE`, you'll know – Vao Tsun May 22 '18 at 10:58
  • No, when I don't use sorting results are immediate. – sennin May 22 '18 at 13:47
  • 1
    The sorting only adds 17 millisecond to the total runtime (not surprising for only 4433 rows). The majority of time is spent in the Seq Scan - _probably_ caused by the function being called 4433 times - but that can't be determined from the execution plan. –  May 23 '18 at 10:14

0 Answers0