Re: How to force planner to use GiST index?

"If you really want to use function names also, I beleive you can make
in inline SQL function ... if there's an operator."

Correct, this is what I am doing now.

Ale.

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog(at)svana(dot)org]
Sent: Thursday, March 08, 2007 10:35 AM
To: Ale Raza
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How to force planner to use GiST index?

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

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability
to litigate.