remove indexes on a column?
| От | Vance Maverick |
|---|---|
| Тема | remove indexes on a column? |
| Дата | |
| Msg-id | DAA9CBC6D4A7584ABA0B6BEA7EC6FC0B01268CB1@hq-exch01.corp.pgp.com обсуждение исходный текст |
| Ответы |
Re: remove indexes on a column?
|
| Список | pgsql-general |
I'd like to write a SQL script, possibly with some PL/pgSQL, that can find all indexes on a column -- so I can remove
them,and set up exactly the indexes I want. (I know what indexes are *supposed* to be there, but depending on the
migrationhistory of the specific instance, the names may vary.)
I tried writing this logic using the system catalogs (pg_index, etc.), and it works up to a point. But when some of
theindexes involve expressions, e.g.
CREATE INDEX foo_lower_value ON foo(lower(value));
it's not so easy to do the lookup. In this case, the column index is coded deep in an expression string ("in
nodeToString()representation"), and I don't see how to parse that.
Alternatively, I could take the brute-force approach:
- create a new column with the same type
- copy the values from the old column to the new
- drop the old column, presumably killing all the indices
- rename the new column to the old name
But that involves a lot of data copying, table restructuring, etc.
Is there a good way to do this? Thanks,
Vance
В списке pgsql-general по дате отправления: