Date: Fri, 30 Jul 2010 08:11:29 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-general@postgresql.org
Subject: Re: How to improve: performance of query on postgresql 8.3 takes days
Message-ID: <20100730061129.GA25240@a-kretschmer.de>
References: <173838.52053.qm@web51106.mail.re2.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <173838.52053.qm@web51106.mail.re2.yahoo.com>
User-Agent: Mutt/1.5.9i

In response to Dino Vliet :
> I arrived at 15 functions because I had 7 or 8 joins in the past and saw that
> my disk was getting hid and I had heard someplace that RAM is faster so I
> rewrote those 7 or 8 joins as functions in pl/pgsql. They were just simple
> lookups, although some of the functions are looking stuff up in tables
> containing 78000 records. However, I thought this wouldn't be a problem because
> they are simple functions which look up the value of one variable based on a
> parameter. 3 of the more special functions are shown here:

I disaagree with you. The database has to do the same job, wherever with
7 or 8 joins or with functions, but functions (in this case) are slower.

You should run EXPLAIN <your statement with 7 or 8 joins> and show us
the result, i believe there are missing indexes.


> # - Memory -
> 
> 
> shared_buffers = 512MB # min 128kB or max_connections*16kB

How much RAM contains your server? You should set this to approx. 25% of RAM.


> work_mem = 50MB # min 64kB

That's maybe too much, but it depends on your workload. If you have a
lot of simultaneous and complex queries you run out of RAM, but if there
only one user (only one connection) it's okay.


> effective_cache_size = 256MB # was 128

That's too tow, effective_cache_size = shared_buffers + OS-cache


> Questions
> 
> 
>  1. What can I do to let the creation of table B go faster?

Use JOINs for table-joining, not functions.


> 
>  2. Do you think the use of indices (but where) would help me? I didn't go that
>     route because in fact I don't have a where clause in the create table B
>     statement. I could put indices on the little tables I'm using in the
>     functions.

Yes! Create indexes on the joining columns.


> 
>  3. What about the functions? Should I code them differently?

Don't use functions for that kind of table-joining.


> 
>  4. What about my server configuration. What could be done over there?

see above.


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99