Thread: 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 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
"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 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