From: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance Bottleneck
Date: Wed, 04 Aug 2004 08:40:41 +0200
Message-ID: <opsb63h3abcq72hf@musicbox> (raw)
In-Reply-To: <X0ZPc.23543$yT2.23053@clgrps13>
References: <ktQPc.23297$yT2.16983@clgrps13>
<410FDC53.5080509@bigfoot.com>
<X0ZPc.23543$yT2.23053@clgrps13>
> 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...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com
Subject: Re: Performance Bottleneck
In-Reply-To: <opsb63h3abcq72hf@musicbox>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox