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

On Mon, Apr 6, 2009 at 8:50 AM, Mario Splivalo
<mario(dot)splivalo(at)megafon(dot)hr> wrote:
> 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) ?

Either that or reverse the terms in the pk.

Also, you might want to look at adjusting random_page_access to
something around 1.5 to 2.0.