pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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...














view thread (38+ messages)  latest in thread

Message-ID: <opsb63h3abcq72hf@musicbox>
Permalink:  ../opsb63h3abcq72hf@musicbox/
Also on:    postgresql.org/message-id/opsb63h3abcq72hf@musicbox

 · 

reply

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