PostgreSQL – String Comparison to Text Column Performance

The documentation often gives you an answer to such questions. Like in this case, too:

The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale. As an example, you might index a varchar column like this:

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index. Such queries cannot use the xxx_pattern_ops operator classes. (Ordinary equality comparisons can use these operator classes, however.) It is possible to create multiple indexes on the same column with different operator classes.

The documentation goes on to say:

If you do use the C locale, you do not need the xxx_pattern_ops operator classes, because an index with the default operator class is usable for pattern-matching queries in the C locale.

You can check your locale as follows (it is likely to be UTF8 rather than "C"):

postgres=> show lc_collate;
 lc_collate
-------------
 en_GB.UTF-8

• Open pgAdminIII,

• Select your database,

• Use the Magnifying Glass button, i.e. "Execute arbitrary SQL queries"

• Instead of running your query using the green triangle "Execute Query", choose the button two to the right of that - i.e. "Execute Query, write result to file".

• Choose your destination, then you can view arbitrary length text in your chosen file destination.

• You can also use psql which will show text, but will scroll if that's any use. Also, there would be the possibility of scripting your query output using different delimiters.