RE: Very bad performance on a query - Mailing list pgsql-general

Hi Guillaume,

I'm definitely NOT an expert in SQL, or just about anything, really, wow --

But, I'll tell you one thing that I do know, and that's bigger isn't
necessarily better, except when it comes to a select few things, depending
on how you like your steaks, and other times, what your gender is, or isn't.

That all said, loading big honkin indices of tables containing 500,000 rows
into memory might be a problem.  The primary key of a table is meant to be a
short unique identifier of a table row.  Indexing and indices exist to help
speed up very specific queries, but with tradeoffs, including slow insertion
and update times (i bet it's very slow inserting into your 500000 row table)
... do you do a vacuum analyze very frequently?  You said that you can't
load your database into memory because each query depends on other queries,
etc ... I bet that your huge index gets very useless very quickly if not.

Also, maybe breaking apart your larger table into smaller chunks might help.
I am using one of those "radix search" type of things to break my huge
tables in to more manageable "buckets".

Viva la PostgreSQL!


-----Original Message-----
From: Guillaume Lémery [mailto:glemery@comclick.com]
Sent: Wednesday, January 24, 2001 10:15 AM
To: pgsql-general
Subject: Very bad performance on a query


Hi,

I've already posted something about this trouble, and all I tried didn't
worked very well...

Here is my tables :

CREATE TABLE accord_editeur
(
id_regie int4 not null,
num_editeur int4 not null,
num_site int4 not null,
num_emplacement int4 not null,
num_campagne int4 not null,
num_publicite int4 not null,
num_periode int4,
par_id_technologie int4 not null,
affichage_possible int4 default 0,
ponderation_calculee int4,
date_pilotage timestamp NULL,
id_ct1 int4,
PRIMARY
KEY(id_regie,num_editeur,num_site,num_emplacement,num_campagne,num_publicite
)
);

(I've got a primary key on multiple fields because of the complexity of
my database.)

CREATE TABLE parametre
(
id_parametre int4 not null primary key,
id_regie int4 NULL ,
par_id_parametre int4 NULL ,
type INT4 not null,
valeur_str varchar null,
valeur_int int4 null,
valeur_fl float8 null,
valeur_txt varchar,
date_pilotage timestamp NULL,
id_ct1 int4
);
CREATE INDEX parametre_tracking_idx ON parametre(type, valeur_int);

Here is my query :

SELECT ae.id_regie,
ae.num_campagne,
ae.num_publicite,
ae.ponderation_calculee * random(),
ae.num_periode
FROM accord_editeur ae,
parametre par
WHERE ae.id_regie=1
AND ae.num_editeur = 1494
AND ae.num_site = 1
AND ae.num_emplacement = 1
AND ae.affichage_possible = 1
AND ae.par_id_technologie = par.id_parametre
AND par.type = 10
AND par.valeur_int = 1


If I do an Explain I got this :

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..15422.73 rows=1 width=56)
   ->  Index Scan using accord_editeur_pkey on accord_editeur ae
(cost=0.00..15420.71 rows=1 width=48)
   ->  Index Scan using parametre_tracking_idx on parametre par
(cost=0.00..2.02 rows=1 width=8)

EXPLAIN

Table accord_editeur only have about 500 000 records.
Table parametre about 200.

So, with Index Scan it is supposed to run fast.

To access to the base, I have a connection pooling tool which maintains
always 5 connections to the base (to have more doesn't increase
performance).
But if I launch 100 queries at a single time (which is what my base is
going to face), the execution of the queries is too too slow...
The server is a bi-PIII 660 with 1GB RAM.
During the execution, a top reveal that CPU usage is 100% - about 20%
per postmaster !
This server is dedicated to database... I've separated the web server to
see what happens...

I can't load my base into memory because its data always change and I
have to always have the last data to set my parameters for other queries...

Has anybody an idea of what is wrong with this query ?
I don't have any ideas anymore.

Thanks a lot...

Guillaume.