On Mon, Apr 6, 2009 at 8:50 AM, Mario Splivalo <mario.splivalo@xxxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
