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

От: Tom Lane
Тема: Re: seq scan over 3.3 million rows instead of single key index access
Дата: ,
Msg-id: 16660.1227398322@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: seq scan over 3.3 million rows instead of single key index access  (Gregory Stark)
Список: pgsql-performance

Скрыть дерево обсуждения

seq scan over 3.3 million rows instead of single key index access  ("Andrus", )
 Re: seq scan over 3.3 million rows instead of single key index access  (Gregory Stark, )
  Re: seq scan over 3.3 million rows instead of single key index access  (Tom Lane, )
  Re: seq scan over 3.3 million rows instead of single key index access  ("Andrus", )
   Re: seq scan over 3.3 million rows instead of single key index access  ("A. Kretschmer", )
 Re: seq scan over 3.3 million rows instead of single key index access  (Andreas Kretschmer, )
  Re: seq scan over 3.3 million rows instead of single keyindex access  ("Andrus", )
 Re: seq scan over 3.3 million rows instead of single key index access  (Andreas Kretschmer, )

Gregory Stark <> writes:
> "Andrus" <> 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


В списке pgsql-performance по дате сообщения:

От: "Andrus"
Дата:
Сообщение: Re: seq scan over 3.3 million rows instead of single key index access
От: PFC
Дата:
Сообщение: Re: Hash join on int takes 8..114 seconds