Thread: How to determine which indexes are not using or using seldom in database
How to determine which indexes are not using or using seldom in database
From
"Denis Lishtovny"
Date:
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.
Re: How to determine which indexes are not using or using seldom in database
From
Thomas Pundt
Date:
Hi, On Monday 02 April 2007 10:12, Denis Lishtovny wrote: | 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. Try "select * from pg_stat_user_indexes;" - that should give you a good start to look at. | p.s for example i need this to reduce database size for increase backup and | restore speed. Deleting indexes won't reduce backup size noticeably (but has impact on restore speed), if you use pg_dump for backup. Ciao, Thomas -- Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
Re: How to determine which indexes are not using or using seldom in database
From
Andreas Kostyrka
Date:
* Denis Lishtovny <d.lishtovny@chronopay.com> [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
Hi All,
Currently in one of the projects we want to restrict the unauthorized users to the Postgres DB. Here we are using Postgres version 8.2.0
Can anybody tell me how can I provide the user based previleges to the Postgres DB so that, we can restrict the unauthorized users as well as porivde the access control to the users based on the set previleges by the administrator.
Thanks and Regards,
Ramachandra B.S.
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com |
ramachandra.bhaskaram@wipro.com wrote: > > Hi All, > > Currently in one of the projects we want to restrict the > unauthorized users to the Postgres DB. Here we are using Postgres > version 8.2.0 > > Can anybody tell me how can I provide the user based previleges > to the Postgres DB so that, we can restrict the unauthorized users as > well as porivde the access control to the users based on the set > previleges by the administrator. > The pgsql-general list might be more appropriate for this type of question... Still: Are you talking restrictions based on database-users ? If so, look up grant and revoke in the PG documentation (under SQL commands). If you're talking about restricting system-users to even attempt to use psql (which really, would not be much of a restriction), then perhaps you would have to assign a group-owner to the file psql and grant execute permission to the group only (well, and owner). HTH, Carlos --