EXECUTE within function not using index? I have an accounts table with ~200k rows and an index for these columns:

  account_type_id BIGINT
, member_id BIGINT
, external_id VARCHAR(64)




CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id) TABLESPACE index_tbsp;


And I have a function that is performing some ETL work with the following query:

EXECUTE '
   SELECT * FROM normal_object.account
   WHERE account_type_id = $1
   AND member_id = $2
   AND external_id = $3'
INTO e_row
USING r_row.account_type_id, r_row.member_id, r_row.external_id;


Yet, the EXECUTE command is NOT using the index and I'm not sure why. My only guess is that the data types are not lining up. Yet r_row.account_type_id is a BIGINT, r_row.member_id is a BIGINT, and r_row.external_id is a VARCHAR(64).

Any suggestions as to why it is not using the index?
How can I get it to use the index? (I've already tried setting enable_seqscan off.)
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 EXECUTE:


PostgreSQL Stored Procedure Performance


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.

You write:


  r_row.account_type_id is a BIGINT, r_row.member_id is a BIGINT, and
  r_row.external_id is a VARCHAR(64).


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")
TABLESPACE index_tbsp;

Your query would then be:

EXECUTE '
   SELECT *
   FROM   normal_object.account
   WHERE  account_type_id = $1
   AND    member_id = $2
   AND    external_id COLLATE "C" = $3
   LIMIT  1'
INTO  e_row
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":


How do I efficiently get &quot;the most recent corresponding row&quot;?
The manual on "Collation Support".