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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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!
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
