Re: [HACKERS] passing parameters to CREATE INDEX
Teodor Sigaev [EMAIL PROTECTED] wrote: pluggable parameters for index. I think, we may can add to pg_opclass's definition method/parameter name and create some API (may be, index specific) to propagate parameter's to module's interface functions to index. How abount adding a new option hander to GiST/GIN support functions? Presently, amoptions() are defined as bytea *amoptions (ArrayType *reloptions, bool validate). If there is a support function like this form in operator classes, we can propagate options. Eventually, calling sequence will be DefineIndex() - amoptions() - tsvectoroptions(). We also need to change gist/ginoptions() in order to determine what operator class is used. This is a bit messy problem. Options are parsed before support functions are initialized, so that I don't know in what form we should pass the operator class to amoptions(). Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] passing parameters to CREATE INDEX
http://archives.postgresql.org/pgsql-hackers/2005-09/msg00851.php Just to follow up on the discussion of that thread: what's been implemented is a way to store arbitrary name=value strings in an index's pg_class entry, and to make these available in a pre-parsed form through the index relcache entry. However you'd have to be cautious about using the values directly for any fundamental index structure decisions, because ALTER INDEX will just change them without giving you an opportunity to modify the index in response. So depending on what you are doing, you might need to store the real values in the index metapage, and set those values from the reloptions parameters only at ambuild() time. This would mean that ALTER INDEX + REINDEX would be the I see. There is one more problem: pluggable parameters for index. For example, the parameter needed for tsearch2 (size of signature) isn't useful for others modules/opclasses. Another issue, GiST (and GIN too) doesn't have metapage at all for now, it's not a problem, but until now it wasn't needed. I think, we may can add to pg_opclass's definition method/parameter name and create some API (may be, index specific) to propagate parameter's to module's interface functions to index. Also: as of CVS tip ginoptions() accepts FILLFACTOR but nothing is done with it. Can you do something useful with FILLFACTOR in GIN? Now GIN is nested B-Tree: B-tree for entries (lexemes for tsearch2) and B-Tree for ItemPointers per entry if entry is popular enough. So fillfactor may be used as usual. Small advertising :) : http://www.sigaev.ru/gin/GinStructure.pdf -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] passing parameters to CREATE INDEX
Teodor Sigaev [EMAIL PROTECTED] writes: can add to pg_opclass's definition method/parameter name and create some API (may be, index specific) to propagate parameter's to module's interface functions to index. Huh? You can get them from the index's Relation structure. I don't think there's anything missing in the API. About all you need is an extended struct definition for rd_options, and to provide your own code substituting for default_reloptions(). An index AM can do both of those locally to itself. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] passing parameters to CREATE INDEX
Just wanted to make clear to Hackers that the gates are now open to include other parameters for CREATE INDEX, as originally requested here: http://archives.postgresql.org/pgsql-hackers/2005-09/msg00851.php The new WITH (param=value...) syntax could easily be extended to include a variety of other parameters for each different index AM. http://developer.postgresql.org/docs/postgres/sql-createindex.html Thanks to Itagaki Takahiro and various reviewers/committers. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] passing parameters to CREATE INDEX
Simon Riggs [EMAIL PROTECTED] writes: Just wanted to make clear to Hackers that the gates are now open to include other parameters for CREATE INDEX, as originally requested here: http://archives.postgresql.org/pgsql-hackers/2005-09/msg00851.php Just to follow up on the discussion of that thread: what's been implemented is a way to store arbitrary name=value strings in an index's pg_class entry, and to make these available in a pre-parsed form through the index relcache entry. However you'd have to be cautious about using the values directly for any fundamental index structure decisions, because ALTER INDEX will just change them without giving you an opportunity to modify the index in response. So depending on what you are doing, you might need to store the real values in the index metapage, and set those values from the reloptions parameters only at ambuild() time. This would mean that ALTER INDEX + REINDEX would be the procedure needed to change the structure of an existing index. OTOH, if you can tolerate on-the-fly changes of a parameter, then using it directly from the rd_options struct would be reasonable. Also: as of CVS tip ginoptions() accepts FILLFACTOR but nothing is done with it. Can you do something useful with FILLFACTOR in GIN? regards, tom lane ---(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
Re: [HACKERS] passing parameters to CREATE INDEX
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@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. -- 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
Re: [HACKERS] passing parameters to CREATE INDEX
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@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. pgpp5w9mVJ5or.pgp Description: PGP signature
[HACKERS] 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@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
Re: [HACKERS] passing parameters to CREATE INDEX
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
Re: [HACKERS] passing parameters to CREATE INDEX
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. pgpK0N8FPyZ9w.pgp Description: PGP signature
Re: [HACKERS] passing parameters to CREATE INDEX
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
Re: [HACKERS] passing parameters to CREATE INDEX
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@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
Re: [HACKERS] passing parameters to CREATE INDEX
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 [EMAIL PROTECTED] ---(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
