Обсуждение: Stats
Hello, 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? -- Lukas www.nsoft.lt
"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