| 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: | Whole Thread | Raw Message | 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
| 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? |