X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D23CD475AE5 for ; Sat, 29 Mar 2003 18:25:43 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id E50534758E6 for ; Sat, 29 Mar 2003 18:25:41 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.8/8.12.8) with ESMTP id h2TNPhub001004; Sat, 29 Mar 2003 18:25:43 -0500 (EST) To: Wil Peters Cc: pgsql-performance@postgresql.org Subject: Re: Bad perfomance of pl/pgsql-function on new server In-reply-to: <3E860D66.9030209@itaudit.demon.nl> References: <3E860D66.9030209@itaudit.demon.nl> Comments: In-reply-to Wil Peters message dated "Sat, 29 Mar 2003 22:17:26 +0100" Date: Sat, 29 Mar 2003 18:25:43 -0500 Message-ID: <1001.1048980343@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-32.5 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/198 X-Sequence-Number: 1496 Wil Peters writes: > On the new server - although this server has far better specs! - the > function does NOT perfom as well as on the old server. Searches take > several minutes, where on the old server a few SECONDS where needed. Is the new installation really equivalent to the old? I'd wonder about differences in multibyte compilation option, database locale and encoding, etc. Any of these could result in a huge hit in text-pushing performance. Another traditional post-upgrade problem is forgetting to VACUUM ANALYZE; but that probably shouldn't affect this function, since it's not issuing any database queries. (Personally I'd have written this sort of function in plperl or pltcl, either of which are far more appropriate for text-string-mashing than plpgsql. But that's not really answering your question.) regards, tom lane