Re: Using a User-Def function in a query

> Ralph Smith <smithrn(at)washington(dot)edu> writes:
>> I've written several user-defined functions (UDFs) for converting
>> dates to unix time, every which way.
>> ... but when I try to use the function in a query
>> # select count(distinct username) from stats where eventtime >
>> dtu_dmony('22 Sep 2008') ;
>> it never comes back...
> =====================================
> Did you EXPLAIN that query? Is it using the index I suppose you've
> got
> on eventtime? I'll bet that it's not, and that the reason why not is
> that you didn't mark the function IMMUTABLE (or STABLE, which is the
> correct marking if it depends on the timezone setting). The planner
> won't try to use volatile functions in index conditions.
>
> regards, t lane
=======================================
There is no index on that table, partially because I always have to do
a serial scan on it and an index isn't used. It's a huge table.

# \d stats+
Table "public.stats"
Column | Type | Modifiers
-----------+-----------------------+-----------
lab | character varying(30) |
name | character varying(50) |
status | character varying(40) |
eventtime | integer |
username | character varying(30) |
pkey | character varying(60) |

# EXPLAIN select count(*) from stats where eventtime > 1221894000 ;
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=269556.18..269556.19 rows=1 width=0)
-> Seq Scan on stats (cost=0.00..269466.96 rows=35687 width=0)
Filter: (eventtime > 1221894000)
(3 rows)

I made all my functions STABLE, but no change.

It takes about 22 seconds to run the function-less query above.

Do you think I need to use IMMUTABLE?

I'll give it a go in the meantime.

Thanks again all,
Ralph
=========================