Packages

ecto_psql_extras

0.3.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 unused_indexes.ex
Raw

lib/queries/unused_indexes.ex 

defmodule EctoPSQLExtras.UnusedIndexes do

  @behaviour EctoPSQLExtras



  def info do

    %{

      title: "Unused and almost unused indexes",

      columns: [

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

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

        %{name: :index_size, type: :bytes},

        %{name: :index_scans, type: :integer}

      ]

    }

  end



  def query do

    """

    /* Unused and almost unused indexes */

    /* Ordered by their size relative to the number of index scans.

    Exclude indexes of very small tables (less than 5 pages),

    where the planner will almost invariably select a sequential scan,

    but may not in the future as the table grows */



    SELECT

      schemaname || '.' || relname AS table,

      indexrelname AS index,

      pg_relation_size(i.indexrelid) AS index_size,

      idx_scan as index_scans

    FROM pg_stat_user_indexes ui

    JOIN pg_index i ON ui.indexrelid = i.indexrelid

    WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192

    ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,

    pg_relation_size(i.indexrelid) DESC;

    """

  end

end