-- -- functions operating on featureloc ranges -- -- create a point CREATE OR REPLACE FUNCTION create_point (bigint, bigint) RETURNS point AS 'SELECT point ($1, $2)' LANGUAGE 'sql'; -- create a range box -- (make this immutable so we can index it) CREATE OR REPLACE FUNCTION boxrange (bigint, bigint) RETURNS box AS 'SELECT box (create_point(0, $1), create_point($2,500000000))' LANGUAGE 'sql' IMMUTABLE; -- create a query box CREATE OR REPLACE FUNCTION boxquery (bigint, bigint) RETURNS box AS 'SELECT box (create_point($1, $2), create_point($1, $2))' LANGUAGE 'sql' IMMUTABLE; --functional index that depends on the above functions CREATE INDEX binloc_boxrange ON featureloc USING GIST (boxrange(fmin, fmax)); CREATE OR REPLACE FUNCTION featureloc_slice(bigint, bigint) RETURNS setof featureloc AS 'SELECT * from featureloc where boxquery($1, $2) @ boxrange(fmin,fmax)' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION featureloc_slice(varchar, bigint, bigint) RETURNS setof featureloc AS 'SELECT featureloc.* FROM featureloc INNER JOIN feature AS srcf ON (srcf.feature_id = featureloc.srcfeature_id) WHERE boxquery($2, $3) @ boxrange(fmin,fmax) AND srcf.name = $1 ' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION featureloc_slice(bigint, bigint, bigint) RETURNS setof featureloc AS 'SELECT * FROM featureloc WHERE boxquery($2, $3) @ boxrange(fmin,fmax) AND srcfeature_id = $1 ' LANGUAGE 'sql'; -- can we not just do these as views? CREATE OR REPLACE FUNCTION feature_overlaps(bigint) RETURNS setof feature AS 'SELECT feature.* FROM feature INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) INNER JOIN featureloc AS y ON (y.feature_id = $1) WHERE x.srcfeature_id = y.srcfeature_id AND ( x.fmax >= y.fmin AND x.fmin <= y.fmax ) ' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION feature_disjoint_from(bigint) RETURNS setof feature AS 'SELECT feature.* FROM feature INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) INNER JOIN featureloc AS y ON (y.feature_id = $1) WHERE x.srcfeature_id = y.srcfeature_id AND ( x.fmax < y.fmin OR x.fmin > y.fmax ) ' LANGUAGE 'sql'; --Evolution of the methods found in range.plpgsql (C. Pommier) --Goal : increase performances of segment fetching -- Implies to optimise featureloc_slice --Background : The existing featureloc_slice uses uses a spatial rtree index. The spatial objects used are a boxrange ((0,fmin), (fmax,500000000)) and a boxquery ((fmin,fmax),(fmin,fmax)) . The boxranges are indexed. -- To speed up things (for gbrowse) featureloc_slice has been overiden to filter simultaneously on the boxrange and the srcfeature_id. This gives good results. -- The goal here is to push this logic further and to include the srcfeature_id filter directly into the boxrange object. We propose to consider the following boxs : -- boxrange : ((srcfeature_id,fmin),(srcfeature_id,fmax)) -- boxquery : ((srcfeature_id,fmin),(srcfeature_id,fmax)) CREATE OR REPLACE FUNCTION boxrange (bigint, bigint, bigint) RETURNS box AS 'SELECT box (create_point($1, $2), create_point($1,$3))' LANGUAGE 'sql' IMMUTABLE; -- create a query box CREATE OR REPLACE FUNCTION boxquery (bigint, bigint, bigint) RETURNS box AS 'SELECT box (create_point($1, $2), create_point($1, $3))' LANGUAGE 'sql' IMMUTABLE; CREATE INDEX binloc_boxrange_src ON featureloc USING GIST (boxrange(srcfeature_id,fmin, fmax)); CREATE OR REPLACE FUNCTION featureloc_slice(bigint, bigint, bigint) RETURNS setof featureloc AS 'SELECT * FROM featureloc WHERE boxquery($1, $2, $3) && boxrange(srcfeature_id,fmin,fmax)' LANGUAGE 'sql';