philippe wrote:
explain analyse select count(*) from client_contact left join client_company using(cli_id) where (cli_mail = 'xxx@xxxxxx') 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
