[postgis-users] GiST Index

[Gregory Williamson]

Matthew --

Try running 
  ANALYZE streets_relation;

This provides the statistics to the planner ... 

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.

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.

But my guess is you need to provide statistics for the planner and then use the proper function call to invoke the GIST index.

HTH,

Greg Williamson
Senior DBA
DigitalGlobe Inc.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of Matthew Pulis
Sent: Wed 2/27/2008 3:49 AM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] GiST Index
 
 

Hei all :)

 

I have a table where it holds 2 Geometry type columns, this_geom, and
last_geom.

 

I will be using this table as a reference table where I will either pass
thisgid or this_geom and would like all the other data extracted.

 

However an EXPLAIN SELECT on a where this_geom =
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D6094
1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724
E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000
000000000'

 

Gave me :

 

"Seq Scan on streets_relation  (cost=0.00..4666.80 rows=45 width=758)"

"  Filter: (this_geom =
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D6094
1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724
E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000
000000000'::geometry)"

 

Why rows does not equal to 1 but to 45?

 

Does a GiST index in groups of 45? Can it be minimised to less groups
please?

 

This is the CREATE Table SQL text used :

 

-- Table: streets_relation

 

-- DROP TABLE streets_relation;

 

CREATE TABLE streets_relation

(

  last_geom geometry,

  x_last double precision,

  y_last double precision,

  z_last double precision,

  lastid integer NOT NULL,

  stseg bigint,

  lastname character varying(80),

  this_geom geometry,

  x_this double precision,

  y_this double precision,

  z_this double precision,

  x_new double precision,

  y_new double precision,

  z_new double precision,

  thisname character varying(80),

  thisgid integer NOT NULL,

  CONSTRAINT "Primarky Key GID" PRIMARY KEY (lastid, thisgid)

) 

WITHOUT OIDS;

ALTER TABLE streets_relation OWNER TO yancho;

 

 

-- Index: "This Geom"

 

-- DROP INDEX "This Geom";

 

CREATE INDEX "This Geom"

  ON streets_relation

  USING gist

  (this_geom);

 

 

When I did where thisgid = 10 this is the result I got :

 

"Index Scan using "Primarky Key GID" on streets_relation  (cost=0.00..284.19
rows=1 width=758)"

"  Index Cond: (thisgid = 10)"

 

Any idea on what I can do to improve the performance please?

 

Thanks and regards

 

Matthew

 

  _____  

I am using the free version of SPAMfighter for private users.
It has removed 23646 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter <http://www.spamfighter.com/len>  for free now!


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