Re: [PERFORM] Analysis Function

2010-06-11 Thread David Jarvis
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);

Re: [PERFORM] Analysis Function

2010-06-11 Thread Tim Landscheidt
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

Re: [PERFORM] Analysis Function

2010-06-11 Thread David Jarvis
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,

Re: [PERFORM] Analysis Function

2010-06-11 Thread Heikki Linnakangas
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

[PERFORM] Query about index usage

2010-06-11 Thread Jayadevan M
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

Re: [PERFORM] Analysis Function

2010-06-11 Thread Tim Landscheidt
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_

Re: [PERFORM] slow query performance

2010-06-11 Thread Kenneth Marshall
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

Re: [PERFORM] slow query performance

2010-06-11 Thread Anj Adu
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

Re: [PERFORM] slow query performance

2010-06-11 Thread Kenneth Marshall
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

Re: [PERFORM] slow query performance

2010-06-11 Thread Matthew Wakeling
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

Re: [PERFORM] Query about index usage

2010-06-11 Thread Kevin Grittner
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

Re: [PERFORM] Query about index usage

2010-06-11 Thread Greg Smith
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

[PERFORM] O/T: performance tuning cars

2010-06-11 Thread Dave Crooke
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

Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?

2010-06-11 Thread John Reeve
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

Re: [PERFORM] Analysis Function

2010-06-11 Thread David Jarvis
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

Re: [PERFORM] Analysis Function

2010-06-11 Thread David Jarvis
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

Re: [PERFORM] Query about index usage

2010-06-11 Thread Bob Lunney
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

Re: [PERFORM] Analysis Function

2010-06-11 Thread Tim Landscheidt
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 >

Re: [PERFORM] Analysis Function

2010-06-11 Thread Tom Lane
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

Re: [PERFORM] Analysis Function

2010-06-11 Thread David Jarvis
Hi, Tom. extract(YEAR FROM m.taken) I thought that returned a double precision? Dave

Re: [PERFORM] Analysis Function

2010-06-11 Thread David Jarvis
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