X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8BEEFD1B20D for ; Wed, 4 Aug 2004 03:39:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32670-01 for ; Wed, 4 Aug 2004 06:39:59 +0000 (GMT) Received: from boutiquenumerique.com (gailleton-2-82-67-9-10.fbx.proxad.net [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 88994D1B1B6 for ; Wed, 4 Aug 2004 03:39:50 -0300 (ADT) Received: (qmail 23408 invoked from network); 4 Aug 2004 08:39:57 +0200 Received: from unknown (HELO musicbox) (192.168.0.2) by gailleton-2-82-67-9-10.fbx.proxad.net with SMTP; 4 Aug 2004 08:39:57 +0200 Date: Wed, 04 Aug 2004 08:40:41 +0200 To: pgsql-performance@postgresql.org Subject: Re: Performance Bottleneck References: <410FDC53.5080509@bigfoot.com> From: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: User-Agent: Opera M2/7.50 (Linux, build 673) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/43 X-Sequence-Number: 7679 > The queries themselves are simple, normally drawing information from one > table with few conditions or in the most complex cases using joins on > two table or sub queries. These behave very well and always have, the > problem is that these queries take place in rather large amounts due to > the dumb nature of the scripts themselves. Hum, maybe this "dumb" thing is where to look at ? I'm no expert, but I have had the same situation with a very dump PHP application, namely osCommerce, which averaged about 140 (!!!!!) queries on a page ! I added some traces to queries, and some logging, only to see that the stupid programmers did something like (pseudo code): for id in id_list: select stuff from database where id=id Geee... I replaced it by : select stuff from database where id in (id_list) And this saved about 20 requests... The code was peppered by queries like that. In the end it went from 140 queries to about 20, which is still way too much IMHO, but I couldn't go lower without an extensive rewrite. If you have a script making many selects, it's worth grouping them, even using stored procedures. For instance using the classical "tree in a table" to store a tree of product categories : create table categories ( id serial primary key, parent_id references categories(id), etc ); You basically have these choices in order to display the tree : - select for parent_id=0 (root) - for each element, select its children - and so on OR - make a stored procedure which does that. At least 3x faster and a lot less CPU overhead. OR (if you have say 50 rows in the table which was my case) - select the entire table and build your tree in the script It was a little bit faster than the stored procedure. Could you give an example of your dumb scripts ? It's good to optimize a database, but it's even better to remove useless queries...