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
Date: Fri, 30 Jul 2010 08:11:29 +0200
Message-ID: <20100730061129.GA25240@a-kretschmer.de> (raw)
In-Reply-To: <173838.52053.qm@web51106.mail.re2.yahoo.com>
References: <173838.52053.qm@web51106.mail.re2.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas.kretschmer@schollglas.com, pgsql-general@postgresql.org
Subject: Re: How to improve: performance of query on postgresql 8.3 takes days
In-Reply-To: <20100730061129.GA25240@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox