[PERFORM] Used Memory

2005-10-20 Thread Christian Paul B. Cosinas
HI!   I am having a confusion to the memory handling of postgreSQL.   Here is the Scenario. I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9, which is a Dual Xeon Server and 6 gig of memory. Of course there is not much memory still used since it is just restarted. B

Re: [PERFORM] cached plans in plpgsql

2005-10-20 Thread Tom Lane
Kuba Ouhrabka <[EMAIL PROTECTED]> writes: > IF Var_datos.pronargs > 0 THEN > Var_args := ''; > FOR i IN 0..Var_datos.pronargs-1 LOOP > SELECT typname::varchar INTO Var_nameArg FROM pg_type WHERE oid > = Var_datos.proargtypes[i]; >

Re: [PERFORM] cached plans in plpgsql

2005-10-20 Thread Kuba Ouhrabka
> [howto recreate plpgsql functions] Start here: http://archives.postgresql.org/pgsql-hackers/2005-09/msg00690.php Great, thanks! I slighltly modified the function - it was not working for overloaded functions (same name, different arguments) and for functions with named arguments. Modified

Re: [PERFORM] cached plans in plpgsql

2005-10-20 Thread Merlin Moncure
Kuba wrote: > is there an easy way to flush all cached query plans in pl/pgsql > functions? I've long running sessions where data are changing and the > plans become inaccurate after a while. I can imagine something like > recreating all pl/pgsql functions. I can recreate them from sql source > fi

Re: [PERFORM] [HACKERS] tuning seqscan costs

2005-10-20 Thread Tom Lane
Katherine Stoovs <[EMAIL PROTECTED]> writes: > There must be something > wrong in the planning parameters after all if a plan that is slower by > a factor of tens or hundreds becomes estimated better than the fast > variant. Instead of handwaving, how about showing us EXPLAIN ANALYZE results for b

[PERFORM] cached plans in plpgsql

2005-10-20 Thread Kuba Ouhrabka
Hi, is there an easy way to flush all cached query plans in pl/pgsql functions? I've long running sessions where data are changing and the plans become inaccurate after a while. I can imagine something like recreating all pl/pgsql functions. I can recreate them from sql source files but I'd p

Re: [PERFORM] Materializing a sequential scan

2005-10-20 Thread Steinar H. Gunderson
On Thu, Oct 20, 2005 at 12:58:51AM -0400, Tom Lane wrote: > As-is, it's not doing anything for you ... certainly not enforcing > that the undergruppe_id be aktiv. Oops, yes, that's a bug -- thanks for noticing. (It does not matter particularily with the current data set, though.) /* Steinar */ --

Re: [PERFORM] Materializing a sequential scan

2005-10-20 Thread Steinar H. Gunderson
On Thu, Oct 20, 2005 at 12:37:25AM -0400, Tom Lane wrote: > That mdb_gruppekobling_transitiv_tillukning function looks awfully > grotty ... how many rows does it return, and how long does it take to > run by itself? How often does its temp table get vacuumed? A quick > band-aid might be to use TR

Re: [PERFORM] Deleting Records

2005-10-20 Thread Matteo Beccati
Hi, What could possibly I do so that I can make this fast? Here is the code inside my function: FOR temp_rec IN SELECT * FROM item_qc_doer LOOP DELETE FROM qc_session WHERE item_id = temp_rec.item_id; DELETE FROM item_qc_doer WHERE item_id = temp_rec.ite

Re: [PERFORM] Deleting Records

2005-10-20 Thread Christopher Kings-Lynne
What could possibly I do so that I can make this fast? Here is the code inside my function: FOR temp_rec IN SELECT * FROM item_qc_doer LOOP DELETE FROM qc_session WHERE item_id = temp_rec.item_id; DELETE FROM item_qc_doer WHERE item_id = temp_rec.item_id;

Re: [PERFORM] Deleting Records

2005-10-20 Thread Christopher Kings-Lynne
Here is the code inside my function: FOR temp_rec IN SELECT * FROM item_qc_doer LOOP DELETE FROM qc_session WHERE item_id = temp_rec.item_id; DELETE FROM item_qc_doer WHERE item_id = temp_rec.item_id; END LOOP; Item_qc_oder table contains 22,000 re

Re: [PERFORM] Deleting Records

2005-10-20 Thread Csaba Nagy
Christian, Do you have foreign keys pointing to your table with ON CASCADE... ? Cause in that case you're not only deleting your 22000 records, but the whole tree of cascades. And if you don't have an index on one of those foreign keys, then you might have a sequential scan of the child table on e

Re: [PERFORM] Deleting Records

2005-10-20 Thread Nörder-Tuitje , Marcus
what about firing a DELETE FROM qc_session S WHERE EXISTS (SELECT * FROM item_qc_doer i WHERE i.item_id = s.item_id); and DELETE FROM item_qc_doer S WHERE EXISTS (SELECT * FROM item_qc_doer i

[PERFORM] Deleting Records

2005-10-20 Thread Christian Paul B. Cosinas
Hi! I'm experiencing a very slow deletion of records. Which I thin is not right. I have a Dual Xeon Server with 6gig Memory. I am only deleting about 22,000 records but it took me more than 1 hour to finish this. What could possibly I do so that I can make this fast? Here is the code inside my f

Re: [PERFORM] Inefficient escape codes.

2005-10-20 Thread Nörder-Tuitje , Marcus
I guess, You should check, if a blob field and large object access is suitable for you - no escaping etc, just raw binary large objects. AFAIK, PQExecParams is not the right solution for You. Refer the "Large object" section: "28.3.5. Writing Data to a Large Object The function int lo_write(PGc