Обсуждение: Upgrade from 9.0.5 to 9.1.1 - Problems with citext indexes
Hi,
upgrading Postgres 9.0.5 to 9.1.1 is done using:
- pg_upgrade
- CREATE EXTENSION citext FROM unpackaged (using the latest patch)
This works fine even for indexes on citext columns.
The problem comes with an index on LOWER(citext), e.g. the index idx_lower_login
has been created on column 'login::citext' using 'btree (lower(login::text))'.
This index is NOT used after the upgrade to 9.1.1.
A REINDEX did not help, only after DROP/CREATE INDEX of idx_lower_login
the index was used by the query planner.
As in our case dropping/creating a new index takes a long time (30min = downtime) we'd like to
discuss other options and ideas. E.g. would it be possible to concurrently create already on 9.0.5
an index that can be upgraded to 9.1.1. and will directly be used?
Or is there anything that can be done to the PG internal tables so that the old index will be used as is?
Thanks for your help.
Rudolf van der Leeden
Scoreloop, Germany
A subsidiary of Research in Motion (RIM)
upgrading Postgres 9.0.5 to 9.1.1 is done using:
- pg_upgrade
- CREATE EXTENSION citext FROM unpackaged (using the latest patch)
This works fine even for indexes on citext columns.
The problem comes with an index on LOWER(citext), e.g. the index idx_lower_login
has been created on column 'login::citext' using 'btree (lower(login::text))'.
This index is NOT used after the upgrade to 9.1.1.
A REINDEX did not help, only after DROP/CREATE INDEX of idx_lower_login
the index was used by the query planner.
As in our case dropping/creating a new index takes a long time (30min = downtime) we'd like to
discuss other options and ideas. E.g. would it be possible to concurrently create already on 9.0.5
an index that can be upgraded to 9.1.1. and will directly be used?
Or is there anything that can be done to the PG internal tables so that the old index will be used as is?
Thanks for your help.
Rudolf van der Leeden
Scoreloop, Germany
A subsidiary of Research in Motion (RIM)
Rudolf van der Leeden wrote: > Hi, > > upgrading Postgres 9.0.5 to 9.1.1 is done using: > > - pg_upgrade > - CREATE EXTENSION citext FROM unpackaged (using the latest patch) > > This works fine even for indexes on citext columns. > The problem comes with an index on LOWER(citext), e.g. the index > idx_lower_login > has been created on column 'login::citext' using 'btree > (lower(login::text))'. > This index is NOT used after the upgrade to 9.1.1. > A REINDEX did not help, only after DROP/CREATE INDEX of idx_lower_login > the index was used by the query planner. > > As in our case dropping/creating a new index takes a long time (30min = > downtime) we'd like to > discuss other options and ideas. E.g. would it be possible to concurrently > create already on 9.0.5 > an index that can be upgraded to 9.1.1. and will directly be used? > Or is there anything that can be done to the PG internal tables so that the > old index will be used as is? That is quite odd. I have no idea what would cause that. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +