From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | philippe <philippe(at)camailia(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Slow query with 'or' clause |
Date: | 2007-02-15 15:44:33 |
Message-ID: | 45D47FE1.5010209@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
philippe wrote:
> explain analyse
> select count(*) from client_contact
> left join client_company using(cli_id)
> where (cli_mail = 'xxx(at)xxx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Smet | 2007-02-15 16:05:25 | Re: Question about Bitmap Heap Scan/BitmapAnd |
Previous Message | philippe | 2007-02-15 14:36:53 | Slow query with 'or' clause |