Is it a good idea/approach to index a VARCHAR column?

Question

We're using PostgreSQL v8.2.3.

There are tables involved: EMPLOYEE and EMAILLIST.

Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6)
Table 2: EMAILLIST (email)

2 tables are joined in such a way that if either EMPLOYEE.EMAIL1 or EMPLOYEE.EMAIL2 do not have a matching entry, those rows will be returned.

SELECT employee.email1, employee.email2,
        e1.email IS NOT NULL AS email1_matched, e2.email IS NOT NULL AS email2_matched
   FROM employee
   LEFT JOIN emaillist e1 ON e1.email = employee.email1
   LEFT JOIN emaillist e2 ON e2.email = employee.email2
 WHERE e1.email IS NULL OR e2.email IS NULL

Column EMAIL which is varchar(256) of EMAILLIST table is indexed. Now, the response time is 14 seconds.

Table count statistics: Currently, EMPLOYEE has got 165,018 records & EMAILLIST has got 1,810,228 records, and both tables are expected to grow in future.

1. Is it a good idea/approach to index a VARCHAR column? This question immediately strike on my mind because of the reason that we've not indexed a VARCHAR column before in our application. Experts advice/suggestion on this are highly appreciated.
2. With this current query and index, the response time of 14 seconds is reasonable or is there any scope for further tuning? What are other user's real-time experience/opinion based on this kind of table size and response time?

NOTE: My actual requirement/use case is explained in detail here.

Answer 1

There's nothing wrong with indexing a varchar column if you're going to be doing queries based on it. However please keep in mind that there a limits to some indexes and how much they can index in a single field. Example you can't index a column that can contain an unlimited amount of text. However you should be able to do an index on varchar(256) without issue. Try it, and analyze the improvements in your queries performance to see if it helps.

Answer 2

There is no issue indexing a varchar column as such

Where it can become an issue is when you have the varchar column as an FK in a billion row table. You'd then have a surrogate key for the PK and FK, but you'd still need a unique constraint/index on the natural varchar key.

Your tables are quite small and the performance could be related to the OR clause. Unfortunately, the same issue applies no matter how you structure the query (and I'm not familiar enough with PostgresSQL to offer much sorry)

Answer 3

Try getting rid of the "OR e2.email IS NULL" part of your query and see how fast it runs. If it runs faster you may be able to run it quicker with a "union all"