[postgis-users] Re:touches problem

[Gregory Williamson]

And now for the ultimate fun, try sending the results of 

EXPLAIN ANALYZE <query>;

It will take longer to run than the real query because it has timing info it tracks and those calls slow it down, but it provides an account of how the planner approached the problem. If the issues are with PostgreSQL issues and not the GIS extension, the postgres performance list might be a better place to post. (but try here first anyway).

Take care in pasting the results -- they can be messy and hard to read.

If your SQL does an update/delete/insert and you don't want it to change your data, be sure to wrap the EXPLAIN ANALYZE in a BEGIN; ROLLBACK; transaction since otherwise the underlying data would be changed. If the query is a read only then it doesn't matter.

The user manual has some more information on this very useful command.

And make sure you run ANALYZE after creating indexes and after large changes to the underlying data -- it only collects information on indexed columns and if they change the statisitics need to reflect this.

HTH,

Greg W.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of caij at lreis.ac.cn
Sent: Thu 11/15/2007 6:55 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Re:touches problem
 
Thanks for Greg Williamson's answer.I have analyzed my quanbj table.And the analysis result is below:
INFO:  analyzing "public.quanbj1_10000_6_9_fullscreen"
INFO:  "quanbj1_10000_6_9_fullscreen": scanned 3000 of 45260 pages, containing 18570 live rows and 8496 dead rows; 3000 rows in sample, 280159 estimated total rows
And my table DDL is:
CREATE TABLE quanbj1_10000_6_9_fullscreen
(
  ogc_fid int4,
  wkb_geometry geometry,
  quanbeijin numeric(9),
  quanbeij_1 numeric(9),
  id numeric(9),
  "ͼ°ßºÅ" char(7),
  dlm char(4),
  "ȨÊô´úÂë" char(12),
  "ȨÊôÃû³Æ" char(30),
  "ȨÊôÐÔÖÊ" char(3),
  "×ùÂä´úÂë" char(15),
  isadd int2,
  isleaf int2,
  "level" int4,
  lidu float4,
  child_level int2,
  ischanged int2,
  prechanged int2 DEFAULT 0
) 
WITHOUT OIDS;
ALTER TABLE quanbj1_10000_6_9_fullscreen OWNER TO caij;


-- Index: qb_ischanged

-- DROP INDEX qb_ischanged;

CREATE INDEX qb_ischanged
  ON quanbj1_10000_6_9_fullscreen
  USING btree
  (ischanged);

-- Index: qb_leve

-- DROP INDEX qb_leve;

CREATE INDEX qb_leve
  ON quanbj1_10000_6_9_fullscreen
  USING btree
  ("level");

-- Index: qb_lid

-- DROP INDEX qb_lid;

CREATE INDEX qb_lid
  ON quanbj1_10000_6_9_fullscreen
  USING btree
  (lidu);

-- Index: qb_ogc_f

-- DROP INDEX qb_ogc_f;

CREATE INDEX qb_ogc_f
  ON quanbj1_10000_6_9_fullscreen
  USING btree
  (ogc_fid);

-- Index: qb_prechanged

-- DROP INDEX qb_prechanged;

CREATE INDEX qb_prechanged
  ON quanbj1_10000_6_9_fullscreen
  USING btree
  (prechanged);

-- Index: qb_wkb_geometry

-- DROP INDEX qb_wkb_geometry;

CREATE INDEX qb_wkb_geometry
  ON quanbj1_10000_6_9_fullscreen
  USING gist
  (wkb_geometry);

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071115/bfcb3f78/attachment.html>