"Lukas" wrote:
>
> lets say I have such theoretical situation: big database with a lot
> of tables and fields, and a lot of users with are using different
> queries. And the worse - I am that data base admin ;] which has to
> add or remove indexes on table columns. As I dont know what queries
> are coming (users are writing it by them self) I dont know which
> columns should have indexes.
> My question - is here any statistics Postgres can collect to help
> answer my question. Basically I need most often "where" statements
> of queries (also JOINs etc). Is here something what can help in
> such situation?
If it were me, I would do two things:
(1) I would add indexes which seemed likely to be useful, then see
which were not being used, so I could drop them. See
pg_stat_user_indexes:
http://www.postgresql.org/docs/current/interactive/monitoring-stats.html#MONITORING-STATS-VIEWS
(2) I would log long-running queries and see what selection criteria
they used. See log_min_duration_statement:
http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN
You might also want to consider using pgFouine:
http://pgfouine.projects.postgresql.org/
-Kevin