I have several records in my database which have encrypted fields. I
want to find all the ones that match a certain format but do NOT match
another.
My problem is that the 'cc_encrypt' function is being executed for every
matching row in the table instead of just once. The function was
defined as STABLE and I tried IMMUTABLE as well. That doesn't seem to
be helping.
This format causes the function to execute too many times:
SELECT COUNT(*) AS result
FROM credit_card
WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
AND card_number_enc != cc_encrypt('4111111111111111', 'pwd');
So, the second cc_encrypt is being executed for every row matching the
first cc_encrypt condition. My expectation is that both functions would
be executed ONCE the result would be used in the query like this:
SELECT COUNT(*) AS result
FROM credit_card
WHERE card_number_enc = <RESULT>
AND card_number_enc != <RESULT>;
To fix the "bug", I can rewrite my query like this and the functions
will only be executed once each as expected:
SELECT COUNT(*) AS result
FROM credit_card
WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
AND card_number_enc NOT IN (
SELECT cc_encrypt('4111111111111111', 'pwd')
);
I don't understand what's happening here. Any help? Maybe the EXPLAIN
tells something?
# EXPLAIN SELECT COUNT(*) AS result
# FROM credit_card
# WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
# AND card_number_enc != cc_encrypt('4111111111111111', 'pwd');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Aggregate (cost=196.96..196.97 rows=1 width=0)
-> Bitmap Heap Scan on credit_card (cost=4.87..196.76 rows=79 width=0)
Recheck Cond: (card_number_enc =
credit_card_encrypt('4111-1111-1111-1111'::text, 'password'::text))
Filter: (card_number_enc <>
credit_card_encrypt('4111111111111111'::text, 'password'::text))
-> Bitmap Index Scan on credit_card_idx_card_number_enc
(cost=0.00..4.85 rows=79 width=0)
Index Cond: (card_number_enc =
credit_card_encrypt('4111-1111-1111-1111'::text, 'password'::text))
(6 rows)
Oddly, when I use 'EXPLAIN', I see my debug logging "RAISE NOTICE"
statements showing that the function was only executed once each. When
I don't use EXPLAIN, it's back to showing that the second function was
executed for each matching record of the first.
# SELECT version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)
-- Dante
