Hi,
To avoid string concatenation using dates, I figured I could write a C
function:
#include "postgres.h"
#include "fmgr.h"
#include "utils/date.h"
#include "utils/nabstime.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
Datum dateserial (PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1 (dateserial);
David Jarvis wrote:
> [...]
> Yes. Here are the variations I have benchmarked (times are best of three):
> Variation #0
> -no date field-
> Explain: http://explain.depesz.com/s/Y9R
> Time: 2.2s
> Variation #1
> date('1960-1-1')
> Explain: http://explain.depesz.com/s/DW2
> Time: 2.6s
> Variatio
Hi, Tim.
Have you tested DATE_TRUNC()?
>
Not really; it returns a full timestamp and I would still have to
concatenate strings. My goal is to speed up the following code (where
*p_*parameters are user inputs):
*date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''')
d1,
On 11/06/10 11:25, David Jarvis wrote:
Datum dateserial (PG_FUNCTION_ARGS) {
int32 p_year = PG_GETARG_INT32(0);
int32 p_month = PG_GETARG_INT32(1);
int32 p_day = PG_GETARG_INT32(2);
DateADT d = date2j (p_year, p_month, p_day) - POSTGRES_EPOCH_JDATE;
PG_RETURN_DATEADT(d);
}
Compil
Hello all,
One query about PostgreSQL's index usage. If I select just one column on
which there is an index (or select only columns on which there is an
index), and the index is used by PostgreSQL, does PostgreSQL avoid table
access if possible? I am trying to understand the differences betwee
David Jarvis wrote:
>> Have you tested DATE_TRUNC()?
> Not really; it returns a full timestamp and I would still have to
> concatenate strings. My goal is to speed up the following code (where
> *p_*parameters are user inputs):
> *date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_
Hi Anj,
That is an indication that your system was less correctly
modeled with a random_page_cost=2 which means that the system
will assume that random I/O is cheaper than it is and will
choose plans based on that model. If this is not the case,
the plan chosen will almost certainly be slower for
Is there a way to determine a reasonable value for random_page_cost
via some testing with OS commands. We have several postgres databases
and determining this value on a case by case basis may not be viable
(we may have to go with the defaults)
On Fri, Jun 11, 2010 at 5:44 AM, Kenneth Marshall wr
If you check the archives, you will see that this is not easy
to do because of the effects of caching. The default values
were actually chosen to be a good compromise between fully
cached in RAM and totally un-cached. The actual best value
depends on the size of your database, the size of its worki
On Fri, 11 Jun 2010, Kenneth Marshall wrote:
If you check the archives, you will see that this is not easy
to do because of the effects of caching.
Indeed. If you were to take the value at completely face value, a modern
hard drive is capable of transferring sequential pages somewhere between
Jayadevan M wrote:
> One query about PostgreSQL's index usage. If I select just one
> column on which there is an index (or select only columns on which
> there is an index), and the index is used by PostgreSQL, does
> PostgreSQL avoid table access if possible?
PostgreSQL can't currently avoid
Jayadevan M wrote:
One query about PostgreSQL's index usage. If I select just one column on
which there is an index (or select only columns on which there is an
index), and the index is used by PostgreSQL, does PostgreSQL avoid table
access if possible?
PostgreSQL keeps information about what
Never say never with computer geeks
http://www.youtube.com/watch?v=mJyAA0oPAwE
On Fri, Jun 11, 2010 at 7:44 AM, Kenneth Marshall wrote:
> Hi Anj,
>
> That is an indication that your system was less correctly
> modeled with a random_page_cost=2 which means that the system
> will assume that
Greg Smith 2ndquadrant.com> writes:
>
> Max Williams wrote:
> > Can I just turn this off on 8.4.4 or is it a compile time option
>
> You can update your postgresql.conf to include:
>
> debug_assertions = false
>
> And restart the server. This will buy you back *some* of the
> performance lo
Hi,
The C function returns a DateADT, which is a typedef for int32, but the
> CREATE FUNCTION statement claims that it returns 'text'.
>
That'll do it. Thank you!
but whether that is faster or slower I don't know. But I
> don't see why this query needs to be fast in the first
> place. It seems t
Hi,
Here is code to convert dates from integers without string concatenation:
Edit dateserial.c:
#include "postgres.h"
#include "utils/date.h"
#include "utils/nabstime.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
Datum dateserial(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1 (dateserial);
Datu
Jayadevan,
PostgreSQL must go to the table to determine if the row you are requesting is
visible to your transaction. This is an artifact of the MVCC implementation.
Oracle can fetch the data from the index, since it doesn't keep multiple
representations of the rows, but it may need to check
David Jarvis wrote:
> [...]
>> invest too much time to have the user wait not 4.4, but
>> 2.2 seconds. You could also do the concatenation in the ap-
>> plication if that is faster than PostgreSQL's date arithme-
>> tics.
> No, I cannot. The concatenation uses the year that the measurement was
>
David Jarvis writes:
> dateserial(PG_FUNCTION_ARGS) {
> int32 p_year = (int32)PG_GETARG_FLOAT8(0);
> int32 p_month = PG_GETARG_INT32(1);
> int32 p_day = PG_GETARG_INT32(2);
Er ... why float? Integer is plenty for the range of years supported by
the PG datetime infrastructure. The above co
Hi, Tom.
extract(YEAR FROM m.taken)
I thought that returned a double precision?
Dave
Hi,
I added an explicit cast in the SQL:
dateserial(extract(YEAR FROM
m.taken)::int,'||p_month1||','||p_day1||') d1,
dateserial(extract(YEAR FROM
m.taken)::int,'||p_month2||','||p_day2||') d2
The function now takes three integer parameters; there was no performance
loss.
Thank y
21 matches
Mail list logo
