Re: How to improve: performance of query on postgresql 8.3 takes days

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