Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 14 down vote accepted

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.

share|improve this answer
    
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 Jan 24 '11 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. –  Frank Heikens Jan 25 '11 at 8:07

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)

share|improve this answer

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"

share|improve this answer
1  
It's good to add some description of why it might be faster etc. More detail is usually helpful for future queries. –  Max Vernon Oct 9 '12 at 22:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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