Hi,
Also Index habe ich gesetzt auf gist.
Code:
CREATE TABLE gps_messwerte
(
id integer NOT NULL,
zeitstempel timestamp with time zone,
messung_id integer,
breite double precision,
laenge double precision,
hoehe double precision,
einheitbreite character(20),
einheitlaenge character(20),
einheithoehe character(20),
geom geometry,
CONSTRAINT gps_messwerte_pkey PRIMARY KEY (id),
CONSTRAINT gps_messwerte_messung_id_fkey FOREIGN KEY (messung_id)
REFERENCES messung (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 3),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (srid(geom) = 4326)
)
WITH (OIDS=TRUE);
ALTER TABLE gps_messwerte OWNER TO till;
-- Index: index_messwerte
-- DROP INDEX index_messwerte;
CREATE INDEX index_messwerte
ON gps_messwerte
USING gist
(geom); Die explain Analyse läuft noch. Wenn ich das Ergebnis habe Poste ich es noch.
So das Ergebniss:
Code:
"Nested Loop (cost=15.86..2529.35 rows=1 width=41) (actual time=2713.651..1428295.459 rows=1424 loops=1)"
" Join Filter: (g.messung_id <> m.id)"
" -> Hash Join (cost=15.86..2528.11 rows=1 width=45) (actual time=2713.606..1428033.129 rows=1424 loops=1)"
" Hash Cond: (g.messung_id = y.id)"
" Join Filter: (((box2d(g.geom))::geometry @ (box2d(t.geom))::geometry) AND within(transform(g.geom, 31467), buffer(transform(t.geom, 31467), 2::double precision)))"
" -> Seq Scan on gps_messwerte g (cost=0.00..2309.66 rows=29766 width=41) (actual time=4.034..181.663 rows=29766 loops=1)"
" -> Hash (cost=15.86..15.86 rows=1 width=71475) (actual time=4.948..4.948 rows=8 loops=1)"
" -> Hash Join (cost=1.22..15.86 rows=1 width=71475) (actual time=1.927..4.922 rows=8 loops=1)"
" Hash Cond: (x.id = y.track_id)"
" -> Nested Loop (cost=0.00..14.62 rows=1 width=71475) (actual time=1.816..4.781 rows=6 loops=1)"
" Join Filter: ((x.id <> t.id) AND ((box2d(x.geom))::geometry && (box2d(t.geom))::geometry))"
" -> Seq Scan on track t (cost=0.00..7.16 rows=1 width=71471) (actual time=0.041..0.046 rows=1 loops=1)"
" Filter: (id = 6)"
" -> Seq Scan on track x (cost=0.00..7.13 rows=13 width=71471) (actual time=0.016..0.050 rows=13 loops=1)"
" -> Hash (cost=1.18..1.18 rows=18 width=8) (actual time=0.082..0.082 rows=18 loops=1)"
" -> Seq Scan on messung y (cost=0.00..1.18 rows=18 width=8) (actual time=0.008..0.040 rows=18 loops=1)"
" -> Seq Scan on messung m (cost=0.00..1.23 rows=1 width=8) (actual time=0.131..0.135 rows=1 loops=1424)"
" Filter: (6 = track_id)"
"Total runtime: 1428299.402 ms"
Lesezeichen