[postgis-users] GiST Index

[Mark Cave-Ayland]

On Wednesday 27 February 2008 10:49:07 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 =
> '01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D609
>4
> 1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA712472
>4
> E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB5540000000
>0 000000000'
>
>
>
> Gave me :
>
>
>
> "Seq Scan on streets_relation  (cost=0.00..4666.80 rows=45 width=758)"
>
> "  Filter: (this_geom =
> '01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D609
>4
> 1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA712472
>4
> E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB5540000000
>0 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


Hi Matthew,

EXPLAIN only provides an *estimate* of how many rows the query will return 
i.e. it is based on the table statistics gathered during the last ANALYZE. If 
the estimate is very different from the actual number of rows, you may need 
to re-ANALYZE your table.

Note that if you want to compare the estimates with the actual numbers 
returned, you need to use EXPLAIN ANALYZE rather than just EXPLAIN.


HTH,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063