[postgis-users] GiST Index

[Mark Leslie]

Matthew,
There are a number of parameters that go into a decision to use an 
index, as well as the stats on the table.  Make sure the stats are up to 
date with a VACUUM ANALYSE.
The first step would be to find out if an index scan would actually be 
faster.  You can disable sequence scans using:
	SET enable_seqscan = off
for each connection.  Note that this is a poor idea for purposes other 
than performance tuning.  If it turns out that an index scan is actually 
faster in your case, you'll need to tune PostgreSQL to make it realise this.

Mark Cave-Ayland has posted a great tuning guide to the list a while 
back.  It can be found at:
http://postgis.refractions.net/pipermail/postgis-users/2006-March/011539.html

-- 
Mark Leslie
Geospatial Software Architect
LISAsoft Pty Ltd
+61 (0)2 8570 5050

Commercial Support for Geospatial Open Source Software
http://www.lisasoft.com/LISAsoft/SupportedProducts.html


Matthew Pulis wrote:
> 
> 
>  
> 
> 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 = 
> '01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D60941B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000000000000' 
> 
> 
>  
> 
> Gave me :
> 
>  
> 
> "Seq Scan on streets_relation  (cost=0.00..4666.80 rows=45 width=758)"
> 
> "  Filter: (this_geom = 
> '01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D60941B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000000000000'::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!
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users