Home 2017 June Greenplum: Script to find the size and usage statistics of Indexes

Greenplum: Script to find the size and usage statistics of Indexes

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a script to find the size and usage statistics of Greenplum Indexes. Having the complete knowledge of our database indexes is imperative. We should check the size and usage of indexes.

Recently, I started my work with Greenplum which based on PostgreSQL 8.2, so I am sharing a script to check index usage in Greenplum.

Anvesh Patel
PostgreSQL: Script to find the count of objects for each Database SchemaGreenplum: Script to find configuration information of Resource Queues
Comments: 2
  1. Suma
    May 24, 2018 at 5:47 am

    Hi Anvesh,

    I need help on this.
    There are so many regular users have udw_prd_connect__r group rle.ally
    Usually regular users should have acccess for udw_prd_connect__sso_r group role to connect to the PRD database.
    Task:- Need to find the only regular users who have access for udw_prd_connect_r incorrectly.
    Need to write a script for this..Below where i am going wrong find out.let me know..Or please if possible modify.

    select rpt.grantee,rpt.rolcanlogin2,rpt.connect_r,rpt.connect_sso_r,
    case
    when rpt.connect_r=TRUE and rpt.connect_sso_r=TRUE then ‘ERROR’
    when rpt.connect_r=FALSE and rpt.connect_sso_r=FALSE then ‘ERROR’
    else ‘OK’ end as “flag”
    from (
    select r.grantee,r.rolcanlogin2,r.grantor,
    case when r.grantor=’udw_prd_connect_r’ then TRUE else FALSE
    end as “connect_r”,
    case when r.grantor=’udw_prd_connect_sso_r’ then TRUE else FALSE
    end as “connect_sso_r”
    from(
    select au2.rolname grantee,au2.rolcanlogin AS rolcanlogin2,au1.rolname grantor,
    au1.rolcanlogin rolcalogin1 from pg_catalog.pg_auth_members m,
    pg_catalog.pg_authid au2 , pg_catalog.pg_authid au1
    where m.roleid=au1.oid and m.member=au2.oid

    This query will not encounter the errors. but it will show the duplicate rows because some users have both roles (udw_prd_connect_r and udw_prd_connect_sso_r) but i need every user should have one row.

    TASK:
    Need to exapand:-
    Regular user should have udw_prd_connect_r
    Service account should not have udw_prd_connect_sso_r

    Please help me out to get proper result.

    Thank you,
    Suma R

    ReplyCancel
  2. Suma
    May 24, 2018 at 5:51 am

    Correct script:-

    select rpt.grantee,rpt.rolcanlogin2,rpt.connect_r,rpt.connect_sso_r,
    case
    when rpt.connect_r=TRUE and rpt.connect_sso_r=TRUE then ‘ERROR’
    when rpt.connect_r=FALSE and rpt.connect_sso_r=FALSE then ‘ERROR’
    else ‘OK’ end as “flag”
    from (
    select r.grantee,r.rolcanlogin2,r.grantor,
    case when r.grantor=’udw_prd_connect_r’ then TRUE else FALSE
    end as “connect_r”,
    case when r.grantor=’udw_prd_connect_sso_r’ then TRUE else FALSE
    end as “connect_sso_r”
    from(
    select au2.rolname grantee,au2.rolcanlogin AS rolcanlogin2,au1.rolname grantor,
    au1.rolcanlogin rolcalogin1 from pg_catalog.pg_auth_members m,
    pg_catalog.pg_authid au2 , pg_catalog.pg_authid au1
    where m.roleid=au1.oid and m.member=au2.oid
    and aq1.rolname in (‘udw_prd_connect_r’,’udw_prd_connect_sso_r’) r
    group by 1,2,3
    ) rpt
    order by 1,2;

    Please help to find out the result.

    ReplyCancel

Leave a Reply to Suma Cancel reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

June 8, 2017 2 Comments Greenplum, Greenplum DBA Script, , , , , , , , , , , , , , ,