Search Postgresql Archives

Re: remove indexes on a column?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux