Selon Richard Huxton <dev@xxxxxxxxxxxx>: > 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? > 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 >
