[ADMIN] Upgrade from 9.0.5 to 9.1.1 - Problems with citext indexes

[Rudolf van der Leeden]

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)

[Bruce Momjian]

Post by Rudolf van der Leeden
Hi,
- 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 <***@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +