IndexChecker

IndexChecker provides a simple way to check the indexes in your databases.

Please note that the README on GitHub is accurate with the latest code on GitHub. You are most likely using a released version of IndexChecker, so please refer to the documentation for the latest released version of IndexChecker.

gem install index_checker

or Gemfile:

gem 'index_checker'

then run bundle install

Run the generator

rails g index_checker:initialize

Which will create a config file and a report file. In the config file, you can set database you need to check, username and password:

IndexChecker.config do |c|

  # dbname  : required
  # Database name is required
  c.dbname = 'database-to-analysis'


  # user    : optional.
  # If the `user` is not provided, it will be the default user (same name as your OS name)
  # c.user = 'your-postgres-username'


  # password: optional
  # The password just requires if your (Postgres) user authenticates by `md5` method,
  # that is optional for other authentication methods: `trust` or `peer`. 
  # Your postgres config can be found in pg_hba.config
  # c.password = 'your-password' 


  # rdbms   : default  
  # This configuration is default. IndexChecker just support PostgresSQL for now!

  # c.rdbms = 'postgresql'
end

That's enough. Just run the following command and waiting for report file open in your default browser!

bundle exec rake index_checker:analysis

dbname(*) : name of database that you need to check.

user : username to login with PostgreSQL

• if the user name is not provided, we will use the default user - same as your Operating System username

password: password for the user above.

• This config is optional or required that depends on your PostgreSQL configuration. If your user was configured to authenticate by md5 method - the password is required! And that is not neccessary if the authentication is trust.
• You can check that configuration in file pg_hba.config or more information in this discussion.

Table name

Index name

Write actions: sum of n_tup_ins, n_tup_upd, n_tup_del from pg_stat_all_tables table.

Read actions: sum of seq_tup_read, idx_tup_fetch from pg_stat_all_tables table.

Sequence Scan: Times of table scaning without index, be selected by seq_scan from pg_stat_all_tables table.

Index Scan: Times of table scaning uses this index, be selected by idx_scan from pg_stat_all_tables table.

Index Size: Size of the index table (in byte), be selected by pg_relation_size(indexrelid) from pg_stat_user_indexes table.

I hope this simple gem will helpful for you ❤️

If you found any problems in source code (mistyped, code smell, wrong queries,...), please don't hesitate to correct me. Thanks a lot! 🙏

Now, IndexChecker just

• works with PostgreSQL only!
• generate some very basic Index information!

So, I will implement for more Relative Database Management Systems and add features like detect redundant indexes, unused indexes, evaluate the effectiveness of indexes,...

I hope to get help from you! 🤗

Thank you again.