Re: How to force planner to use GiST index? - Mailing list pgsql-general

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@omc-international.com.au]
Sent: Wednesday, March 07, 2007 4:42 PM
To: Ale Raza
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to force planner to use GiST index?

araza@esri.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