Gaetano Mendola <mendola@bigfoot.com> writes:
> What about the wrong row expected ?
After I looked more closely, I realized that the planner hasn't any hope
of getting a really correct answer on that. You've got
WHERE ... ud.id_class = cd.id_class AND cd.id_provider = 39;
Now the planner doesn't have any problem figuring out that this will
select one "cd" row, but the number of "ud" rows matched varies wildly
depending on which one cd.id_class value happens to be involved.
Without actually pre-executing the query it has no way to know which
value will be involved, and so it has to fall back to a default
estimate, which is IIRC (number of rows in ud)/(number of distinct values).
Then there's a similar problem with estimating the number of rows
retrieved from ul.
> Anyway if the rows expected are 400 ( instead of 43 ) why not an index
> scan, with 400 rows on 1500000 seems a good choise do an index scan,
> isn't it ?
The trouble here is that because of the very skewed data statistics (in
both ud and ul), the planner can't really be sure that this query will
retrieve only a few rows from either table. There are other values in
both tables that would have retrieved vastly more data. The hash join
may be slower for this particular id_provider value, but it won't get
very much worse with other id_provider values --- a nestloop plan will.
Your idea of reducing id_provider to id_class using a separate query
seems like a good one to me --- that will allow the planner to generate
different plans depending on which id_class value is involved.
regards, tom lane