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





view thread (2+ messages)  latest in thread

Message-ID: <l03130308b65aa0d64c4a@[195.200.188.2]>
Permalink:  ../l03130308b65aa0d64c4a@%5B195.200.188.2%5D/
Also on:    postgresql.org/message-id/l03130308b65aa0d64c4a@[195.200.188.2]

 · 

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 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