Ralph Smith <smithrn@washington.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
=========================