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.

3 Answers 3


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.

  • Thanks for your valuable comment. Is there any scope for further tuning of my query in this regard to reduce the response time from 14 seconds?
    – Gnanam
    Commented Jan 24, 2011 at 13:53
  • 2
    Without the results from EXPLAIN, it's impossible to tell what to optimize. Version 8.2.3 is also outdated, you should upgrade to a newer version, you're 4 years behind in maintenance. Versions 8.3, 8.4 and 9.0 are also faster in many situations. Better statistics also help to gain performance. Commented Jan 25, 2011 at 8:07
  • how about an index on a field with varchar(5000) could that affects the performance of the queries?
    – R0b0t0
    Commented Jun 22, 2021 at 18:57
  • @R0b0t0 I don't know that this is relevant in modern versions of Postgres, pretty sure that there are many performance improvements. However, a unique key of that size may be slow on insert in some cases. I recall that Postgres starts, though, by looking at the first 8? characters to optimize in recentish versions, I think in the 9.x range, but I can't recall. Commented Jun 23, 2021 at 18:58
  • Example you can't index a column that can contain an unlimited amount of text. - I've just been able to create an index on a varchar (varchar(MAX)) column on PostgreSQL 13. Can you please clarify what you meant? You can't as in the engine doesn't let you or ...? Commented May 16, 2022 at 15:10

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)


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"


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.