From: philippe <philippe@camailia.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query with 'or' clause
Date: Thu, 15 Feb 2007 18:10:45 +0100
Message-ID: <1171559445.45d494158444c@ssl0.ovh.net> (raw)
In-Reply-To: <45D47FE1.5010209@archonet.com>
References: <1171550213.45d470056249c@ssl0.ovh.net>
<45D47FE1.5010209@archonet.com>
Selon Richard Huxton <dev@archonet.com>:
> 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?
>
You are right, I was focused on server perf and I should have analysed my query.
Query time is ok now.
thanks you !!
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: philippe@camailia.com, dev@archonet.com
Subject: Re: Slow query with 'or' clause
In-Reply-To: <1171559445.45d494158444c@ssl0.ovh.net>
* 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