Message-ID: <45D47FE1.5010209@archonet.com>
Date: Thu, 15 Feb 2007 15:44:33 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: IceDove 1.5.0.9 (X11/20061220)
MIME-Version: 1.0
To: philippe <philippe@camailia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query with 'or' clause
References: <1171550213.45d470056249c@ssl0.ovh.net>
In-Reply-To: <1171550213.45d470056249c@ssl0.ovh.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

philippe wrote:
> explain analyse
> select count(*) from client_contact
> left join client_company using(cli_id)
> where (cli_mail = 'xxx@xxx.xx') OR
> (lower(cli_nom) = 'xxxxxx' and zipcode = '10001');
> 
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=37523.98..37523.99 rows=1 width=0) (actual
> time=3871.086..3871.087 rows=1 loops=1)
>    ->  Merge Left Join  (cost=0.00..36719.10 rows=321952 width=0) (actual
> time=3871.058..3871.058 rows=0 loops=1)

This is the root of the problem - it's expecting to match over 320000 
rows rather than 0.

I'm guessing there's a lot of correlation between cli_mail and cli_nom 
(you're expecting them to match the same clients) but the planner 
doesn't know this.

If this is a common query, you could try an index on zipcode - that 
might cut down the other side.

However, I have to ask why you're using a left-join? Do you really have 
rows in client_contact without a matching cli_id in client_company?

-- 
   Richard Huxton
   Archonet Ltd