Why the index is not used ?

Hi
I would like to submit the following problem to the PostgreSQL community. In my company, we have data encryption needs.
So I decided to use the following procedure :

(1) Creating a table with a bytea type column to store the encrypted data
CREATE TABLE cartedecredit(card_id SERIAL PRIMARY KEY, username VARCHAR(100), cc bytea);

(2) inserting encrypted data
INSERT INTO cartedecredit(username,cc) SELECT 'individu ' || x.id, pgp_sym_encrypt('test value ' || x.id, 'motdepasse','compress-algo=2, cipher-algo=aes256') FROM generate_series(1,100000) AS x(id);

(3) Querying the table
SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM cartedecredit WHERE pgp_sym_decrypt(cc, 'motdepasse')='test value 32';

pgp_sym_decrypt

-----------------

test value 32

(1 row)

Time: 115735.035 ms (01:55.735)
-> the execution time is very long. So, I decide to create an index

(4) Creating an index on encrypted data
CREATE INDEX idx_cartedecredit_cc02 ON cartedecredit(cc);

(5) Querying the table again

SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM cartedecredit WHERE pgp_sym_decrypt(cc, 'motdepasse')='test value 32';
pgp_sym_decrypt

-----------------

test value 32

(1 row)

Time: 118558.485 ms (01:58.558) -> almost 2 minutes !!
postgres=# explain analyze SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM cartedecredit WHERE pgp_sym_decrypt(cc, 'motdepasse')='test value 32';

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------

Seq Scan on cartedecredit (cost=0.00..3647.25 rows=500 width=32) (actual time=60711.787..102920.509 rows=1 loops=1)

Filter: (pgp_sym_decrypt(cc, 'motdepasse'::text) = 'test value 32'::text)

Rows Removed by Filter: 99999

Planning time: 0.112 ms

Execution time: 102920.585 ms

(5 rows)

? the index is not used in the execution plan. maybe because of the use of a function in the WHERE clause. I decide to modify the SQL query

(6) Querying the table
SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM cartedecredit WHERE cc=pgp_sym_encrypt('test value 32', 'motdepasse');
pgp_sym_decrypt

-----------------

(0 rows)

Time: 52659.571 ms (00:52.660)

? The execution time is very long and I get no result (!?)

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------

Seq Scan on cartedecredit (cost=0.00..3646.00 rows=1 width=32) (actual time=61219.989..61219.989 rows=0 loops=1)

Filter: (cc = pgp_sym_encrypt('test value 32'::text, 'motdepasse'::text))

Rows Removed by Filter: 100000

Planning time: 0.157 ms

Execution time: 61220.035 ms

(5 rows)

? My index is not used.

QUESTIONS :
- why I get no result ?

- why the index is not used?

Thanks in advance

Best Regards
Didier

[cid:image002(dot)png(at)01D14E0E(dot)8515EB90]

Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD