Re: How to force planner to use GiST index?

On Wed, Mar 07, 2007 at 04:00:14PM -0800, 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.

I don't know whether you noticed, but a function call can never use an
index like that. Index scans *only* work with operators, not with
functions.

> 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;

So no matter what you do, this can never be an index scan, because
there's no operator postgres can apply to the index...

If you really want to use function names also, I beleive you can make
in inline SQL function to convert from function form to operator form.
The query planner will only look to the index if there's an operator.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.