From: Michel Vrand <mvrand@pacwan.fr>
To: postgresql <pgsql-sql@postgresql.org>
Subject: Query optimisation
Date: Mon, 11 Dec 2000 16:20:33 +0100
Message-ID: <l03130308b65aa0d64c4a@[195.200.188.2]> (raw)
Hi!
I first apologize for my poor english.
We are working with linux-apache-postgresql-php3.
One among our queries is structured as follow :
$result = pg_exec ($conn, "SELECT produits.id_produit, produits.id_proprio,
articles.categ, groupes.nom, produits.pvttc_prod, articles.titre,
articles.type_1, articles.obs_art
FROM produits, articles, groupes
WHERE $conditions $relations;");
$conditions may be
1/ ...AND groupes.nom = '$something' AND....
or
2/ ...AND groupes.nom ~* '$something' AND....
In the first case, the query is reasonnably fast (0.30 s for 4 items on
15000)
In the second case, the query becomes very slow (more than 31 s for the same
result)
to give example, in the first case $something = "Beatles"
in the second case $something = "beatl"
How to optimise speed ? I tried to type EXPLAIN but I do not understand the
result :
For 1/
Nested loop (cost=0.00 size=1 width=86)
-> Merge Join (cost=0.00 size=1 width=72)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Seq Scan on produits (cost=0.00 size=0 width=16)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Index Scan using type_1.idx on articles (cost=0.00 size=0
width=56)
-> Index Scan using nom_groupe.idx on groupes (cost=0.00 size=0 width=16)
For 2/
Nested loop (cost=0.00 size=1 width=86)
-> Merge Join (cost=0.00 size=1 width=72)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Seq Scan on produits (cost=0.00 size=0 width=16)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Index Scan using type_1.idx on articles (cost=0.00 size=0
width=56)
-> Seq Scan on groupes (cost=0.00 size=0 width=16)
The only difference seems to be the use (or not) of index in the last line.
Do you think it causes such a decrease of speed ? How to work around ?
Thanks in advance
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mvrand@pacwan.fr
Subject: Re: Query optimisation
In-Reply-To: <l03130308b65aa0d64c4a@[195.200.188.2]>
References: <l03130308b65aa0d64c4a@[195.200.188.2]>
* 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