> > using equality operator, the result is at most 10000
> > rows (min(card(R1),card(R2)). But pg estimates
> > 1 000 000 (uses selectivity 0.01 here).
>
> Surely not. If you inner join, you can get many more than min
> (card(R1),card(R2)), if you are joining over non-unique keys (a common
> case). For example:
Ohh yes. You are right. Also I found that my main problem
was not running VACUUM ANALYZE so that I have invalid value
of column's disbursion.
I ran it and now hash join estimates row count correctly.
> > But indexscan always lookups actual record in heap even if
> > all needed attributes are contained in the index.
> > Oracle and even MSSQL reads attributes directly from index
> > without looking for actual tuple at heap.
>
> I believe this is because PgSQL doesn't remove entries from the index
> at DELETE time, thus it is always necessary to refer to the main table
> in case the entry found in the index has since been deleted.
Hmm it looks reasonable. But it still should not prevent us
to retrieve data directly from index whether possible. What
do you think ? Only problem I can imagine is if it has to
do something with locking ..
regards, devik