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