Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance

От: Mario Splivalo
Тема: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance
Дата: ,
Msg-id: 49DA16C9.8050405@megafon.hr
(см: обсуждение, исходный текст)
Ответ на: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Scott Marlowe)
Ответы: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Scott Marlowe)
Список: pgsql-performance

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

Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Mario Splivalo, )
 Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Tom Lane, )
  Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Mario Splivalo, )
   Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Scott Marlowe, )
    Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Mario Splivalo, )
     Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Scott Marlowe, )
      Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Mario Splivalo, )
       Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Scott Marlowe, )
        Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Mario Splivalo, )
         Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance  (Scott Marlowe, )

Scott Marlowe wrote:
>> CREATE INDEX photo_info_data_ix_field_value
>>  ON user_info_data USING btree (field_value);
>>
>> So, there is index on (user_id, field_name). Postgres is using index for
>> user_id (...WHERE user_id = 12345) but not on field-name (...WHERE
>> field_name = 'f-spot'). When I add extra index on field name:
>>
>> CREATE INDEX photo_info_data_ix__field_name
>>  ON user_info_data USING btree (field_name);
>>
>> Then that index is used.
>
> On older versions of pgsql, the second of two terms in a multicolumn
> index can't be used alone.  On newer versions it can, but it is much
> less efficient than if it's a single column index or if the term is
> the first one not the second.

I'm using 8.3.7. So, you'd also suggest to keep that extra (in a way
redundant) index on field_name, since I need PK on (photo_id, field_name) ?

    Mike


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

От: Scott Marlowe
Дата:
Сообщение: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance
От: Lists
Дата:
Сообщение: Re: Best replication solution?