<!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] GiST Index</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Matthew --<BR>
<BR>
Try running<BR>
ANALYZE streets_relation;<BR>
<BR>
This provides the statistics to the planner ...<BR>
<BR>
Use the ST_ functions (or && in older nomenclature) to invoke the spatial index. Typically using an intersection or some such to get things in an area. A GIST index is not a B-tree index and requires the && operator to be used (the ST_ functions in postGIS are wrappers that provide the real spatial operator). "=" won't do that.<BR>
<BR>
Finally, if we have a very small table, or are asking for a *lot* of rows, PostgreSQL will probably do a sequential scan anyway as it is faster.<BR>
<BR>
But my guess is you need to provide statistics for the planner and then use the proper function call to invoke the GIST index.<BR>
<BR>
HTH,<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
DigitalGlobe Inc.<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Matthew Pulis<BR>
Sent: Wed 2/27/2008 3:49 AM<BR>
To: 'PostGIS Users Discussion'<BR>
Subject: [postgis-users] GiST Index<BR>
<BR>
<BR>
<BR>
Hei all :)<BR>
<BR>
<BR>
<BR>
I have a table where it holds 2 Geometry type columns, this_geom, and<BR>
last_geom.<BR>
<BR>
<BR>
<BR>
I will be using this table as a reference table where I will either pass<BR>
thisgid or this_geom and would like all the other data extracted.<BR>
<BR>
<BR>
<BR>
However an EXPLAIN SELECT on a where this_geom =<BR>
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D6094<BR>
1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724<BR>
E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000<BR>
000000000'<BR>
<BR>
<BR>
<BR>
Gave me :<BR>
<BR>
<BR>
<BR>
"Seq Scan on streets_relation (cost=0.00..4666.80 rows=45 width=758)"<BR>
<BR>
" Filter: (this_geom =<BR>
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D6094<BR>
1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724<BR>
E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000<BR>
000000000'::geometry)"<BR>
<BR>
<BR>
<BR>
Why rows does not equal to 1 but to 45?<BR>
<BR>
<BR>
<BR>
Does a GiST index in groups of 45? Can it be minimised to less groups<BR>
please?<BR>
<BR>
<BR>
<BR>
This is the CREATE Table SQL text used :<BR>
<BR>
<BR>
<BR>
-- Table: streets_relation<BR>
<BR>
<BR>
<BR>
-- DROP TABLE streets_relation;<BR>
<BR>
<BR>
<BR>
CREATE TABLE streets_relation<BR>
<BR>
(<BR>
<BR>
last_geom geometry,<BR>
<BR>
x_last double precision,<BR>
<BR>
y_last double precision,<BR>
<BR>
z_last double precision,<BR>
<BR>
lastid integer NOT NULL,<BR>
<BR>
stseg bigint,<BR>
<BR>
lastname character varying(80),<BR>
<BR>
this_geom geometry,<BR>
<BR>
x_this double precision,<BR>
<BR>
y_this double precision,<BR>
<BR>
z_this double precision,<BR>
<BR>
x_new double precision,<BR>
<BR>
y_new double precision,<BR>
<BR>
z_new double precision,<BR>
<BR>
thisname character varying(80),<BR>
<BR>
thisgid integer NOT NULL,<BR>
<BR>
CONSTRAINT "Primarky Key GID" PRIMARY KEY (lastid, thisgid)<BR>
<BR>
)<BR>
<BR>
WITHOUT OIDS;<BR>
<BR>
ALTER TABLE streets_relation OWNER TO yancho;<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
-- Index: "This Geom"<BR>
<BR>
<BR>
<BR>
-- DROP INDEX "This Geom";<BR>
<BR>
<BR>
<BR>
CREATE INDEX "This Geom"<BR>
<BR>
ON streets_relation<BR>
<BR>
USING gist<BR>
<BR>
(this_geom);<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
When I did where thisgid = 10 this is the result I got :<BR>
<BR>
<BR>
<BR>
"Index Scan using "Primarky Key GID" on streets_relation (cost=0.00..284.19<BR>
rows=1 width=758)"<BR>
<BR>
" Index Cond: (thisgid = 10)"<BR>
<BR>
<BR>
<BR>
Any idea on what I can do to improve the performance please?<BR>
<BR>
<BR>
<BR>
Thanks and regards<BR>
<BR>
<BR>
<BR>
Matthew<BR>
<BR>
<BR>
<BR>
_____ <BR>
<BR>
I am using the free version of SPAMfighter for private users.<BR>
It has removed 23646 spam emails to date.<BR>
Paying users do not have this message in their emails.<BR>
Try SPAMfighter <<A HREF="http://www.spamfighter.com/len">http://www.spamfighter.com/len</A>> for free now!<BR>
<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>