[postgis-users] GiST Index

[Matthew Pulis]

Thanks a lot Gregory

 

It did work as intended :)

 

EXPLAIN SELECT * from streets_relation where this_geom =
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D6094
1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724
E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000
000000000'

 

Gives :

 

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

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

 

Whilst, as you explained well - the && worked with the GiST

 

EXPLAIN SELECT * from streets_relation where this_geom &&
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D6094
1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724
E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000
000000000'

 

"Index Scan using "This Geom" on streets_relation  (cost=0.00..6.01 rows=1
width=758)"

"  Index Cond: (this_geom &&
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D6094
1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724
E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000
000000000'::geometry)"

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

 

Using the GiST I got a response in 30ms, whilst with the normal '=' I got a
response in 63ms, 100% difference :)

 

Thanks a lot for your help :)

 

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Gregory
Williamson
Sent: Wednesday, February 27, 2008 12:21 PM
To: PostGIS Users Discussion; PostGIS Users Discussion
Subject: RE: [postgis-users] GiST Index

 

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!

 

  _____  

I am using the free version of SPAMfighter for private users.
It has removed 23855 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/20080229/591d31bb/attachment.html>