Andreas Kendlinger <andreas.kendlinger@bestsolution.at> writes:
> I wrote a little stored function to simulate the EXTRACT(YEAR_MONTH ...)
> from mySQL.
> ...
> One Method call requires 53ms.
Really? Near as I can tell, it takes about 130 microsec on my ancient
HPPA machine, which is surely as slow as anything anyone's still using.
What PG version are you using? Are you sure you're only measuring the
function call and not some other overhead? I tested like this:
regression=# \timing
Timing is on.
regression=# select count(extractyearmonth('2008-02-04')) from generate_series(1,100000);
count
--------
100000
(1 row)
Time: 14431.591 ms
regression=# select count(1) from generate_series(1,100000);
count
--------
100000
(1 row)
Time: 1130.305 ms
regression=# select (14431.591-1130.305)/100000;
?column?
------------------------
0.13301286000000000000
(1 row)
Time: 7.262 ms
(This is with the IMMUTABLE marker removed from the function, else it'd
be called only once and we couldn't measure anything.)
However, I certainly think it can be done more easily --- use to_char.
It looks to me like to_char(some_timestamp, 'YYYYMM') does what you want,
and that runs in about 18 microsec.
regards, tom lane