Postgresql – Is it a good idea/approach to index a VARCHAR column

indexperformancepostgresqlquery

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.

Best Answer

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.

Related Question