PostgreSQL executing my function too many times during query

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: Postgres-General <pgsql-general(at)postgresql(dot)org>
Subject: PostgreSQL executing my function too many times during query
Date: 2008-05-15 22:51:59
Message-ID: 482CBE8F.4030309@lorenso.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-05-15 23:05:23 Re: PostgreSQL executing my function too many times during query
Previous Message Vance Maverick 2008-05-15 22:43:49 triggers: dynamic references to fields in NEW and OLD?