Range Queries

Important

Starting with InterMine 1.4, bioseg is no longer required if you are using a PostgreSQL version higher than 9.1.

PostgreSQL 9.2 and higher

InterMine now can perform range queries on location table taking advantage of PostgreSQL built in int4range type.

The int4range type requires PostgreSQL 9.2 and queries perform best with 9.3.

If range types are not available (PostgreSQL 9.1) then queries will revert to bioseg if available or vanilla queries if not.

New installations of PostgreSQL may still need to install gist for the indexes to work.

Indexing

Performance is improved by creating an index of int4range(start, end) on the location table.

This is achieved in the post-process phase of the build of an InterMine (see Tutorial).

Please use the create-location-range-index post-process, which should replace create-bioseg-location-index in the project.xml file in your YOUR_MINE directory.

The create-overlap-view task will also detect whether built-in ranges are available and use them instead of bioseg for the overlappingfeatures view.

PostgreSQL 9.1

For PostgreSQL 9.1 bioseg is still required. Here is how to install it.

Prerequisites

Contrib software

debian	apt-get install postgresql-contrib-9.1
fedora	yum install postgresql-contrib

Header files

debian	apt-get install postgresql-server-dev-9.1
fedora	yum install postgresql-devel

pg_config

debian

apt-get install libpq-dev

Installation

1. Download the bioseg tar from http://www.bioinformatics.org/bioseg/wiki/.
2. Untar the file, change to the created directory and run these commands

$ make USE_PGXS=t clean
$ make USE_PGXS=t
$ make USE_PGXS=t install

Create bioseg Type

You need to create the bioseg type in each database that is going to use it. If you create it in the template1, then all newly-created databases will have the bioseg type.

Warning

DO NOT install bioseg to the template0 or postgres databases - they should never be altered.

Change directory to the postgres contrib directory

debian	/usr/share/postgresql/9.1/contrib
fedora	/usr/share/pgsql/contrib

For each database, type:

# in the contrib directory
$ psql (database) <bioseg.sql

Gist

We also need to create the default gist operators for Bioseg too, in order to have normal types in multi-column indexes.

PostgreSQL 8.x users

For each database, type:

# in the contrib directory
$ psql (database) <btree_gist.sql

PostgreSQL 9.x users

See http://www.postgresql.org/docs/9.1/static/btree-gist.html. Run the command in the template1 database:

$ CREATE EXTENSION btree_gist;