Ingmar Brouns wrote: >> My solution is fast and efficient, it will call upper() only once >> per query. I don't see your problem. Different database systems >> do things in different ways, but as long as you can do what you need >> to do, that should be good enough. > I was toying around a little bit with this example, just for my > understanding, the function upper is called for every row in the > result. I think this has something to to with the filter in the plan. You are right, and the function is also called once per result row. The point I was really trying to make is that it is *not* called once per row in the table. > postgres=# explain select * from foo where test_upper(md5) like 'ABAAB%'; > QUERY PLAN > ---------------------------------------------------------------------------------------------- > Index Scan using foo_ind on foo (cost=0.50..14.02 rows=250 width=33) > Index Cond: ((test_upper(md5) ~>=~ 'ABAAB'::text) AND > (test_upper(md5) ~<~ 'ABAAC'::text)) > Filter: (test_upper(md5) ~~ 'ABAAB%'::text) > (3 rows) > > > So under my assumption that it is the filter that causes the function > execution, I don't understand > how a row can satisfy > > --which I read as >= 'ABAAB' and < 'ABAAC' > ((test_upper(md5) ~>=~ 'ABAAB'::text) AND (test_upper(md5) ~<~ 'ABAAC'::text)) > > and not > > (test_upper(md5) ~~ 'ABAAB%'::text) I don't know, but I suspect it has to do with collations. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
