I've got a relation in a Postgres 8.2 database similar to this:
CREATE TABLE foo ( foo_id varchar(160) NOT NULL, bar_id varchar(160) NOT NULL, created bigint NOT NULL, PRIMARY KEY (foo_id, bar_id) );
Let's leave aside for the moment the fact that the PK is composite and uses varchars (not my choice, legacy product, etc. etc.).
Currently, our application never issues queries that contain
created in a WHERE clause, so it's not indexed. However, we've got a new requirement that requires us to query on a range of
created values. The proposed query is along the lines of:
SELECT * FROM foo WHERE foo_id IN (...) AND created > 1234 AND created <= 6789
foo table is easily the largest in our application, but even then it's quite likely to have fewer than 50,000 rows even in the largest deployment, and there are rarely more than a dozen or so rows with the same
My question is, should an index be added on the
created column, considering that the
foo_id is part of the PK? If so, does it make sense to index only the
created column, or to index on (
My EXPLAIN on the above statement shows that the PK is being used, and then a FILTER operation is being applied. Using test data, the performance seems fine. My concern is performance if the tables grow to a massive size.