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