<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] Re:touches problem</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>And now for the ultimate fun, try sending the results of<BR>
<BR>
EXPLAIN ANALYZE <query>;<BR>
<BR>
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).<BR>
<BR>
Take care in pasting the results -- they can be messy and hard to read.<BR>
<BR>
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.<BR>
<BR>
The user manual has some more information on this very useful command.<BR>
<BR>
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.<BR>
<BR>
HTH,<BR>
<BR>
Greg W.<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of caij@lreis.ac.cn<BR>
Sent: Thu 11/15/2007 6:55 PM<BR>
To: postgis-users@postgis.refractions.net<BR>
Subject: [postgis-users] Re:touches problem<BR>
<BR>
Thanks for Greg Williamson's answer.I have analyzed my quanbj table.And the analysis result is below:<BR>
INFO: analyzing "public.quanbj1_10000_6_9_fullscreen"<BR>
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<BR>
And my table DDL is:<BR>
CREATE TABLE quanbj1_10000_6_9_fullscreen<BR>
(<BR>
ogc_fid int4,<BR>
wkb_geometry geometry,<BR>
quanbeijin numeric(9),<BR>
quanbeij_1 numeric(9),<BR>
id numeric(9),<BR>
"图斑号" char(7),<BR>
dlm char(4),<BR>
"权属代码" char(12),<BR>
"权属名称" char(30),<BR>
"权属性质" char(3),<BR>
"座落代码" char(15),<BR>
isadd int2,<BR>
isleaf int2,<BR>
"level" int4,<BR>
lidu float4,<BR>
child_level int2,<BR>
ischanged int2,<BR>
prechanged int2 DEFAULT 0<BR>
)<BR>
WITHOUT OIDS;<BR>
ALTER TABLE quanbj1_10000_6_9_fullscreen OWNER TO caij;<BR>
<BR>
<BR>
-- Index: qb_ischanged<BR>
<BR>
-- DROP INDEX qb_ischanged;<BR>
<BR>
CREATE INDEX qb_ischanged<BR>
ON quanbj1_10000_6_9_fullscreen<BR>
USING btree<BR>
(ischanged);<BR>
<BR>
-- Index: qb_leve<BR>
<BR>
-- DROP INDEX qb_leve;<BR>
<BR>
CREATE INDEX qb_leve<BR>
ON quanbj1_10000_6_9_fullscreen<BR>
USING btree<BR>
("level");<BR>
<BR>
-- Index: qb_lid<BR>
<BR>
-- DROP INDEX qb_lid;<BR>
<BR>
CREATE INDEX qb_lid<BR>
ON quanbj1_10000_6_9_fullscreen<BR>
USING btree<BR>
(lidu);<BR>
<BR>
-- Index: qb_ogc_f<BR>
<BR>
-- DROP INDEX qb_ogc_f;<BR>
<BR>
CREATE INDEX qb_ogc_f<BR>
ON quanbj1_10000_6_9_fullscreen<BR>
USING btree<BR>
(ogc_fid);<BR>
<BR>
-- Index: qb_prechanged<BR>
<BR>
-- DROP INDEX qb_prechanged;<BR>
<BR>
CREATE INDEX qb_prechanged<BR>
ON quanbj1_10000_6_9_fullscreen<BR>
USING btree<BR>
(prechanged);<BR>
<BR>
-- Index: qb_wkb_geometry<BR>
<BR>
-- DROP INDEX qb_wkb_geometry;<BR>
<BR>
CREATE INDEX qb_wkb_geometry<BR>
ON quanbj1_10000_6_9_fullscreen<BR>
USING gist<BR>
(wkb_geometry);<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>