Re: How to improve: performance of query on postgresql 8.3 takes days - Mailing list pgsql-general

From A. Kretschmer
Subject Re: How to improve: performance of query on postgresql 8.3 takes days
Date
Msg-id 20100730061129.GA25240@a-kretschmer.de
Whole thread Raw
In response to How to improve: performance of query on postgresql 8.3 takes days  (Dino Vliet <dino_vliet@yahoo.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Sandeep Srinivasa
Date:
Subject: Re: Which CMS/Ecommerce/Shopping cart ?
Next
From: Scott Frankel
Date:
Subject: PQescapeStringConn