Database indexes result in sequential scans, add two more indexes

Dear Friends,

I am logging server side SQL execution time in a Drupal production database under PostgreSQL, the reference SQL 99 reference database.

In pgAdmin3, the table structure of locale is:

CREATE TABLE locales_source
(
  lid serial NOT NULL,
  "location" character varying(255) NOT NULL DEFAULT ''::character varying,
  textgroup character varying(255) NOT NULL DEFAULT 'default'::character varying,
  source text NOT NULL,
  "version" character varying(20) NOT NULL DEFAULT 'none'::character varying,
  CONSTRAINT locales_source_pkey PRIMARY KEY (lid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE locales_source OWNER TO gooze;

CREATE INDEX locales_source_source_idx
  ON locales_source
  USING btree
  (substr(source, 1, 30));

CREATE INDEX locales_source_textgroup_location_idx
  ON locales_source
  USING btree
  (substr(textgroup::text, 1, 30), location);

CREATE TABLE locales_target
(
  lid integer NOT NULL DEFAULT 0,
  translation text NOT NULL,
  "language" character varying(12) NOT NULL DEFAULT ''::character varying,
  plid integer NOT NULL DEFAULT 0,
  plural integer NOT NULL DEFAULT 0,
  i18n_status integer NOT NULL DEFAULT 0,
  l10n_status integer NOT NULL DEFAULT 0,
  CONSTRAINT locales_target_pkey PRIMARY KEY (language, lid, plural)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE locales_target OWNER TO gooze;

CREATE INDEX locales_target_lid_idx
  ON locales_target
  USING btree
  (lid);

CREATE INDEX locales_target_plid_idx
  ON locales_target
  USING btree
  (plid);

CREATE INDEX locales_target_plural_idx
  ON locales_target
  USING btree
  (plural);

In logs, I can see JOIN queries like:

SELECT s.lid, t.translation, s.version 
FROM locales_source s 
LEFT JOIN locales_target t ON s.lid = t.lid AND t.language = 'fr' 
WHERE s.source = 'Triggers are system events, such as when new content is added or when a user logs in. Trigger module combines these triggers with actions (functional tasks), such as unpublishing content or e-mailing an administrator. The <a href="@url">Actions settings page</a> contains a list of existing actions and provides the ability to create and configure additional actions.' AND s.textgroup = 'default'

Obviously, the JOINs should be performed on indexes, which is not the case. Thus on large installs, this query will run slowly.

t.language and s.source should carry indexes, which is not the case.

Creating an index on a substring of 30 caracters is not a solution to speed up such a query.