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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
