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: 49DA13C1.2030200@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:
> On Mon, Apr 6, 2009 at 6:20 AM, Mario Splivalo
> <> wrote:
>> Scott Marlowe wrote:
>>> It's not really solved, it's just a happy coincidence that the current
>>> plan runs well.  In order to keep the query planner making good
>>> choices you need to increase stats target for the field in the index
>>> above.  The easiest way to do so is to do this:
>>>
>>> alter database mydb set default_statistics_target=100;
>>>
>>> and run analyze again:
>>>
>>> analyze;
>> So, i removed the index on field_name, set default_default_statistics_target
>> to 100, analyzed, and the results are the same:
>
> Why did you remove the index?
>

Because I already have index on that column, index needed to enforce PK
constraint. Here is the original DDL for the table:

CREATE TABLE photo_info_data
(
   photo_id integer NOT NULL,
   field_name character varying NOT NULL,
   field_value character varying,
   CONSTRAINT photo_info_data_pk PRIMARY KEY (user_id, field_name)
)

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.

    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?