[PostgreSQL/-GIS] Onderlinge afstanden tussen boxes bepalen

[licensed]

Ik breek mijn hoofd al een tijdje over dit probleem. Ik heb een PostgreSQL 8.4 database met daarin 1 tabel met ruim 4.000.000 records. Deze tabel ziet er als volgt uit:

SQL:

CREATE TABLE metadata (
  id serial NOT NULL,
  "value" text NOT NULL DEFAULT ''::text,
  segment_box box NOT NULL DEFAULT box(point(((-9223372036854775808)::bigint)::double precision, (1)::double precision), point((9223372036854775807::bigint)::double precision, ((-1))::double precision)),
  CONSTRAINT metadata_pk PRIMARY KEY (id)
)

CREATE INDEX metadata_segment_box_ix
  ON metadata
  USING gist
  (segment_box);

CREATE INDEX metadata_tag_value_ix
  ON metadata
  USING btree
  (value);

In de tabel worden segmenten in de tijd opgeslagen, gerepresenteerd als rechthoekige boxen. Deze segmenten worden geannoteerd (de kolom value).

Nu wil ik alle segmenten vinden die geannoteerd zijn met een bepaalde value en die niet verder dan een bepaalde afstand van elkaar liggen. De volgende query levert het gewenste antwoord:

SQL:

SELECT * FROM (SELECT * FROM metadata WHERE value='X') a, 
(SELECT * FROM metadata WHERE AND value='Y') b 
WHERE a.segment_box <-> b.segment_box <= 3000

Maar zoals je al had geraden is diet niet bijzonder efficient. Het cartesisch product van sub-queries a en b loopt nogal uit de hand. Is er een manier om dit efficienter te doen? In een willekeurige programmeertaal zou ik hier een sliding window aanpak kiezen. Kan dat ook in PostgreSQL? Bijvoorbeeld zoiets als:

SQL:

SELECT *, rank() OVER (
PARTITION BY "value" ORDER BY (segment_box[1])[0], (segment_box[0])[0]
) FROM metadata WHERE value='X' OR value='Y'

[licensed]

Eén van de oplossingsrichtingen die ik zag was een custom PostgreSQL functie. De onderstaande functie heb ik geprobeerd, maar deze levert geen snelheidswinst op.

SQL:

CREATE OR REPLACE FUNCTION within_window(size bigint DEFAULT 0)
  RETURNS setof metadata AS
$BODY$DECLARE
  segment RECORD;
  neighbour RECORD;
  newwindow box;
BEGIN
  FOR segment IN (
    SELECT * FROM metadata WHERE value='X' OR value='Y' 
      ORDER BY (segment_box[1])[0], (segment_box[0])[0]
  ) LOOP
    newwindow := box(segment.segment_box[0], 
      point((((segment.segment_box[1])[0]) + size), (segment.segment_box[1])[1]));
    FOR neighbour IN (
      SELECT DISTINCT ON (metadata_id) * FROM metadata WHERE value='X' OR value='Y') 
        AND segment_box &< newwindow
        AND segment_box &> newwindow 
    ) LOOP
      RETURN NEXT neighbour;
    END LOOP;
  END LOOP;
END;$BODY$
  LANGUAGE plpgsql;

Heeft er iemand andere ideeën?