sql.indexes_with_boolean.sql Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of pg-index-health-core Show documentation

pg-index-health-core is a Java library for analyzing and maintaining indexes and tables health in PostgreSQL databases on a specific host.

There is a newer version: 0.14.4

Show newest version

/*
 * Copyright (c) 2019-2024. Ivan Vakhrushev and others.
 * https://github.com/mfvanek/pg-index-health-sql
 *
 * Licensed under the Apache License 2.0
 */

-- Finds indexes that contains boolean values.
select
    pi.indrelid::regclass::text as table_name,
    pi.indexrelid::regclass::text as index_name,
    col.attname::text as column_name,
    col.attnotnull as column_not_null,
    pg_relation_size(pi.indexrelid) as index_size
from
    pg_catalog.pg_index pi
    inner join pg_catalog.pg_class pc on pc.oid = pi.indexrelid
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
    inner join pg_catalog.pg_attribute col on col.attrelid = pi.indrelid and col.attnum = any(pi.indkey)
where
    nsp.nspname = :schema_name_param::text and
    not pi.indisunique and
    pi.indisready and
    pi.indisvalid and
    col.atttypid = 'boolean'::regtype
order by table_name, index_name;