Packages

ecto_psql_extras

0.4.2
0.8.8 0.8.7 0.8.6 0.8.5 0.8.4 0.8.3 0.8.2 0.8.1 0.8.0 0.7.15 0.7.14 0.7.13 0.7.12 0.7.11 0.7.10 0.7.9 0.7.8 0.7.7 0.7.6 0.7.5 0.7.4 0.7.3 0.7.2 0.7.1 0.7.0 0.6.5 0.6.4 0.6.3 0.6.2 0.6.1 0.6.0 0.4.4 0.4.3 0.4.2 0.4.1 0.4.0 0.3.4 0.3.3 0.3.2 0.3.1 0.3.0 0.2.0 0.1.4 0.1.3 0.1.2 0.0.2 0.0.1

Ecto PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum stats and more.

HexDocs

Current section

Files

Jump to
Readme
47 Versions
3 Dependencies
Dependants
Files
Activity
ecto_psql_extras lib queries index_usage.ex
Raw

lib/queries/index_usage.ex 

defmodule EctoPSQLExtras.IndexUsage do

  @behaviour EctoPSQLExtras



  def info do

    %{

      title: "Index hit rate (effective databases are at 99% and up)",

      columns: [

        %{name: :relname, type: :string},

        %{name: :percent_of_times_index_used, type: :numeric},

        %{name: :rows_in_table, type: :int}

      ]

    }

  end



  def query do

    """

    /* Index hit rate (effective databases are at 99% and up) */



    SELECT relname,

       CASE idx_scan

         WHEN 0 THEN NULL

         ELSE (100 * idx_scan / (seq_scan + idx_scan))

       END percent_of_times_index_used,

       n_live_tup rows_in_table

     FROM

       pg_stat_user_tables

     ORDER BY

       n_live_tup DESC;

    """

  end

end