passing parameters to CREATE INDEX

Hi there,

it's desirable to be able to pass parameters to CREATE INDEX for
GiST indices. Does SQL standard has something about that so we could
implement it for 8.2 ?

Example from real life project - performance of tsearch2 could be
greatly improved if decrease signature size in gistidx.h, which is
currently hardcoded and one should compile and install tsearch2 into
differnet location and use it for specific database. It's impossible
to have different signature length for different fts indices because
we have no possibility to pass parameters to CREATE INDEX command.

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Oleg Bartunov wrote:

> Hi there,
>
> it's desirable to be able to pass parameters to CREATE INDEX for
> GiST indices. Does SQL standard has something about that so we could
> implement it for 8.2 ?

According to the docs:

"CREATE INDEX is a PostgreSQL language extension. There are no
provisions for indexes in the SQL standard."

So we could do whatever we like.

cheers

andrew

On Tue, Sep 20, 2005 at 11:26:26PM +0400, Oleg Bartunov wrote:
> it's desirable to be able to pass parameters to CREATE INDEX for
> GiST indices. Does SQL standard has something about that so we could
> implement it for 8.2 ?

As has been pointed out, INDEXes arn't in the SQL spec at all, so you
can do just about anything.

> Example from real life project - performance of tsearch2 could be
> greatly improved if decrease signature size in gistidx.h, which is
> currently hardcoded and one should compile and install tsearch2 into
> differnet location and use it for specific database. It's impossible
> to have different signature length for different fts indices because
> we have no possibility to pass parameters to CREATE INDEX command.

What syntax were you envisioning? Does this value just need to be
passed to GiST at the creation of the the index, or does it actually
need to remembered by the backend and passed each call? At the moment
there is some discussion on changes to the index interface so now is
the time to ask for what you want...

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> What syntax were you envisioning? Does this value just need to be
> passed to GiST at the creation of the the index, or does it actually
> need to remembered by the backend and passed each call?

I should think that the index ought to remember any such info for itself
(eg, in the metapage). Putting it somewhere else, such as the pg_index
row for the index, would force a one-size-fits-all approach.

regards, tom lane

On Tue, 20 Sep 2005, Martijn van Oosterhout wrote:

> On Tue, Sep 20, 2005 at 11:26:26PM +0400, Oleg Bartunov wrote:
>> it's desirable to be able to pass parameters to CREATE INDEX for
>> GiST indices. Does SQL standard has something about that so we could
>> implement it for 8.2 ?
>
> As has been pointed out, INDEXes arn't in the SQL spec at all, so you
> can do just about anything.
>
>> Example from real life project - performance of tsearch2 could be
>> greatly improved if decrease signature size in gistidx.h, which is
>> currently hardcoded and one should compile and install tsearch2 into
>> differnet location and use it for specific database. It's impossible
>> to have different signature length for different fts indices because
>> we have no possibility to pass parameters to CREATE INDEX command.
>
> What syntax were you envisioning? Does this value just need to be
> passed to GiST at the creation of the the index, or does it actually
> need to remembered by the backend and passed each call? At the moment
> there is some discussion on changes to the index interface so now is
> the time to ask for what you want...

it'd be nice if parameters could be passed at the creation time only and
somehow stored, so other functions could retrieve them. It's not
convenient but also safe.

>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

On K, 2005-09-21 at 09:01 +0400, Oleg Bartunov wrote:
> On Tue, 20 Sep 2005, Martijn van Oosterhout wrote:
> > What syntax were you envisioning? Does this value just need to be
> > passed to GiST at the creation of the the index, or does it actually
> > need to remembered by the backend and passed each call? At the moment
> > there is some discussion on changes to the index interface so now is
> > the time to ask for what you want...
>
> it'd be nice if parameters could be passed at the creation time only and
> somehow stored, so other functions could retrieve them. It's not
> convenient but also safe.

If not changing syntax is essential, then these could be passed by some
GUC variables at index create time, then stored.

This way one could have as many configurables a one likes .

--
Hannu Krosing <hannu(at)skype(dot)net>

On Wed, Sep 21, 2005 at 08:47:04AM +0300, Hannu Krosing wrote:
> On K, 2005-09-21 at 09:01 +0400, Oleg Bartunov wrote:
> > it'd be nice if parameters could be passed at the creation time only and
> > somehow stored, so other functions could retrieve them. It's not
> > convenient but also safe.
>
> If not changing syntax is essential, then these could be passed by some
> GUC variables at index create time, then stored.
>
> This way one could have as many configurables a one likes .

The only major problem with that is that the parameters won't survive a
dump/restore. I don't know enough about what's it's needed for to know
if that's a problem...

So even if an index can store the parameter itself, there would need to
be a way for pg_dump to extract it.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Added to TODO:

* Allow CREATE INDEX to take an additional parameter for use with
special index types

---------------------------------------------------------------------------

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Wed, Sep 21, 2005 at 08:47:04AM +0300, Hannu Krosing wrote:
> > On K, 2005-09-21 at 09:01 +0400, Oleg Bartunov wrote:
> > > it'd be nice if parameters could be passed at the creation time only and
> > > somehow stored, so other functions could retrieve them. It's not
> > > convenient but also safe.
> >
> > If not changing syntax is essential, then these could be passed by some
> > GUC variables at index create time, then stored.
> >
> > This way one could have as many configurables a one likes .
>
> The only major problem with that is that the parameters won't survive a
> dump/restore. I don't know enough about what's it's needed for to know
> if that's a problem...
>
> So even if an index can store the parameter itself, there would need to
> be a way for pg_dump to extract it.
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
-- End of PGP section, PGP failed!

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073