2

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.)

Community
  • 1
Joishi Bodio
  • 3,378
  • 2
  • 16
  • 19
  • So you tried SET enable_seqscan off? What was the effect? Why is the data type or your ID column external_id varchar(64)? What's in it exactly? How long are the strings and most importantly what is the COLLATION your index was created with? It needs to match the collation setting of your session, or the index cannot be used. Details here. As first, quick test: remove the condition on external_id in your query and try again, to verify the column is the culprit. – Erwin Brandstetter Nov 12 '14 at 02:33
  • Try run the query in psql ,and see if the query use the index. – francs Nov 12 '14 at 08:50
  • @francs Why would it be any different in an other client? – András Váczi Nov 12 '14 at 09:16
  • @dezso: Because I think it's may differenct between function and psql client, just try . – francs Nov 12 '14 at 09:52
  • Erwin - I have already tried SET enable_seqscan OFF - it still wasn't u sing the index. The DB in question is a data warehouse, so the external_id could be any primary key or other identifying code for the account in the external system - so a string (in case some system decides to use alpha-numeric). As of now, I believe the set of data can be anywhere from 5 to 32 chars in length. If I remove the condition on external_id, then it will no longer find the correct row that I'm looking for.. The collation in the index is "default" for the string. – Joishi Bodio Nov 12 '14 at 15:01
  • Francs - when I run EXPLAIN on the SELECT (using dummy vars for the params) it uses the index. But when it's ran in the function it doesn't. I have been doing all of this stuff via psql, though .. not using PgAdmin or some other 3rd party software (although not sure why that would matter since the backend generates the explain plan - not the client). – Joishi Bodio Nov 12 '14 at 15:02
  • @JoishiBodio: We'll need to see the complete plpgsql function including header and footer. And also the actual table definition - what you get with\d normal_object.account` in psql. – Erwin Brandstetter Nov 12 '14 at 17:47

1 Answers1

2

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:

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":

Community
  • 1
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • I have the EXECUTE wrapper for the SQL statement because the table MAY start out relatively small and the planner would decide not to use the index. I will try to find time to answer some of the questions you posed to my original post today - however, this project I'm working on is not priority at the moment. – Joishi Bodio Nov 13 '14 at 16:06
  • I'm going to mark this as correct, although I was not able to get it using an index before my time was up (I am no longer working on that project). I assume this works. ;) – Joishi Bodio Dec 16 '14 at 23:19