[postgis-users] question on gist performance

[Stefan Zweig]

hi list,

i have noticed a strange thing while selecting geometries from a table and using the && operator (gist):

i have a table which holds all countrys of the world (somewhat more than 200) an their boundaries in column the_geom
the boundaries have a quite good resolution, so the total amount of points is huge.
for better performance (if low resolution is needed) i created a second column 

the_geom_1 as:
UPDATE table SET the_geom_1=simplify(the_geom,0.01)

which results in a less total amount of points compared to the original data.

i have set up a gist index on both columns

and now the strange thing:
SELECT name FROM _g2965 WHERE the_geom && SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);
takes 297ms

SELECT name FROM _g2965 WHERE the_geom_1 && SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);
takes 15ms

actually i would have thought that the query using the gist index should take the same time on both columns, because gist index strategy is comparing bounding boxes of the geometries with the bounding box of my query, isn't it?
can anybody give me a hint why there is such a massive time difference when selecting from different columns?


thanks in advance, stefan


-- Table: _g2965

-- DROP TABLE _g2965;

CREATE TABLE _g2965
(
  gid serial NOT NULL,
  the_geom geometry NOT NULL,
  country integer,
  searchname character varying(255),
  name character varying(255),
  providerid character varying(255),
  geocode character varying(255),
  the_geom_1 geometry NOT NULL,
  the_geom_2 geometry NOT NULL,
  the_geom_3 geometry NOT NULL,
  bbox_sven geometry,
  CONSTRAINT _g2965_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_bbox_sven CHECK (ndims(bbox_sven) = 2),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_dims_the_geom_1 CHECK (ndims(the_geom_1) = 2),
  CONSTRAINT enforce_dims_the_geom_2 CHECK (ndims(the_geom_2) = 2),
  CONSTRAINT enforce_dims_the_geom_3 CHECK (ndims(the_geom_3) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR geometrytype(the_geom) = 'GEOMETRYCOLLECTION'::text OR geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_geotype_the_geom_1 CHECK (geometrytype(the_geom_1) = 'MULTIPOLYGON'::text OR geometrytype(the_geom_1) = 'POLYGON'::text OR geometrytype(the_geom_1) = 'GEOMETRYCOLLECTION'::text OR the_geom_1 IS NULL),
  CONSTRAINT enforce_geotype_the_geom_2 CHECK (geometrytype(the_geom_2) = 'MULTIPOLYGON'::text OR geometrytype(the_geom_2) = 'POLYGON'::text OR geometrytype(the_geom_2) = 'GEOMETRYCOLLECTION'::text OR the_geom_2 IS NULL),
  CONSTRAINT enforce_geotype_the_geom_3 CHECK (geometrytype(the_geom_3) = 'MULTIPOLYGON'::text OR geometrytype(the_geom_3) = 'POLYGON'::text OR geometrytype(the_geom_3) = 'GEOMETRYCOLLECTION'::text OR the_geom_3 IS NULL),
  CONSTRAINT enforce_srid_bbox_sven CHECK (srid(bbox_sven) = 4326),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 4326),
  CONSTRAINT enforce_srid_the_geom_1 CHECK (srid(the_geom_1) = 4326),
  CONSTRAINT enforce_srid_the_geom_2 CHECK (srid(the_geom_2) = 4326),
  CONSTRAINT enforce_srid_the_geom_3 CHECK (srid(the_geom_3) = 4326)
) 
WITHOUT OIDS;
ALTER TABLE _g2965 OWNER TO mapchart;


-- Index: _g2965_geocode_key

-- DROP INDEX _g2965_geocode_key;

CREATE UNIQUE INDEX _g2965_geocode_key
  ON _g2965
  USING btree
  (geocode);

-- Index: _g2965_searchname_idx

-- DROP INDEX _g2965_searchname_idx;

CREATE INDEX _g2965_searchname_idx
  ON _g2965
  USING btree
  (searchname);

-- Index: _g2965_the_geom_1_gist

-- DROP INDEX _g2965_the_geom_1_gist;

CREATE INDEX _g2965_the_geom_1_gist
  ON _g2965
  USING gist
  (the_geom_1);

-- Index: _g2965_the_geom_2_gist

-- DROP INDEX _g2965_the_geom_2_gist;

CREATE INDEX _g2965_the_geom_2_gist
  ON _g2965
  USING gist
  (the_geom_2);

-- Index: _g2965_the_geom_3_gist

-- DROP INDEX _g2965_the_geom_3_gist;

CREATE INDEX _g2965_the_geom_3_gist
  ON _g2965
  USING gist
  (the_geom_3);

-- Index: _g2965_the_geom_gist

-- DROP INDEX _g2965_the_geom_gist;

CREATE INDEX _g2965_the_geom_gist
  ON _g2965
  USING gist
  (the_geom);
ALTER TABLE _g2965 CLUSTER ON _g2965_the_geom_gist;




_______________________________________________________________________
Jetzt neu! Schützen Sie Ihren PC mit McAfee und WEB.DE. 3 Monate
kostenlos testen. http://www.pc-sicherheit.web.de/startseite/?mc=022220