[postgis-users] GiST Index

[Matthew Pulis]

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/637bfb08/attachment.html>