PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

7-Jul-2003 Issue: 33

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

Performance Tuning and the Annotated postgresql.conf
by Josh Berkus & Shridhar Daithankar
Early Release of postgresql.conf documentation 3-Jul-2003

As many of us have anticipated, Josh Berkus has, with Shridhar Daithankar's help put together two marvelous documents to help everyone sort out the details of the postgresql.conf file and the GUC (Global User Configuration) options.

The first document, Tuning PostgreSQL for Performance is a set of guidelines and questions to ask when preparing to tune an installation.

In the second document, The Annotated postgresql.conf, the elements of the postgresql.conf file are nicely organized into functional groups. Each item is documented with its range, default value, corresponding variable and -o option and is described in detail. Comments on most items also give you a clearer understanding of how and when to set these variables and what impact should be expected.

Both of these documents are required reading for anyone who plans to set up a production installation of PostgreSQL.

Contributors: Josh Berkus at, Shridhar Daithankar at
Searching without accents
[GENERAL] Accent insensitive search 1-Jul-2003

In order to search data ignoring accents, you must convert the data to ASCII.

A table containing the following data and selected with the usual LIKE case, then you would only get "Polo".

	FROM testtable 
	WHERE testfield like '%olo%';
If you selected from the table converting to ASCII you would get both values:
	FROM testtable 
	WHERE to_ascii(testfield,'LATIN1') LIKE '%olo%'
This solution has not been tested against a database with UNICODE encoded and one response warned that it may not work in that case.

Contributors: Alejandro Javier Pomeraniec apomeraniec at, Ian Barwick barwick at, Alvaro Herrera alvherre at
Multi-Column and Redundant Indexes
[GENERAL] Q: Structured index - which one runs faster? 22-May-2003

For a table with a three part key, the question was raised whether a functional index on the concatenated fields or a multi-part index might be faster. The indexes would be:

	create index threepart on table foo (col_one, col_two, col_three);
	create index catthree on table foo ( cattext3(col_one, col_two, col_three));
where cattext3() is a function which concatenates and returns its three arguments.

It is agreed that the three part index is better than the functional index for several reasons. Particularly if the "least non-unique" value is the leftmost in the index definition, the three part index will be faster.

The multi-part indexes in general are much more flexible as well. Partial index searches can use multi-part indexes. For example, if you were to search only on col_one the first part of the multi-part index would be used. The multi-part index would not be used for col_two without being preceded with col_one, but that would not work in the functional index either.

In a related question, if the table had the threepart index and an index on col_one alone, the index on col_one alone would be redundant. There is no need to create or store the second index when the first will do the work.

Contributors: Ernest E Vogelsinger ernest at, Tom Lane tgl at, Vivek Khera khera at, Bruno Wolff III bruno at, Manfred Koizar mkoi-pg at, scott.marlowe scott.marlowe at, Reece Hart rkh at gene.COM
What is $libdir
[GENERAL] Installation problem: can't find $libdir 2-Jul-2003

When installing languages, like plpgsql in a database, the system checks whether the directory $libdir exists. $libdir is a PostgreSQL representation of the directory specified as the path to the shared objects library when PostgreSQL was configured.

If you configured with --prefix=/local/pgsql73, for example, the string $libdir is expanded to /local/pgsql73/lib. $libdir's value is usually the result of the cascading defaults of --prefix=PREFIX --exec-prefix=EPREFIX and --libdir=dir.
PREFIX defaults to /usr/local/pgsql.
EPREFIX defaults to PREFIX.
$libdir defaults to EPREFIX/lib

The location of $libdir is also pertinent when writing C functions. When you define a C function in SQL, you must specify the location of the shared object (.so) of your function. You can use $libdir literally in the SQL definition to signify that your shared object is in your installation's definition of $libdir

	CREATE FUNCTION hello_cstr()
	RETURNS cstring
	AS '$libdir/'

It is not required that $libdir be used for C function shared objects. It is also possible to specify a full path name for development or for storing shared objects in an alternate directory.

Contributors: Rich Cullingford rculling at, Tom Lane, elein at
Two Installations on the same machine
[GENERAL] 2 different versions of postgres on the same system 1-Jul-2003

Having two separate installations on the same machines is fairly straightforward. With the beta of 7.4 coming up, many people will want to keep their existing installations and still be able to test a 7.4 installation.

There are three key elements in creating separate installations, location of the installation, location of the data directory and client access. The location of the installation is determined at configuration time by setting the prefix of the build to be in a specified directory instead of the default directory. The location of the data directory is determined with initdb. The client access is determined by setting the appropriate PG variables and ensuring the appropriate installation's bin is earlier in your PATH than the other one.

To build a 7.4 installation into /local/pgsql74 one would use:

	./configure --prefix=/local/pgsql74 ... 
When the install is performed all of the pieces of the installation go there.

Next you need to determine where the data directory for the installation will reside. My own preference is the directory PREFIX/data so that it is not in the database superuser's home directory and so that it will not be confused with any other installation. To set up the data directory, then, use:

	initdb -D /local/pgsql74/data 

In Issue 12 a convention for a user environment set up was described. It consisted of an environment file called pgenv which is sourced in the client environment to set the default PG environment variables. PG_INST is not a regular PG variable; it was created for convenience.


As described previously these variables can be set in a user's .profile or in /etc/profile to set the default installation on a system. Alternatively, two versions of the file can be made available, perhaps called pgenv73 and pgenv74 so that when switching from one to the other is only a matter of sourcing the correct file.

Note that this pgenv file changes your PATH. This is important, particularly if one of your installations is in /usr/local/bin. You must be sure that the executables for the installation of choice come before others on your path.

Contributors: elein at Madhavi Daroor madhavi at, Arguile arguile at, scott.marlowe scott.marlowe at

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW