[SQL] Difference in DATEs
Assume that x and y are of type DATE: 1. AGE( x, y ) < INTERVAL '...' works. 2. x < y + INTERVAL '...' works. 3. x - y < INTERVAL '...' doesn't work (but then, the minus operator is not defined in the manual for two DATE values). Question: Is the meaning of x - y well-defined? That is, is there a definition that I can count on? Not an important question, but I'm curious, since I like to write date differences in a style that makes readability/maintenance easy. -- Dean ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] degradation in performance
Good afternoon, I created a database with Postgres 7.3.4 under Linux RedHat 7.3 on a Dell PowerEdge server. One of the table is resultats(numbil, numpara, mesure, deviation) with an index on numbil. Each select on numbil returns up to 60 rows (that means 60 rows for one numbil with 60 different numpara) for example (20,1,500,3.5) (20,2,852,4.2) (20,12,325,2.8) (21,1,750,1.5) (21,2,325,-1.5) (21,8,328,1.2) etc.. This table contains now more than 6.500.000 rows and grows from 6000 rows a day. I have approximatively 1.250.000 rows a year. So I have 5 years of data online. Now, an insertion of 6000 lasts very lng, up to one hour... I tried to insert 100.000 yesterday evening and it was not done in 8 hours. Do you have any idea how I can improve speed - apart from splitting the table every 2 or 3 years which is the the aim of a database! I thank you for your suggestions. Regards. Alain Reymond CEIA Bd Saint-Michel 119 1040 Bruxelles Tel: +32 2 736 04 58 Fax: +32 2 736 58 02 [EMAIL PROTECTED] PGP key sur http://pgpkeys.mit.edu:11371 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] degradation in performance
Am 21.09.2004 13:27 schrieb Alain Reymond: > > Do you have any idea how I can improve speed - apart from splitting > the table every 2 or 3 years which is the the aim of a database! > Drop the index before you insert the data and recreate it afterwards. Use the "copy from ..." command instead of "insert into". Greetings, Martin -- Martin Knipper www : http://www.mk-os.de Mail : [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] degradation in performance
On Tue, 21 Sep 2004, Alain Reymond wrote: > I created a database with Postgres 7.3.4 under Linux RedHat 7.3 on a > Dell PowerEdge server. You should probably upgrade to the end of the 7.3 branch at the least (7.3.7). > One of the table is > resultats(numbil, numpara, mesure, deviation) > with an index on numbil. > > Each select on numbil returns up to 60 rows (that means 60 rows for > one numbil with 60 different numpara) for example > (20,1,500,3.5) > (20,2,852,4.2) > (20,12,325,2.8) > (21,1,750,1.5) > (21,2,325,-1.5) > (21,8,328,1.2) > etc.. > > This table contains now more than 6.500.000 rows and grows from > 6000 rows a day. I have approximatively 1.250.000 rows a year. So I > have 5 years of data online. > Now, an insertion of 6000 lasts very lng, up to one hour... > I tried to insert 100.000 yesterday evening and it was not done in 8 > hours. Some questions... Are you doing the inserts each in their own transaction or are you putting them in a single transaction or batching some number per transaction? Have you considered using copy for importing large blocks of data? Is this table basically only taking inserts (no delete or update)? Does this table have foreign key references to another table or have any triggers? You might see if reindexing the table or running vacuum full verbose(*) helps. (*) - I don't remember how 7.3 handled cluster, but if vacuum full verbose says there's lots of removable row entries, clustering the table might be faster. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] degradation in performance
6000 inserts, each in its own transaction, will be very long. Group your inserts in one transaction and it'll be faster (maybe 1-2 minutes). Have your program generate a tab-delimited text file and load it with COPY, you should be down to a few seconds. On Tue, 21 Sep 2004 13:27:43 +0200, Alain Reymond <[EMAIL PROTECTED]> wrote: Good afternoon, I created a database with Postgres 7.3.4 under Linux RedHat 7.3 on a Dell PowerEdge server. One of the table is resultats(numbil, numpara, mesure, deviation) with an index on numbil. Each select on numbil returns up to 60 rows (that means 60 rows for one numbil with 60 different numpara) for example (20,1,500,3.5) (20,2,852,4.2) (20,12,325,2.8) (21,1,750,1.5) (21,2,325,-1.5) (21,8,328,1.2) etc.. This table contains now more than 6.500.000 rows and grows from 6000 rows a day. I have approximatively 1.250.000 rows a year. So I have 5 years of data online. Now, an insertion of 6000 lasts very lng, up to one hour... I tried to insert 100.000 yesterday evening and it was not done in 8 hours. Do you have any idea how I can improve speed - apart from splitting the table every 2 or 3 years which is the the aim of a database! I thank you for your suggestions. Regards. Alain Reymond CEIA Bd Saint-Michel 119 1040 Bruxelles Tel: +32 2 736 04 58 Fax: +32 2 736 58 02 [EMAIL PROTECTED] PGP key sur http://pgpkeys.mit.edu:11371 ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Difference in DATEs
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > 3. x - y < INTERVAL '...' doesn't work (but then, the minus operator is not > defined in the manual for two DATE values). Sure it is: see 10th row in http://www.postgresql.org/docs/7.4/static/functions-datetime.html#OPERATORS-DATETIME-TABLE Forget the interval and compare to an integer. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Help with function
Thanks a bunch for the pointers and help.
One other hopefully quick question.
How do you query using a variable containing the query?
I'm trying to build a select statment based upon what parameters are being
passed to the function.
somthing like this:
Declare
Param1 varchar;
Param2 varchar;
SQLStr varchar;
Table_rec Table%ROWTYPE;
Begin
SQLStr:="select * from table"
Param1:= $1;
Param2 :=$2;
if (Param1 is not null) then
SQLStr := SQLStr || "where column=Param1";
else
SQLStr := SQLStr || "where column=Param2";
end if;
SQLStr := SQLStr || ";"
for Table_Rec in SQLStr loop
return next Table_rec;
end loop;
return;
end;
Is this possible?
Thanks again for any help,
Chris
--( Forwarded letter 1 follows )-
Date: Mon, 20 Sep 2004 13:51:09 -0700 (PDT)
To: chris.hoover
Cc: [EMAIL PROTECTED]
From: [EMAIL PROTECTED]
Subject: Re: [SQL] Help with function
On Mon, 20 Sep 2004, CHRIS HOOVER wrote:
> I need some help writing a simple function.
>
> Due to some program limitations for a program I run the db's for, I'm having
> to write some simple functions to run some selects. However, I am not sure
> how to have them correctly return the record(s) selected and/or how to
> properly call them from sql.
>
> Would someone be so kind as to help me with this.
>
> Here is an example function:
>
> CREATE OR REPLACE FUNCTION "public"."test_func" (varchar) RETURNS SETOF
> "public"."test_tbl" AS'
> Declare
> PCN varchar;
> test_tbl_rec clmhdr%ROWTYPE;
>
> Begin
>
> PCN := $1;
>
> select into test_tbl_rec * from test_tbl where test_tbl.pcn = PCN;
> return test_tbl_rec;
>
> end;
> 'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
If you want to return sets of rows, you're going to need to loop over the
returned rows from the select using return next.
These links may help:
http://www.varlena.com/varlena/GeneralBits/26.html
http://techdocs.postgresql.org/guides/SetReturningFunctions
> I was trying to call this function from psql using:
> select test_func('asdf');
As a side note, plpgsql set returning functions cannot be called in a
select list, only in the from clause (the above links will have examples).
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Help with function
On Tue, 21 Sep 2004, CHRIS HOOVER wrote: > Thanks a bunch for the pointers and help. > > One other hopefully quick question. > > How do you query using a variable containing the query? > > I'm trying to build a select statment based upon what parameters are being > passed to the function. > > somthing like this: > > Declare > Param1 varchar; > Param2 varchar; > SQLStr varchar; > Table_rec Table%ROWTYPE; > Begin > > SQLStr:="select * from table" > Param1:= $1; > Param2 :=$2; > > if (Param1 is not null) then > SQLStr := SQLStr || "where column=Param1"; > else > SQLStr := SQLStr || "where column=Param2"; > end if; > SQLStr := SQLStr || ";" > > for Table_Rec in SQLStr loop > return next Table_rec; > end loop; > return; > > end; > > Is this possible? Pretty much yes. You can use the FOR IN EXECUTE LOOP structure to run the query. The only thing is that you have to put the values into the string not the name of the parameters (probably using quote_literal). So rather than SQLStr := SQLStr || "where column = Param1"; you'd want something like: SQLStr := SQLStr || "where column = " || quote_literal(Param1); ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Difference in DATEs
Tom Lane wrote on 2004-09-21 07:01: Sure it is: see 10th row in http://www.postgresql.org/docs/7.4/static/functions-datetime.html#OPERATORS-DATETIME-TABLE Forget the interval and compare to an integer. Thanks; that line is not present in the table in the 7.3.4 docs. -- Dean ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Different topic
Ok, this is the third time I've sent this eMail; the other two times the mail was accepted (as shown by my postfix logs), but never appeared on the list. So, this time I've changed the subject field (was JOIN performance): Tom Lane wrote on 2004-09-20 22:19: In 7.3 only a view whose targetlist consists of simple column references can be pulled up into the nullable side of an outer join. OK, well you dragged me kicking and screaming into writing and 'end_date' function (IMMUTABLE but non-STRICT). I then use that in the JOINed VIEW (and other strategic places) rather than in the subject of the LEFT JOIN, and that works with no performance problems. Since we mentioned PostgreSQL versions, one of the things I'd like to see in v8.0 is support for "DELETE FROM ... AS ..." and "UPDATE ... AS ...". For example, I need to do things like: DELETE FROM xxx AS x WHERE field > xxx.field; In other words, a DELETE that has criteria similar to a self JOIN. Right now, I do this via: CREATE VIEW yyy AS SELECT * FROM xxx; DELETE FROM xxx WHERE field > yyy.field; And that works fine. But it's probably too late for v8.0 ... -- Dean ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] raise is not working
Hello again everyone. I need some help once again. I am following the postgresql pl/pgsql docs and trying to have my function show me the query it is trying to run since it not returning the expected results. However, it does not appear that the raise option is working. Can anyone please point me to what is wrong, or what server options need to be turned on. I have tried setting both server_min_messages (all the way down to debug5), and client_min_messages (to debug1), and I still do not get a responce. I did bounce the server after these changes. Anyway, here is a code snippet of what I am trying to do: SQL_Str := SQL_Str || "limit 15000;"; RAISE NOTICE ''SQL STRING = %'', SQL_Str; raise exception ''THIS SUCKS!''; for Clmhdr_rec in execute SQL_Str loop return next Clmhdr_rec; end loop; return; end; --- SQL_Str is defined as a varchar. Neither of the raise calls have done anything, but I don't get any errors either. I'm running 7.3.4. Thanks, Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] raise is not working
Chris, > I have tried setting both server_min_messages (all the way down to debug5), > and client_min_messages (to debug1), and I still do not get a responce. I > did bounce the server after these changes. Please paste your entire function definition, and a copy of your interactive session on psql (assuming you're using psql; if you're using a GUI tool, that could be the problem). I've a feeling that your function is erroring out *before* it gets to the raise. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] JOIN performance
Tom Lane <[EMAIL PROTECTED]> writes: > Fixing this properly is a research project, and I haven't thought of any > quick-and-dirty hacks that aren't too ugly to consider :-( Just thinking out loud here. Instead of trying to peek inside the CASE couldn't the optimizer just wrap the non-strict expression in a conditional that tests whether the row was found? -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] raise is not working
"CHRIS HOOVER" <[EMAIL PROTECTED]> writes: > SQL_Str is defined as a varchar. Neither of the raise calls have done > anything, but I don't get any errors either. The only way RAISE EXCEPTION "isn't going to do anything" is if control doesn't get to it. My bet would be that you are invoking some other function than you think you are --- we've seen examples of that sort of mistake recently. Check for multiple functions with same name and different argument types. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
