* Denis Lishtovny <d.lishtovny@xxxxxxxxxxxxx> [070402 09:20]: > Hello All. > > I have a lot of tables and indexes in database. I must to determine which > indexes are not using or using seldon in databese . I enabled all posible > statistics in config but a can`t uderstand how to do this. > Thanks. > > p.s for example i need this to reduce database size for increase backup > and restore speed. Indexes are not backuped, and you can increase restore speed by temporarily dropping them. Current pg_dumps should be fine from this aspect. Discovering which tables are unused via the database suggests more of a software eng. problem IMHO. And it is bound to be unprecise and dangerous, tables might get read from: *) triggers. That means some tables might be only consulted if user X is doing something. Or we have full moon. Or the Chi of the DBA barked 3 times this day. *) during application startup only (easy to solve by forcing all clients to restart) *) during a cron job (daily, weekly, monthly, bi-monthly) *) only during human orginated processes. Not a good thing to decide to drop tables just because nothing has accessed them for half an hour. Or even a week. Worse, some tables might have relationsships that are missing in the database (foreign constraint forgotten, or some relationships that are hard to express with SQL constraints). OTOH, if you just try to get a feel what parts of the database is active, you can start by enabling SQL statement logging, and analyze some of that output. Andreas
