January 08, 2016

PostgresSql Index Scripts


Today everything is about maintenace of  the postgres database so, searching and building scripts. Don't try to re-invent the wheel...
cheers


/****http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html***************/

/**************unused index******************/
WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans
),
indexes as (
    SELECT idx_stat.relid, idx_stat.indexrelid,
        idx_stat.schemaname, idx_stat.relname as tablename,
        idx_stat.indexrelname as indexname,
        idx_stat.idx_scan,
        pg_relation_size(idx_stat.indexrelid) as index_bytes,
        indexdef ~* 'USING btree' AS idx_is_btree
    FROM pg_stat_user_indexes as idx_stat
        JOIN pg_index
            USING (indexrelid)
        JOIN pg_indexes as indexes
            ON idx_stat.schemaname = indexes.schemaname
                AND idx_stat.relname = indexes.tablename
                AND idx_stat.indexrelname = indexes.indexname
    WHERE pg_index.indisunique = FALSE
),
index_ratios AS (
SELECT schemaname, tablename, indexname,
    idx_scan, all_scans,
    round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC
        ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct,
    writes,
    round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2)
        as scans_per_write,
    pg_size_pretty(index_bytes) as index_size,
    pg_size_pretty(table_size) as table_size,
    idx_is_btree, index_bytes
    FROM indexes
    JOIN table_scans
    USING (relid)
),
index_groups AS (
SELECT 'Never Used Indexes' as reason, *, 1 as grp
FROM index_ratios
WHERE
    idx_scan = 0
    and idx_is_btree
UNION ALL
SELECT 'Low Scans, High Writes' as reason, *, 2 as grp
FROM index_ratios
WHERE
    scans_per_write <= 1
    and index_scan_pct < 10
    and idx_scan > 0
    and writes > 100
    and idx_is_btree
UNION ALL
SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp
FROM index_ratios
WHERE
    index_scan_pct < 5
    and scans_per_write > 1
    and idx_scan > 0
    and idx_is_btree
    and index_bytes > 100000000
UNION ALL
SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp
FROM index_ratios, all_writes
WHERE
    ( case when total_writes = 0 then 0 else writes::NUMERIC / ( total_writes + 1 )end ) > 0.02
    AND NOT idx_is_btree
    AND index_bytes > 100000000
ORDER BY grp, index_bytes DESC )
SELECT reason, schemaname, tablename, indexname,
    index_scan_pct, scans_per_write, index_size, table_size
FROM index_groups;
/******************Table scan summary***********************************************/
SELECT
x1.table_in_trouble,
pg_relation_size(x1.table_in_trouble) AS sz_n_byts,
x1.seq_scan, x1.idx_scan,
CASE
WHEN pg_relation_size(x1.table_in_trouble) > 500000000
THEN 'Exceeds 500 megs, too large to count in a view. For a count, count individually'::text
ELSE count(x1.table_in_trouble)::text
END AS tbl_rec_count,
x1.priority
FROM
(
SELECT
(schemaname::text || '.'::text) || relname::text AS table_in_trouble,
seq_scan,
idx_scan,
CASE
WHEN (seq_scan - idx_scan) < 500 THEN 'Minor Problem'::text
WHEN (seq_scan - idx_scan) >= 500 AND (seq_scan - idx_scan) < 2500 THEN 'Major Problem'::text
WHEN (seq_scan - idx_scan) >= 2500 THEN 'Extreme Problem'::text
ELSE NULL::text
END AS priority
FROM
pg_stat_all_tables
WHERE
seq_scan > idx_scan
AND schemaname != 'pg_catalog'::name
AND seq_scan > 100) x1
GROUP BY
x1.table_in_trouble,
x1.seq_scan,
x1.idx_scan,
x1.priority
ORDER BY
x1.priority DESC,
x1.seq_scan
;
/********************Check Query Plan*********************/
explain ( analyze, BUFFERS)
select * from ?

/****Postgres index Summary Row Size************/
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='SchemaNameHere'
AND  pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;













/****Postgres index Stats Summary******/
SELECT
    distinct
    t.tablename,
   indexname,
   c.reltuples AS num_rows,
   pg_size_pretty(pg_relation_size(t.schemaname::TEXT||'.'||quote_ident((t.tablename))::text)) AS table_size,
   pg_size_pretty(pg_relation_size(t.schemaname||'.'||quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
 FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='SchemaNameHere'
ORDER BY 1,2;

December 29, 2015

The end of the year is approaching so, before going any further, Happy 2016 to everyone. 
Have fun, don't do anything I won't do :-).

Now that is out of the way, I have been spending a lot time thinking on how fast technology is changing, now everything is cloud this, cloud that, but, the word cloud is just an overrated media frenzy for nothing more than San's or network computing.
Finally, I'm getting to good using PostgresSQL, but as things moving you get what you paid for. But, nothing beat free, but as stated again, you get what you paid for.
Cheers.

December 14, 2015

How to access Postgres from MS Sql server store procs.

Don't you have a bad idea or comment become weird and at the same time awesome?
Well today after a long meeting about why would be a good idea to used our postgres database directly using reporting services the reporting team kind of refused to go into the change, so, I came with a silly comment "why not just create several CRL's in ms sql server using the NPGSQL drivers and let the reporting team used those instead." the room got silence and now that's became my new POC project. So here we go a silly comment now became the solution. Next time I just keep my silly comments to myself.
Cheers,

Contact Form

Name

Email *

Message *