PostgreSQL Hacks - passing parameters to CREATE INDEX

Author

passing parameters to CREATE INDEX

Oleg Bartunov

2005-09-20, 8:24 pm

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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Andrew Dunstan

2005-09-20, 8:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Martijn van Oosterhout

2005-09-20, 8:24 pm

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@svana.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.

Tom Lane

2005-09-20, 8:24 pm

Martijn van Oosterhout <kleptog@svana.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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Oleg Bartunov

2005-09-21, 3:24 am

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

> On Tue, Sep 20, 2005 at 11:26:26PM +0400, Oleg Bartunov wrote:
>
> As has been pointed out, INDEXes arn't in the SQL spec at all, so you
> can do just about anything.
>
>
> 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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Hannu Krosing

2005-09-21, 3:24 am

On K, 2005-09-21 at 09:01 +0400, Oleg Bartunov wrote:
> On Tue, 20 Sep 2005, Martijn van Oosterhout 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 .

--
Hannu Krosing <hannu@skype.net>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Martijn van Oosterhout

2005-09-21, 11:24 am

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:
>
> 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@svana.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.

Bruce Momjian

2005-09-22, 8:25 pm

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.[color=darkred]
> On Wed, Sep 21, 2005 at 08:47:04AM +0300, Hannu Krosing wrote:
>
> 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@svana.org> http://svana.org/kleptog/
-- End of PGP section, PGP failed!

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links