Re: How to force planner to use GiST index?

It helps a bit but not much.

pg=# EXPLAIN analyze Select count(a.objectid_1) as contains from
parcel_l a where
st_contains(st_geometry('polygon ((6221958 1949440, 6349378 1949440,
6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
a.shape) = 1 AND
(st_geometry('polygon ((6221958 1949440, 6349378 1949440, 6349378
2033808, 6221958 2033808, 6221958 1949440))'::cstring,3) ~ a.shape) =
't';
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------
Aggregate (cost=2829.68..2829.69 rows=1 width=4)
(actual time=43371.933..43371.934 rows=1 loops=1)
-> Bitmap Heap Scan on parcel_l a (cost=46.05..2829.67 rows=4
width=4) (actual time=217.830..43155.610 rows=180512 loops=1)
Filter: ((st_contains('ST_POLYGON'::st_geometry, shape) = 1)
AND ('ST_POLYGON'::st_geometry ~ shape))
-> Bitmap Index Scan on parcel_l_ind (cost=0.00..46.05
rows=756 width=0) (actual time=197.052..197.052 rows=180170 loops=1)
Index Cond: ('ST_POLYGON'::st_geometry ~ shape)
Total runtime: 43372.142 ms
(6 rows)

pgsde=# EXPLAIN analyze Select count(a.objectid_1) as contains from
parcel_l a where
st_contains(st_geometry('polygon ((6221958 1949440, 6349378 1949440,
6349378
2033808, 6221958 2033808, 6221958 1949440))'::cstring,3), a.shape) = 1
AND
(st_geometry('polygon ((6221958 1949440, 6349378 1949440, 6349378
2033808,
6221958 2033808, 6221958 1949440))'::cstring,3) && a.shape) = 't';

QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------
Aggregate (cost=20.48..20.49 rows=1 width=4)
(actual time=43898.908..43898.909 rows=1 loops=1)
-> Index Scan using parcel_l_ind on parcel_l a (cost=0.00..20.47
rows=1 width=4) (actual time=0.500..43680.894 rows=180170 loops=1)
Index Cond: ('ST_POLYGON'::st_geometry && shape)
Filter: ((st_contains('ST_POLYGON'::st_geometry, shape) = 1)
AND ('ST_POLYGON'::st_geometry && shape))
Total runtime: 43899.025 ms
(5 rows)

Thanks.

Ale

-----Original Message-----
From: Joe Healy [mailto:joe(at)omc-international(dot)com(dot)au]
Sent: Wednesday, March 07, 2007 4:42 PM
To: Ale Raza
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How to force planner to use GiST index?

araza(at)esri(dot)com wrote:
> Hi,
>
> I have a GiST index on st_geometry type (a user defined type). It
looks
> like index is not getting hit when I use some geometric operator. Here
> is the example of st_contains operator.
> <snip>
>
> How can I force or direct the planner to use the GiST index? Am I
> missing something?
>
>

For the index to be used you need to use an operator that can make use
of it. eg something like:

select parcel1.id, count(*) from parcel1, polygons where
contains(polygons.the_geom, parcel1.the_geom) and parcel1.geom &&
polygons.the_geom group by parcel1.id;

the && (inside bounding box) is able to use the gist index, whilst the
exact contains is not able to.

Hope that helps,

Joe