Обсуждение: How to REMOVE a fillfactor set by accident?
Hi all, I'm using the GUI tool pgModeler to maintain schemas and at some point in the past it used fillfactor 10 for some indexes by default. That seems to have changed in the meantime and most of my indexes don't have ANY fillfactor set explicitly. Is there some way to REMOVE the fillfactor where it is set, so that Postgres applies it's defaults? Would be great to have an output of NULL everywhere where fillfactor is output instead of sometimes e.g. 100 and more often NULL. I've already tried setting things to NULL, which failed with mentioning that a decimal number is necessary. Though, even setting 0 fails, because a value between 10 and 100 is required. > ALTER INDEX pk_clt_rec_src SET (fillfactor = NULL); > ALTER INDEX pk_clt_rec_src SET (fillfactor = 0); So removing isn't possible at all and I can only set to concrete values like 100 or don't care at all instead? Because fillfactor=10 is only set for some low throughput test databases currently. Thanks! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Service - Bitstore Hameln GmbH E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Telefon: 05151- 9468-55 Fax: 05151- 9468-88 Mobil: 0178-8 9468-04 Firmensitz: Bitstore IT-Consulting, Frankfurter Allee 285, 10317 Berlin Steuernummer 037/230/30566, HR 27198, Amtsgericht Potsdam Geschäftsführer Janine Galonska
On 12/31/20 2:13 PM, Thorsten Schöning wrote:
https://www.postgresql.org/docs/12/sql-createindex.html
There must be a fill factor; that's how B-tree, hash, GiST and SP-GiST indices work.
The default fill factor is 90%; thus, to "remove" your custom fill factor, set it to 90%.
Hi all, I'm using the GUI tool pgModeler to maintain schemas and at some point in the past it used fillfactor 10 for some indexes by default. That seems to have changed in the meantime and most of my indexes don't have ANY fillfactor set explicitly. Is there some way to REMOVE the fillfactor where it is set, so that Postgres applies it's defaults? Would be great to have an output of NULL everywhere where fillfactor is output instead of sometimes e.g. 100 and more often NULL. I've already tried setting things to NULL, which failed with mentioning that a decimal number is necessary. Though, even setting 0 fails, because a value between 10 and 100 is required.ALTER INDEX pk_clt_rec_src SET (fillfactor = NULL); ALTER INDEX pk_clt_rec_src SET (fillfactor = 0);So removing isn't possible at all and I can only set to concrete values like 100 or don't care at all instead? Because fillfactor=10 is only set for some low throughput test databases currently.
https://www.postgresql.org/docs/12/sql-createindex.html
There must be a fill factor; that's how B-tree, hash, GiST and SP-GiST indices work.
The default fill factor is 90%; thus, to "remove" your custom fill factor, set it to 90%.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
Thorsten Schöning schrieb am 31.12.2020 um 21:13: > Is there some way to REMOVE the fillfactor where it is set, so that > Postgres applies it's defaults? Would be great to have an output of > NULL everywhere where fillfactor is output instead of sometimes e.g. > 100 and more often NULL. > > I've already tried setting things to NULL, which failed with > mentioning that a decimal number is necessary. Though, even setting 0 > fails, because a value between 10 and 100 is required. > >> ALTER INDEX pk_clt_rec_src SET (fillfactor = NULL); >> ALTER INDEX pk_clt_rec_src SET (fillfactor = 0); > ALTER INDEX pk_clt_rec_src RESET (fillfactor); should do it.
On Thu, Dec 31, 2020 at 1:24 PM Ron <ronljohnsonjr@gmail.com> wrote:
Is there some way to REMOVE the fillfactor where it is set, so that Postgres applies it's defaults? Would be great to have an output of NULL everywhere where fillfactor is output instead of sometimes e.g. 100 and more often NULL. I've already tried setting things to NULL, which failed with mentioning that a decimal number is necessary. Though, even setting 0 fails, because a value between 10 and 100 is required.ALTER INDEX pk_clt_rec_src SET (fillfactor = NULL); ALTER INDEX pk_clt_rec_src SET (fillfactor = 0);So removing isn't possible at all and I can only set to concrete values like 100 or don't care at all instead? Because fillfactor=10 is only set for some low throughput test databases currently.
https://www.postgresql.org/docs/12/sql-createindex.html
There must be a fill factor; that's how B-tree, hash, GiST and SP-GiST indices work.
The default fill factor is 90%; thus, to "remove" your custom fill factor, set it to 90%.
Since the index already exists the "ALTER INDEX" documentation is more pertinent. And behold:
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
David J.
Guten Tag Thomas Kellerer, am Donnerstag, 31. Dezember 2020 um 21:31 schrieben Sie: > ALTER INDEX pk_clt_rec_src RESET (fillfactor); Thanks, should have looked into the docs instead of googling only! :-) Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Service - Bitstore Hameln GmbH E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Telefon: 05151- 9468-55 Fax: 05151- 9468-88 Mobil: 0178-8 9468-04 Firmensitz: Bitstore IT-Consulting, Frankfurter Allee 285, 10317 Berlin Steuernummer 037/230/30566, HR 27198, Amtsgericht Potsdam Geschäftsführer Janine Galonska