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

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.

EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
a
Where st_contains(st_geometry('polygon ((6221958 1949440, 6349378
1949440,
6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
a.shape) = 1;

                                                        QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------
 Aggregate  (cost=79132.24..79132.25 rows=1 width=4)
(actual time=49614.399..49614.400 rows=1 loops=1)
   ->  Seq Scan on parcel_l a  (cost=0.00..79122.79 rows=3778 width=4)
(actual time=2.343..49388.591 rows=184750 loops=1)
         Filter: (st_contains('ST_POLYGON'::st_geometry, shape) = 1)
 Total runtime: 49614.479 ms

The time 49614 ms is too high for 184k rows. I have tried various
configuration parameters as mentioned in section 17.6 (Query planning)
of PostgreSQL 8.2.1 Documentation. For example setting
Set enable_seqscan = off;
Set random_page_cost = 10; etc.,

Changing these parameters did not improve performance.

If I call one of the operators (~) of GiST operator class, then it takes
1015 ms for 184k rows.

EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
a where
(st_geometry('polygon ((6221958 1949440, 6349378 1949440, 6349378
2033808, 6221958 2033808, 6221958 1949440))'::cstring,3) ~ a.shape) =
't';
------------------------------------------------------------------------
------------------------------------------
 Aggregate  (cost=2827.78..2827.79 rows=1 width=4)
(actual time=1015.025..1015.026 rows=1 loops=1)
   ->  Bitmap Heap Scan on parcel_l a  (cost=46.05..2825.89 rows=756
width=4)
(actual time=213.914..876.122 rows=180512 loops=1)
         Filter: ('ST_POLYGON'::st_geometry ~ shape)
         ->  Bitmap Index Scan on parcel_l_ind  (cost=0.00..46.05
rows=756 width=0)
(actual time=202.629..202.629 rows=180170 loops=1)
               Index Cond: ('ST_POLYGON'::st_geometry ~ shape)
 Total runtime: 1015.223 ms

Here is information about table, type, index and rows in the table.

pg=# SELECT relname, relkind, reltuples, relpages FROM pg_class
WHERE relname LIKE 'parcel_l%';
  relname         | relkind | reltuples | relpages
--------------------+---------+-----------+----------
 parcel_l_pkey | i      |    755653 |    1665
 parcel_l          | r      |    755653 |    67788
 parcel_l_ind   | i       |    755653 |     9582
(3 rows)


pg=# \d parcel_l
                 Table "sde.parcel_l"
 olumn       |            Type             | Modifiers
---------------+-----------------------------+-----------
 objectid_1 | integer                     | not null
 area          | numeric(38,8)          |
.....
fid_len        | numeric(38,8)          |
shape         | st_geometry            |
Indexes:
    "parcel_l_pkey" PRIMARY KEY, btree (objectid_1)
    "parcel_l_ind" gist (shape)



How can I force or direct the planner to use the GiST index? Am I
missing something?

Thanks.

Ale Raza.