Queries executed with
EXECUTE are re-planned with the actual parameter values passed to it every time. Since you are using Postgres 9.2, you may not even need
Either way, upgrading to Postgres 9.3 (or the upcoming 9.4) might help some more.
So, unless we have a type mismatch (like you already suspected) or a collation mismatch, your index should be used, if the parameter values are selective enough (retrieving less than ~ 5% of the table), which most probably is the case.
r_row.account_type_id is a
r_row.member_id is a
r_row.external_id is a
But how do you know that? If
r_row is declared as
record, data types of columns are defined in the assignment of an actual row ...
We need to see the complete plpgsql function with header and footer. And the exact table definition.
COLLATE "C" for your char-type index
An index on an
integer type column is far more efficient than one on
varchar(64) in any case. If you have to use the character type but you don't need to sort the column according to your collation setting (as I presume) it would be more efficient to use COLLATE "C" for index and query.
CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id COLLATE "C")
Your query would then be:
WHERE account_type_id = $1
AND member_id = $2
AND external_id COLLATE "C" = $3
USING r_row.account_type_id, r_row.member_id, r_row.external_id;
LIMIT 1 may or may not be needed. Your index is not
UNIQUE, though ...
Or you define the table column with
COLLATE "C" to begin with. Index and query will default to the collation of the column.
This may or may not solve the question at hand - it's a good idea for your situation in any case.
Details in this related answer (chapters "Strings and collation" and "Index":