Received: from webcam34.pacwan.net (smtp-out-4.pacwan.net [195.200.188.237]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBBFCRm20620 for ; Mon, 11 Dec 2000 10:12:28 -0500 (EST) (envelope-from mvrand@pacwan.fr) Received: from [195.200.188.63] (195.200.188.63) by webcam34.pacwan.net with ESMTP (Eudora Internet Mail Server 2.2.1); Mon, 11 Dec 2000 16:12:52 +0100 X-Sender: mvrand@pop.pacwan.fr Message-Id: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Mailer: Eudora Light F3.1.3l Date: Mon, 11 Dec 2000 16:20:33 +0100 To: postgresql From: Michel Vrand Subject: Query optimisation X-Archive-Number: 200012/67 X-Sequence-Number: 398 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