[postgis-users] GIST index speed

[Mark Cave-Ayland]

Steve Kondik wrote:

> Adding force_2d speeds this up immensely.  Not sure I understand why
> this is necessary, but I'm still getting used to PostGIS.
> 
> 
> health_central_22=> explain analyze select id,name from geography where
> type='Z' and centroid && (select force_2d(geometry) from geography where
> id=69495);
>                                                                 QUERY
> PLAN
> -
> ------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using idx_geography_centroid on geography  (cost=8.29..16.57
> rows=1 width=14) (actual time=0.263..125.338 rows=29687 loops=1)
>    Index Cond: (centroid && $0)
>    Filter: ((centroid && $0) AND (type = 'Z'::bpchar))
>    InitPlan
>      ->  Index Scan using geography_pkey on geography  (cost=0.00..8.29
> rows=1 width=4441) (actual time=0.158..0.161 rows=1 loops=1)
>            Index Cond: (id = 69495)
>  Total runtime: 171.229 ms
> (7 rows)

*blinks* this is definitely a bug somewhere - there is no way that 
adding a function wrapper to a constant should make the query several 
orders of magnitude quicker :(

I'll leave the typmod modifications for a bit to dig into this.


ATB,

Mark.

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