til.duyet.net
  • 🤪Today I Learned
  • Data Engineering
    • ☁️AWS
      • Make an NVMe volume available for use on AWS EC2
      • AWS EMR
        • S3 Dist CP
        • Manage services
        • EMR - Tez
        • Issues
    • ⚒️Tools
    • 💻Shell
      • MacOS's Touch ID on Terminal
      • Using `sed` to find and replace in file
      • Merging contents of multiple .csv files into single .csv file
    • env from ConfigMap or Secrets
    • ☸️Kubernetes, Helm, Kustomize
      • initContainer to download file to pod
      • Kustomize: omission of resources
      • K8S: Services and Labels
      • K8S: PVC stuck in status “Terminating”
      • K8S: Port Forward
      • K8S: Pull an Image from a Private Registry
      • Happy helm
      • Helm: render manifest locally
      • Helm: Public Helm chart repository with GitHub Pages
    • 🔄Apache Airflow
      • Extend official Docker image
      • Generate offline SQL upgrade script
      • Airflow in Docker
      • Mastering Airflow UI
      • Best Practices for Airflow and ETLs
      • Airflow in Docker Compose
      • Useful SQL queries for Apache Airflow
    • 🐳Docker
      • "Distroless" Docker Images
      • Docker cleanup
      • Optimize the Docker Image Size
      • The best Docker base image for Python
  • Database
    • Google BigQuery
      • BigQuery Cancel Running Query
      • BigQuery - Split string and get the first part
      • BigQuery - UNNEST in SELECT
      • Bigquery - Sample queries for audiences based
      • BigQuery cookbook for Google Analytics Exported Data
    • Apache Hive
      • Hive - SHOW schemas/tables/create
    • AWS Redshift / Postgres
      • Amazon Redshift Utilities
      • Postgres - Index Summary
      • Postgres - List tables
      • Redshift - GRANT
      • Redshift - tables and their owners
      • Redshift - Check the table size
    • Presto
      • Aliyun Data Lake Analytics (Presto) - Add partition on non-existing location
      • AWS Athena - Add Partition
  • Programming
    • 🍪Rust
      • cheats.rs
      • Imperative vs Declarative
      • Generate Struct from JSON
    • 🐍Python
      • YAML config file with environment variables
      • date_range_generator
      • get_all_s3_keys
      • Pipenv
    • 👻Golang
      • Functions and Methods in Go?
      • Convert JSON to Go struct
    • 💎Javascript / Typescript
      • Intl.ListFormat
    • ✨FE / React
      • Beautiful icons, images, ..
      • Flexbox
      • Create hook to inject JS script
  • Unix
    • Git - Pretty git branch graphs
    • Checking files in Docker build context
    • Bash get the directory of the current script
    • Vim
    • Find and replace
  • Miscellaneous
    • [Fig] Single machine and distributed system structure
    • Deploying Machine Learning Models at Scale
    • Bypass a Chrome certificate/HSTS error
    • Articles
Powered by GitBook
On this page

Was this helpful?

Edit on Git
  1. Database
  2. AWS Redshift / Postgres

Postgres - Index Summary

Here's a sample query to pull the number of rows, indexes, and some info about those indexes for each table. Ditch the pg_size_pretty if you’re on an ancient (<= 8.2) version)

SELECT
    pg_class.relname,
    pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
    pg_class.reltuples AS num_rows,
    count(indexname) AS number_of_indexes,
    CASE WHEN x.is_unique = 1 THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    SUM(case WHEN number_of_columns = 1 THEN 1
              ELSE 0
            END) AS single_column,
    SUM(case WHEN number_of_columns IS NULL THEN 0
             WHEN number_of_columns = 1 THEN 0
             ELSE 1
           END) AS multi_column
FROM pg_namespace 
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
       (SELECT indrelid,
           max(CAST(indisunique AS integer)) AS is_unique
       FROM pg_index
       GROUP BY indrelid) x
       ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
    AS foo
    ON pg_class.relname = foo.ctablename
WHERE 
     pg_namespace.nspname='public'
AND  pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;
PreviousAmazon Redshift UtilitiesNextPostgres - List tables

Last updated 4 years ago

Was this helpful?

Source:

https://wiki.postgresql.org/wiki/Index_Maintenance