remove indexes on a column?
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 migration history 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 the indexes 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
"Vance Maverick" <vmaverick@pgp.com> writes:
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.
Yeah, this seems a bit tricky if you have expression indexes involving
the column. I concur that trying to parse the expressions is a bad
idea --- even if your code works today, it'll probably break in future
PG releases, because the nodetree representation is not very stable.
What I'd look for is pg_depend entries showing indexes that depend on
the column. Here's a hint:
regression=# create table foo (f1 int);
CREATE TABLE
regression=# create index fooi on foo (abs(f1));
CREATE INDEX
regression=# select * from pg_depend where refobjid = 'foo'::regclass;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+--------+----------+------------+----------+-------------+---------
1247 | 534605 | 0 | 1259 | 534603 | 0 | i
1259 | 534606 | 0 | 1259 | 534603 | 1 | a
(2 rows)
regression=# select 534606::regclass;
regclass
----------
fooi
(1 row)
regards, tom lane
Perfect! Looks like I can get the names of the existing indexes by
doing
SELECT dep.relname
FROM pg_attribute col, pg_class tab, pg_depend pd, pg_class dep
WHERE tab.relname = 'mytable'
AND col.attname = 'mycolumn'
AND col.attrelid = tab.oid
AND pd.refobjid = tab.oid
AND pd.refobjsubid = col.attnum
AND pd.objid = dep.oid
AND dep.relkind = 'i';
Thanks.
Vance
Show quoted text
On Wed, 2008-09-10 at 00:23 -0400, Tom Lane wrote:
"Vance Maverick" <vmaverick@pgp.com> writes:
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.Yeah, this seems a bit tricky if you have expression indexes involving
the column. I concur that trying to parse the expressions is a bad
idea --- even if your code works today, it'll probably break in future
PG releases, because the nodetree representation is not very stable.What I'd look for is pg_depend entries showing indexes that depend on
the column. Here's a hint:regression=# create table foo (f1 int);
CREATE TABLE
regression=# create index fooi on foo (abs(f1));
CREATE INDEX
regression=# select * from pg_depend where refobjid = 'foo'::regclass;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+--------+----------+------------+----------+-------------+---------
1247 | 534605 | 0 | 1259 | 534603 | 0 | i
1259 | 534606 | 0 | 1259 | 534603 | 1 | a
(2 rows)regression=# select 534606::regclass;
regclass
----------
fooi
(1 row)regards, tom lane
Vance Maverick <vmaverick@pgp.com> writes:
Perfect! Looks like I can get the names of the existing indexes by
doing
SELECT dep.relname
FROM pg_attribute col, pg_class tab, pg_depend pd, pg_class dep
WHERE tab.relname = 'mytable'
AND col.attname = 'mycolumn'
AND col.attrelid = tab.oid
AND pd.refobjid = tab.oid
AND pd.refobjsubid = col.attnum
AND pd.objid = dep.oid
AND dep.relkind = 'i';
Too tired/lazy to check right now, but you should also look into
what the pg_depend representation is for constraints: I have a feeling
that a unique or primary key constraint yields a pg_depend structure
with an indirect linkage through a pg_constraint entry.
Also, the above query doesn't seem very schema-safe: what if there
are multiple tables named mytable? Personally I'd try something
like tab.oid = 'mytable'::regclass instead of the relname test.
regards, tom lane