Re: seq scan over 3.3 million rows instead of single key index access

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Andrus" <kobruleht2(at)hot(dot)ee> writes:
>> There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int.
>> Instead of using single key index, 8.1.4 scans over whole rid table.
>> Sometimes idtelluued can contain more than single row so replacing join with
>> equality is not possible.
>>
>> How to fix ?

> Firstly the current 8.1 release is 8.1.15. Any of the bugs fixed in those 11
> releases might be related to this.

If this can still be reproduced in 8.1.15 it would be worth looking into.
My first guess is that there are multiple relevant indexes on the big
table and the old bugs in choose_bitmap_and() are making it mess up.

> The planner thinks there are 2,140 rows in that temporary table so I don't
> believe this is from the example posted. I would suggest running ANALYZE
> idtellUued at some point before the problematic query.

No, that's a pretty likely default assumption for a never-vacuumed,
never-analyzed table. Your advice is correct though.

regards, tom lane