pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: How to determine which indexes are not using or using seldom in database
3+ messages / 3 participants
[nested] [flat]
* How to determine which indexes are not using or using seldom in database
@ 2007-04-02 08:12 Denis Lishtovny <d.lishtovny@chronopay.com>
2007-04-02 07:45 ` Re: How to determine which indexes are not using or using seldom in database Andreas Kostyrka <andreas@kostyrka.org>
2007-04-02 08:45 ` Re: How to determine which indexes are not using or using seldom in database Thomas Pundt <mlists@rp-online.de>
0 siblings, 2 replies; 3+ messages in thread
From: Denis Lishtovny @ 2007-04-02 08:12 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to determine which indexes are not using or using seldom in database
2007-04-02 08:12 How to determine which indexes are not using or using seldom in database Denis Lishtovny <d.lishtovny@chronopay.com>
@ 2007-04-02 07:45 ` Andreas Kostyrka <andreas@kostyrka.org>
1 sibling, 0 replies; 3+ messages in thread
From: Andreas Kostyrka @ 2007-04-02 07:45 UTC (permalink / raw)
To: Denis Lishtovny <d.lishtovny@chronopay.com>; +Cc: pgsql-performance
* 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to determine which indexes are not using or using seldom in database
2007-04-02 08:12 How to determine which indexes are not using or using seldom in database Denis Lishtovny <d.lishtovny@chronopay.com>
@ 2007-04-02 08:45 ` Thomas Pundt <mlists@rp-online.de>
1 sibling, 0 replies; 3+ messages in thread
From: Thomas Pundt @ 2007-04-02 08:45 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, "Denis Lishtovny" <d.lishtovny@chronopay.com>
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/ ----
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-04-02 08:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-02 08:12 How to determine which indexes are not using or using seldom in database Denis Lishtovny <d.lishtovny@chronopay.com>
2007-04-02 07:45 ` Andreas Kostyrka <andreas@kostyrka.org>
2007-04-02 08:45 ` Thomas Pundt <mlists@rp-online.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox