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.
Mike
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
