43

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.

Community
  • 1
Gnanam
  • 987
  • 2
  • 9
  • 16

3 Answers3

34

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.

xenoterracide
  • 2,851
  • 5
  • 30
  • 33
  • 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 08:07
  • how about an index on a field with varchar(5000) could that affects the performance of the queries? – R0b0t0 Jun 22 '21 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. – xenoterracide Jun 23 '21 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 ...? – Ermiya Eskandary May 16 '22 at 15:10
  • @ErmiyaEskandary you can index long varchar, just to index it will place start of value to some limit, based on your database config. If you use TablePlus app to look at your database, inside indexes it shows something like index_name(number). How many chars will be used in index again depends on your server config + collation used. utf8 will index less chars, latin1 - more. while utf8 needs more bytes to store single char. – Lukas Liesis May 31 '22 at 12:39
6

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)

gbn
  • 69,809
  • 8
  • 163
  • 243
0

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"

Joe Love
  • 1