Re: [PERFORM] Used Memory

2005-10-21 Thread Jens-Wolfhard Schicke
--On Freitag, Oktober 21, 2005 03:40:47 + "Christian Paul B. Cosinas" <[EMAIL PROTECTED]> wrote: I am having a confusion to the memory handling of postgreSQL. 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

Re: [PERFORM] cached plans in plpgsql

2005-10-21 Thread Kuba Ouhrabka
Tom, many thanks. Perfect advice as usual... Corrected version attached for the archives. Kuba Tom Lane napsal(a): Kuba Ouhrabka <[EMAIL PROTECTED]> writes: IF Var_datos.pronargs > 0 THEN Var_args := ''; FOR i IN 0..Var_datos.pronargs-1 LOOP SELECT typname::

Re: [PERFORM] Sequential scan on FK join

2005-10-21 Thread Martin Nickel
On Tue, 18 Oct 2005 08:52:15 +0100, Richard Huxton wrote: > Martin Nickel wrote: >> When I turn of seqscan it does use the index - and it runs 20 to 30% >> longer. Based on that, the planner is correctly choosing a sequential >> scan - but that's just hard for me to comprehend. I'm joining on an

Re: [PERFORM] Used Memory

2005-10-21 Thread Christian Paul B. Cosinas
But as long as the memory is in the cache my database became much slower. What could probably be the cause of this? But When I restarted the database is back to normal processing. -Original Message- From: Jens-Wolfhard Schicke [mailto:[EMAIL PROTECTED] Sent: Friday, October 21, 2005 7:23

Re: [PERFORM] Used Memory

2005-10-21 Thread Christian Paul B. Cosinas
Also Does Creating Temporary table in a function and not dropping them affects the performance of the database? -Original Message- From: Jens-Wolfhard Schicke [mailto:[EMAIL PROTECTED] Sent: Friday, October 21, 2005 7:23 AM To: Christian Paul B. Cosinas; pgsql-performance@postgresql.org

Re: [PERFORM] Sequential scan on FK join

2005-10-21 Thread Martin Nickel
On Tue, 18 Oct 2005 08:52:15 +0100, Richard Huxton wrote: > 3. Actually - are you happy that your general configuration is OK? We're running dual Opteron 244s with 4G of memory. The platform is Suse 9.3, 64 bit. The database is on a 3ware 9500S-8 sata raid controller configured raid 10 with 4

[PERFORM] What gets cached?

2005-10-21 Thread Martin Nickel
I was reading a comment in another posting and it started me thinking about this. Let's say I startup an Oracle server. All my queries are a little bit (sometimes a lot bit) slow until it gets its "normal" things in memory, then it's up to speed. The "normal" things would include some small look

Re: [PERFORM] What gets cached?

2005-10-21 Thread Steinar H. Gunderson
On Fri, Oct 21, 2005 at 07:34:30AM -0500, Martin Nickel wrote: > Let's say I do the same thing in Postgres. I'm likely to have my very > fastest performance for the first few queries until memory gets filled up. > The only time Postgres seems to take advantage of cached data is when I > repeat t

Re: [PERFORM] What gets cached?

2005-10-21 Thread Alex Turner
Oracle uses LRU caching algorithm also, not LFU. AlexOn 10/21/05, Martin Nickel <[EMAIL PROTECTED]> wrote: I was reading a comment in another posting and it started me thinkingabout this.  Let's say I startup an Oracle server.  All my queries are alittle bit (sometimes a lot bit) slow until it get

Re: [PERFORM] Used Memory

2005-10-21 Thread Jon Brisbin
On Fri, 21 Oct 2005 03:40:47 - "Christian Paul B. Cosinas" <[EMAIL PROTECTED]> wrote: > > But after a number of access to the tables the memory is being used > and it is not being free up. Actually after this access to the > database and the server is just idle I noticed this behavior on my S

Re: [PERFORM] Used Memory

2005-10-21 Thread Alex Turner
[snip]to the second processor in my dual Xeon eServer) has got me to thepoint that the perpetually high memory usage doesn't affect my application server. I'm curious - how does the high memory usage affect your application server? Alex

Re: [PERFORM] What gets cached?

2005-10-21 Thread Michael Fuhr
On Fri, Oct 21, 2005 at 07:34:30AM -0500, Martin Nickel wrote: > I don't know of any way to view what is actually cached at any point in time In 8.1 (currently in beta) you can use contrib/pg_buffercache. Code for older versions is available on PgFoundry: http://pgfoundry.org/projects/pgbufferca

Re: [PERFORM] Inefficient escape codes.

2005-10-21 Thread Rodrigo Madera
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 functionint lo_write(PGconn *c

Re: [PERFORM] What gets cached?

2005-10-21 Thread Neil Conway
On Fri, 2005-21-10 at 07:34 -0500, Martin Nickel wrote: > Let's say I do the same thing in Postgres. I'm likely to have my very > fastest performance for the first few queries until memory gets filled up. No, you're not: if a query doesn't hit the cache (both the OS cache and the Postgres userspa

[PERFORM] Using LIMIT 1 in plpgsql PERFORM statements

2005-10-21 Thread Karl O. Pinc
Hi, I'm wondering if the plpgsql code: PERFORM 1 FROM foo; IF FOUND THEN ... is any slower than: PERFORM 1 FROM foo LIMIT 1; IF FOUND THEN ... Seems like it _could_ be smart enough to know that 1) It's selecting from a real table and not a function 2) GET DIAGNOSTICS is not used and theref