/*
Filename: functions_dbperformance.txt
Overview: PLSQL Performance Functions written for PostgreSQL v. 8.0.3
Date:     04/07/2005
Requires: This script is designed to use the Postgres Database, v.8
Changes: 
04/07/2005	Lane Van Ingen	Established initial file with 9 functions
*/


CREATE TABLE perf_stats_database
( database_name varchar NOT NULL,
  updated_time timestamp(3) NOT NULL,
  shared_buffers int8 NOT NULL DEFAULT 0,
  work_memory int8 NOT NULL DEFAULT 0,
  maint_work_mem int8 NOT NULL DEFAULT 0,
  insertz int8 NOT NULL DEFAULT 0,
  updatez int8 NOT NULL DEFAULT 0,
  deletez int8 NOT NULL DEFAULT 0,
  idx_scan int8 NOT NULL DEFAULT 0,
  seq_scan int8 NOT NULL DEFAULT 0,
  commitz int8 NOT NULL DEFAULT 0,
  rollbackz int8 NOT NULL DEFAULT 0,
  dsk_reads int8 NOT NULL DEFAULT 0,
  mem_reads int8 NOT NULL DEFAULT 0,
  pct_memory_hits numeric(8,4) NOT NULL DEFAULT 0,
  database_sz varchar NOT NULL,
  CONSTRAINT perf_stats_database_pk PRIMARY KEY (database_name, updated_time)
) WITHOUT OIDS;

CREATE TABLE perf_stats_objects
( database_name varchar NOT NULL,
  updated_time timestamp(3) NOT NULL,
  obj_name varchar NOT NULL,
  obj_name1 varchar NOT NULL,
  obj_type varchar(5) NOT NULL,
  dsk_reads int8 NOT NULL DEFAULT 0,
  mem_reads int8 NOT NULL DEFAULT 0,
  pct_memory_hits numeric(8,4) NOT NULL DEFAULT 0,
  idx_scan int8 NOT NULL DEFAULT 0,
  seq_scan int8 NOT NULL DEFAULT 0,
  pct_index_hits numeric(8,4) NOT NULL DEFAULT 0,
  insertz int8 NOT NULL DEFAULT 0,
  updatez int8 NOT NULL DEFAULT 0,
  deletez int8 NOT NULL DEFAULT 0,
  net_kernal_reads int8 NOT NULL DEFAULT 0,
  table_len int8 NOT NULL DEFAULT 0,
  tuple_len int8 NOT NULL DEFAULT 0,
  dead_tuple_len int8 NOT NULL DEFAULT 0,
  free_space int8 NOT NULL DEFAULT 0,
  tuple_pct numeric(8,4) NOT NULL DEFAULT 0,
  dead_tuple_pct numeric(8,4) NOT NULL DEFAULT 0,
  free_pct numeric(8,4) NOT NULL DEFAULT 0,
  system_pct numeric(8,4) NOT NULL DEFAULT 0,
  pagez int8 NOT NULL DEFAULT 0,
  bytez int8 NOT NULL DEFAULT 0,
  rowz int8 NOT NULL DEFAULT 0,
  columnz int2 NOT NULL DEFAULT 0,
  CONSTRAINT perf_stats_objects_pk PRIMARY KEY (database_name, updated_time, obj_name, obj_name1)
) WITHOUT OIDS;

----------------------------------- database_size() -----------------------------------

CREATE OR REPLACE FUNCTION database_size(name)
  RETURNS int8 AS
'$libdir/dbsize', 'database_size'
  LANGUAGE 'c' VOLATILE STRICT;


---------------------------------- insert_perf_db() -----------------------------------

CREATE OR REPLACE FUNCTION insert_perf_db("varchar", "timestamp", int8, int8, int8, int8, int8, int8, int8, int8, int8, int8, int8, int8, "numeric", "varchar")
  RETURNS text AS
$BODY$

DECLARE
  database_name			ALIAS for $1;
  updated_time			ALIAS for $2;
  shared_buffers		ALIAS for $3;
  work_memory			ALIAS for $4;
  maint_work_mem		ALIAS for $5;
  insertz			ALIAS for $6;
  updatez			ALIAS for $7;
  deletez			ALIAS for $8;
  idx_scan			ALIAS for $9;
  seq_scan			ALIAS for $10;
  commitz			ALIAS for $11;
  rollbackz			ALIAS for $12;
  dsk_reads			ALIAS for $13;
  mem_reads			ALIAS for $14;
  pct_memory_hits		ALIAS for $15;
  database_sz			ALIAS for $16;

  insert_stmt			text;

BEGIN 

insert_stmt := 'insert into perf_stats_database values (\'' 
  || database_name || '\', \''
  || to_char(updated_time,'YYYY-MM-DD HH24:MI:SS.MS') || '\', ' 
  || ltrim(to_char(shared_buffers,'999999999999999999')) || ', '
  || ltrim(to_char(work_memory,'999999999999999999')) || ', '
  || ltrim(to_char(maint_work_mem,'999999999999999999')) || ', '
  || ltrim(to_char(insertz,'999999999999999999')) || ', '
  || ltrim(to_char(updatez,'999999999999999999')) || ', '
  || ltrim(to_char(deletez,'999999999999999999')) || ', '
  || ltrim(to_char(idx_scan,'999999999999999999')) || ', '
  || ltrim(to_char(seq_scan,'999999999999999999')) || ', '
  || ltrim(to_char(commitz,'999999999999999999')) || ', '
  || ltrim(to_char(rollbackz,'999999999999999999')) || ', '
  || ltrim(to_char(dsk_reads,'999999999999999999')) || ', ' 
  || ltrim(to_char(mem_reads,'999999999999999999')) || ', '
  || ltrim(to_char(pct_memory_hits,'9999D9999')) || ', \''
  || database_sz || '\')';

RETURN insert_stmt;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


---------------------------------- insert_perf_obj() ----------------------------------

CREATE OR REPLACE FUNCTION insert_perf_obj("varchar", "timestamp", "varchar", "varchar", "varchar", int8, int8, "numeric", int8, int8, "numeric", int8, int8, int8, int8, int8, int8, int8, int8, "numeric", "numeric", "numeric", "numeric", int8, int8, int8, int2)
  RETURNS text AS
$BODY$

DECLARE
  database_name			ALIAS for $1;
  updated_time			ALIAS for $2;
  obj_name			ALIAS for $3;
  obj_name1			ALIAS for $4;
  obj_type			ALIAS for $5;
  dsk_reads			ALIAS for $6;
  mem_reads			ALIAS for $7;
  pct_memory_hits		ALIAS for $8;
  idx_scan			ALIAS for $9;
  seq_scan			ALIAS for $10;
  pct_index_hits		ALIAS for $11;
  insertz			ALIAS for $12;
  updatez			ALIAS for $13;
  deletez			ALIAS for $14;
  net_kernal_reads		ALIAS for $15;
  table_len			ALIAS for $16;
  tuple_len			ALIAS for $17;
  dead_tuple_len		ALIAS for $18;
  free_space			ALIAS for $19;
  tuple_pct			ALIAS for $20;
  dead_tuple_pct		ALIAS for $21;
  free_pct			ALIAS for $22;
  system_pct			ALIAS for $23;
  pagez				ALIAS for $24;
  bytez				ALIAS for $25;
  rowz				ALIAS for $26;
  columnz			ALIAS for $27;

  insert_stmt			text;

BEGIN 

insert_stmt := 'insert into perf_stats_objects values(\'' 
  || database_name || '\', \''
  || to_char(updated_time,'YYYY-MM-DD HH24:MI:SS.MS') || '\', \'' 
  || obj_name || '\', \'' || obj_name1 || '\', \'' || obj_type || '\', '
  || ltrim(to_char(dsk_reads,'999999999999999999')) || ', ' 
  || ltrim(to_char(mem_reads,'999999999999999999')) || ', '
  || ltrim(to_char(pct_memory_hits,'9999D9999')) || ', '
  || ltrim(to_char(idx_scan,'999999999999999999')) || ', '
  || ltrim(to_char(seq_scan,'999999999999999999')) || ', '
  || ltrim(to_char(pct_index_hits,'9999D9999')) || ', '
  || ltrim(to_char(insertz,'999999999999999999')) || ', '
  || ltrim(to_char(updatez,'999999999999999999')) || ', '
  || ltrim(to_char(deletez,'999999999999999999')) || ', '
  || ltrim(to_char(net_kernal_reads,'999999999999999999')) || ', '
  || ltrim(to_char(table_len,'999999999999999999')) || ', '
  || ltrim(to_char(tuple_len,'999999999999999999')) || ', '
  || ltrim(to_char(dead_tuple_len,'999999999999999999')) || ', '
  || ltrim(to_char(free_space,'999999999999999999')) || ', '
  || ltrim(to_char(tuple_pct,'9999D9999')) || ', '
  || ltrim(to_char(dead_tuple_pct,'9999D9999')) || ', '
  || ltrim(to_char(free_pct,'9999D9999')) || ', '
  || ltrim(to_char(system_pct,'9999D9999')) || ', '
  || ltrim(to_char(pagez,'999999999999999999')) || ', '
  || ltrim(to_char(bytez,'999999999999999999')) || ', '
  || ltrim(to_char(rowz,'999999999999999999')) || ', '
  || ltrim(to_char(columnz,'999999999999999999')) || ')';

RETURN insert_stmt;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


--------------------------------- pg_database_size() ----------------------------------

CREATE OR REPLACE FUNCTION pg_database_size(oid)
  RETURNS int8 AS
'$libdir/dbsize', 'pg_database_size'
  LANGUAGE 'c' VOLATILE STRICT;


---------------------------------- pg_size_pretty() ------------------------------------

CREATE OR REPLACE FUNCTION pg_size_pretty(int8)
  RETURNS text AS
'$libdir/dbsize', 'pg_size_pretty'
  LANGUAGE 'c' VOLATILE STRICT;


------------------------------------ pgstattuple() -------------------------------------

CREATE OR REPLACE FUNCTION pgstattuple(oid)
  RETURNS pgstattuple_type AS
'$libdir/pgstattuple', 'pgstattuplebyid'
  LANGUAGE 'c' VOLATILE STRICT;

CREATE OR REPLACE FUNCTION pgstattuple(text)
  RETURNS pgstattuple_type AS
'$libdir/pgstattuple', 'pgstattuple'
  LANGUAGE 'c' VOLATILE STRICT;


---------------------------------- update_perf_obj() ----------------------------------

CREATE OR REPLACE FUNCTION update_perf_obj(int8, int8, int8, int8, "varchar", "timestamp", "varchar", "varchar")
  RETURNS text AS
$BODY$

DECLARE
  pagez				ALIAS for $1;
  bytesz			ALIAS for $2;
  rowz				ALIAS for $3;
  columnz			ALIAS for $4;
  database_name			ALIAS for $5;
  updated_time			ALIAS for $6;
  relname			ALIAS for $7;
  relindex			ALIAS for $8;

  update_stmt			text;

BEGIN 

update_stmt := 'update perf_stats_objects set ' 
  || ' pagez = ' || ltrim(to_char(pagez,'999999999999999999')) || ', '
  || ' bytez = ' || ltrim(to_char(bytesz,'999999999999999999')) || ', '
  || ' rowz = ' || ltrim(to_char(rowz,'999999999999999999')) || ', '
  || ' columnz = ' || ltrim(to_char(columnz,'999999999999999999'))
  || ' where database_name = \'' || database_name || '\' and updated_time = \''  
  || to_char(updated_time,'YYYY-MM-DD HH24:MI:SS.MS') 
  || '\' and obj_name = \'' || relname
  || '\' and obj_name1 = \'' || relindex || '\'';

RETURN update_stmt;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


-------------------------------- analyze_performance() --------------------------------

CREATE OR REPLACE FUNCTION analyze_performance("varchar", "varchar", "varchar", "varchar", "varchar")
  RETURNS "varchar" AS
$BODY$

-- sample command line: select * from analyze_performance('report','','','','')

-- Configuration file settings of PostgreSQL database needed by this function
-- (uses on the following config values from pg_settings):
--      'log_destination' 		= stderr
--      'redirect_stderr' 		= true
--      'log_directory'   		= <your choice>
--      'log_filename'    		= <not null>
--      'log_min_error_statement'	= debug1 
--      'log_min_duration_statement'	= <your choice, 60 recommended>
--      'debug_print_plan'		= <your choice, true or false>
--      'debug_pretty_print'		= <your choice, true or false>
--      'log_statement'			= mod
--      'stats_start_collector'		= true (on)
--      'stats_reset_on_server_start'	= true (on) 
--      'stats_command_string'		= true (on)
--      'stats_row_level'		= true (on)
--      'stats_block_level'		= true (on)

DECLARE
  command_line		ALIAS for $1; -- 'report'
    -- NOTE: (1) the following command line arguments are settable via 'start'
    --       (2) all of following arguments are used by 'report'
    --       (3) all run-time arguments are reset to system defaults on 'stop'
  command_parm1		ALIAS FOR $2; -- defines ms for logging of long-run queries
  command_parm2		ALIAS FOR $3; -- how to print query: 'plan' or 'pretty'
  command_parm3		ALIAS for $4; -- reset stats on restart: 'Y' or 'N'
  command_parm4		ALIAS for $5; -- rpt results to monitor: 'Y' or 'N'(dflt)

  -- record areas
  config_settings	RECORD;
  database_hit_stats	RECORD; -- varchar, int4, int4, int4, int4, numeric
  pg_class_hold		RECORD; -- varchar, varchar, int4, int4, int4, int2
  statio_user_indexes	RECORD; -- varchar, bigint, bigint, numeric
  statio_user_tables	RECORD; -- varchar, varchar, bigint, bigint, numeric
  stat_tuple		RECORD; -- int4, int4, int4, flt4, int4, int4, flt4, int4, 
                                --   int4
                                -- table_len          = phys length of tbl (bytes)
                                -- tuple_count        = number live tuples (rows)
                                -- tuple_len          = totl of row length (bytes)
                                -- tuple_percent      = live rows % of [table_len]
                                -- dead_tuple_count   = number of dead rows
                                -- dead_tuple_len     = totl dead row length (bytes)
                                -- dead_tuple_percent = dead rows % of [table_len]
                                -- free_space         = free space in bytes
                                -- free_percent       = free space % of [table_len]
  stat_user_tables	RECORD; -- varchar, bigint, bigint, numeric, bigint, bigint, 
				--   bigint
  table_view		RECORD; -- integer, varchar

  -- variables
  -- for configuration parms set at boot-up, with defaults
  ws_log_directory		varchar;
  ws_log_filename		varchar;
  -- for configuration parms can be set at run time, with defaults
  ws_log_min_duration_statement varchar := '250';    -- defines slow query ms or -1

  -- indicator variables
  no				char(1) := 'N';
  yes				char(1) := 'Y';

  -- variables
  main_command			varchar;
  print_parse			varchar := 'false';
  print_plan			varchar := 'false';
  print_pretty			varchar := 'false';
  print_rewritten		varchar := 'false';
  sql_insert1			text; -- for inserting tbl info into perf_stats_objects
  sql_insert2			text; -- for inserting idx info into perf_stats_objects
  sql_insert3			text; -- for inserting db info into perf_stats_database
  sql_update1			text; -- for updating tbl info into perf_stats_objects
  ws_blocks_fetched		bigint  := 0;
  ws_blocks_fetched_accum	bigint  := 0;
  ws_blocks_hit			bigint  := 0;
  ws_blocks_hit_accum		bigint  := 0;
  ws_database			varchar := 'our database';
  ws_database_oid		integer;
  ws_database_sz		text;
  ws_deletes_accum		bigint  := 0;
  ws_fatal_error		char(1) := no;
  ws_first_command		char(1);
  ws_first_line			char(1);
  ws_idxname_work		varchar;
  ws_index			char(5) := 'index';
  ws_index_count		bigint  := 0;
  ws_index_scans_accum		bigint  := 0;
  ws_inserts_accum		bigint  := 0;
  ws_maint_work_mem		integer := 0;
  ws_net_kernal_reads		bigint  := 0;
  ws_previous_time		timestamp(3);
  ws_print			char(1);
  ws_record_count		integer := 0;
  ws_schema_oid			integer;
  ws_sequential_scans_accum	bigint  := 0;
  ws_shared_buffers		integer := 0;
  ws_sys_space			float   := 0;
  ws_type			char(5) := 'table';
  ws_updated_time		timestamp(3);
  ws_updates_accum		bigint  := 0;
  ws_work_mem			integer := 0;

BEGIN

if command_line = 'report' then
  NULL;
else
  RAISE NOTICE 'Invalid Command = % % % %', command_line, command_parm1,
    command_parm2, command_parm3;
  RETURN 'Invalid_Command';
end if;
select localtimestamp into ws_updated_time;
ws_previous_time := ws_updated_time - interval '6 months';
select count(*) into ws_record_count 
  from perf_stats_database 
  where updated_time < ws_previous_time;
if ws_record_count > 0 then   -- purge rcds older than 6 months
  delete from perf_stats_database where updated_time < ws_previous_time;
end if;
select count(*) into ws_record_count 
  from perf_stats_objects 
  where updated_time < ws_previous_time;
if ws_record_count > 0 then   -- purge rcds older than 6 months
  delete from perf_stats_objects where updated_time < ws_previous_time;
end if;

-- present performance stats on individual tables & indexes
if command_line = 'report' then
  analyze;
  select oid into ws_database_oid from pg_database where datname = ws_database;
  select oid into ws_schema_oid from pg_namespace where nspname = 'public';
  if ((command_parm4 = yes) OR (command_parm4 = no)) then
    ws_print := command_parm4;
  else
    ws_print := no;
  end if;
  if ws_print = yes then
    RAISE NOTICE '- - - - - - -   Summary Table and Index Statistics   - - - - - - -';
  end if;
  for table_view in 
    select oid, relname::varchar from pg_class 
      where relnamespace = ws_schema_oid and relkind = 'r'
      order by 2
  loop
    -- get statistics on table reads and hits
    if ws_print = yes then
      RAISE NOTICE ' ';
    end if;
    select relname::varchar AS tblname, 
      case
        when (heap_blks_read + toast_blks_read) < 1 then 0
        else coalesce((heap_blks_read + toast_blks_read),0)
      end AS dsk_reads, 
      case
        when (heap_blks_hit + toast_blks_hit) < 1 then 0
        else coalesce((heap_blks_hit + toast_blks_hit),0)
      end AS mem_reads,
      case
        when (heap_blks_read +heap_blks_hit + toast_blks_read + toast_blks_hit) < 1 
          then 0::numeric
        else coalesce((round((((heap_blks_hit + toast_blks_hit) / 
          (heap_blks_read + heap_blks_hit + toast_blks_read + 
          toast_blks_hit)::float) * 100)::numeric,2)),0.00::numeric,2)
      end AS pct_memory_hits
    into statio_user_tables
    from pg_statio_user_tables
    where relname = table_view.relname;
    -- get statistics on table index, types DML activity, and size
    select relname::varchar AS tblname,
      coalesce(seq_scan,0)::int8 AS seq_scan,
      coalesce(idx_scan,0)::int8 AS idx_scan,
      case
        when (idx_scan + seq_scan) = 0 then 0.00::numeric
        else coalesce((round(((idx_scan / (seq_scan + idx_scan)::float) * 100)::numeric,2)),
          0.00::numeric)
      end AS pct_index_hits,
      n_tup_ins::int8 AS insertz, 
      n_tup_upd::int8 AS updatez, 
      n_tup_del::int8 AS deletez
    into stat_user_tables
    from pg_stat_user_tables
    where relname = table_view.relname;
    ws_inserts_accum := ws_inserts_accum + stat_user_tables.insertz;
    ws_updates_accum := ws_updates_accum + stat_user_tables.updatez;
    ws_deletes_accum := ws_deletes_accum + stat_user_tables.deletez;

    -- get statistics on kernal vs memory read efficiency   
    select * into ws_blocks_fetched 
      from pg_stat_get_db_blocks_fetched(table_view.oid);
    ws_blocks_fetched_accum = ws_blocks_fetched_accum + ws_blocks_fetched;
    select * into ws_blocks_hit
      from pg_stat_get_db_blocks_hit(table_view.oid);
    ws_blocks_fetched_accum = ws_blocks_fetched_accum + ws_blocks_fetched;
    ws_blocks_hit_accum = ws_blocks_hit_accum + ws_blocks_hit;
    ws_net_kernal_reads = 0;
    if ws_blocks_fetched > ws_blocks_hit then
      ws_net_kernal_reads = (ws_blocks_fetched - ws_blocks_hit);
    end if;
    ws_index_scans_accum := 
      (ws_index_scans_accum + stat_user_tables.idx_scan);
    ws_sequential_scans_accum := 
      (ws_sequential_scans_accum + stat_user_tables.seq_scan);
    -- report results
    if ws_print = yes then
      RAISE NOTICE 'NAME: %',table_view.relname;
      RAISE NOTICE 'TBL:Inserts    Updates     Deletes      Seq Scans    Idx Scans    Idx Use%';
      RAISE NOTICE 'TBL:%          %           %            %            %           %',
        stat_user_tables.insertz, stat_user_tables.updatez, stat_user_tables.deletez, 
        stat_user_tables.seq_scan, stat_user_tables.idx_scan, stat_user_tables.pct_index_hits;
      RAISE NOTICE '                             KrnlReads  DskReads   MemReads  %MemHits';
      RAISE NOTICE '                             %          %          %          %', 
        ws_net_kernal_reads, statio_user_tables.dsk_reads, 
        statio_user_tables.mem_reads, statio_user_tables.pct_memory_hits;
    end if;
    -- get statistics on adequacy of vacuuming
    select * into stat_tuple from pgstattuple(table_view.oid);
    ws_sys_space := (100.00 - (stat_tuple.tuple_percent + 
      stat_tuple.dead_tuple_percent + stat_tuple.free_percent));
    if ws_print = yes then
      RAISE NOTICE '               Vacuuming Adequacy                    Percentages';
      RAISE NOTICE 'TblLnBytes LiveTupls   DeadTupls   FreeSpBytes  Live  Dead  Free  Sys';
      RAISE NOTICE '%          %           %           %           %     %     %     %',
        stat_tuple.table_len, stat_tuple.tuple_len, stat_tuple.dead_tuple_len,
        stat_tuple.free_space, stat_tuple.tuple_percent, 
        stat_tuple.dead_tuple_percent, stat_tuple.free_percent, ws_sys_space;
    end if;
    sql_insert1 := insert_perf_obj(ws_database::varchar,
      ws_updated_time::timestamp,
      table_view.relname::varchar,
      table_view.relname::varchar,
      'table'::varchar,
      statio_user_tables.dsk_reads::int8,
      statio_user_tables.mem_reads::int8,
      statio_user_tables.pct_memory_hits::numeric,
      stat_user_tables.idx_scan::int8,
      stat_user_tables.seq_scan::int8,
      stat_user_tables.pct_index_hits::numeric,
      stat_user_tables.insertz::int8,
      stat_user_tables.updatez::int8,
      stat_user_tables.deletez::int8,
      ws_net_kernal_reads::int8,
      stat_tuple.table_len::int8,
      stat_tuple.tuple_len::int8,
      stat_tuple.dead_tuple_len::int8,
      stat_tuple.free_space::int8,
      stat_tuple.tuple_percent::numeric,
      stat_tuple.dead_tuple_percent::numeric,
      stat_tuple.free_percent::numeric,
      ws_sys_space::numeric,
      0::int8,0::int8,0::int8,0::int2);
    execute sql_insert1;

    -- get statistics on index reads and hits
    ws_index_count = 0;
    select count(*)::int8 into ws_index_count 
      from pg_statio_user_indexes
      where pg_statio_user_indexes.relname = table_view.relname;
    if ws_index_count > 0 then
      select * into statio_user_indexes from pg_statio_user_indexes limit 1;
      ws_first_line := yes;
      for statio_user_indexes in 
        select relname::varchar AS relname,
          indexrelname::varchar AS idxname, 
          idx_blks_read::int8 AS dsk_reads, 
          idx_blks_hit::int8 AS mem_reads,
          case
            when (idx_blks_read + idx_blks_hit) = 0 then 0.00::numeric
            else coalesce((round(((idx_blks_hit / (idx_blks_read + idx_blks_hit)::float) 
              * 100)::numeric,2)), 0.00::numeric,2)
          end AS pct_memory_hits
        from pg_statio_user_indexes
        where relname = table_view.relname
      loop
        ws_idxname_work := statio_user_indexes.idxname;
        if ws_print = yes then
          if ws_first_line = yes then
            ws_first_line = no;
            RAISE NOTICE 'IDX:Idx Name                         DskReads   MemReads  %MemHits';
          end if;
          RAISE NOTICE 'IDX: %                                 %          %          %',
            ws_idxname_work, statio_user_indexes.dsk_reads, 
            statio_user_indexes.mem_reads, statio_user_indexes.pct_memory_hits;
        end if;
        select * into sql_insert2 from insert_perf_obj(ws_database::varchar,
          ws_updated_time::timestamp,
          statio_user_indexes.relname::varchar,
          statio_user_indexes.idxname::varchar,
          'index'::varchar,
          statio_user_indexes.dsk_reads::int8,
          statio_user_indexes.mem_reads::int8,
          statio_user_indexes.pct_memory_hits::numeric,
          0::int8,0::int8,0::numeric,0::int8,0::int8,0::int8,0::int8,
          0::int8,0::int8,0::int8,0::int8,0::numeric,0::numeric,0::numeric,
          0::numeric,0::int8,0::int8,0::int8,0::int2);
        execute sql_insert2;
      end loop;
    end if;
  end loop;
  -- present disk resource usage stats on all table and index objects
  ws_first_line := yes;
  for pg_class_hold in
    select relname::varchar AS relname,
      relname::varchar AS relindex,
      'table'::varchar AS relobj,
      relpages::int8 AS pagez,
      reltuples::int8 AS rowz,
      (relpages * 8192)::int8 AS bytez,
      relnatts::int2 AS columnz
    from pg_class
    where relkind = 'r'
      and relname not like 'sql_%'
      and relname not like 'pg_%'
    union
    select pgc1.relname::varchar, 
      pgc2.relname::varchar AS relindex, 
      'index'::varchar AS relobj, 
      pgc2.relpages::int8 AS pagez, 
      pgc2.reltuples::int8 AS rowz,
      (pgc2.relpages * 8192)::int8 AS bytez,
      pgc2.relnatts::int2 AS columnz
    from pg_index pgi1, pg_class pgc1, pg_class pgc2
    where pgi1.indexrelid = pgc2.oid
      and pgi1.indrelid   = pgc1.oid
      and pgc2.relname not like 'sql_%'
      and pgc2.relname not like 'pg_%'
    order by 1,2,3 desc
  loop
    if ws_print = yes then
      if ws_first_line = yes then
        ws_first_line := no;
        RAISE NOTICE ' ';
        RAISE NOTICE '- - - - - - -      Table / Index Size Statistics      - - - - - - -';
        RAISE NOTICE 'Name                      Type  Cols  Pages Rows      Bytes'; 
      end if;
      RAISE NOTICE '%                         %     %     %     %        %', 
        pg_class_hold.relindex, pg_class_hold.relobj, pg_class_hold.columnz, 
        pg_class_hold.pagez, pg_class_hold.rowz,pg_class_hold.bytez;
    end if;
    sql_update1 := update_perf_obj(pg_class_hold.pagez::int8,
      pg_class_hold.bytez::int8,
      pg_class_hold.rowz::int8,
      pg_class_hold.columnz::int8,
      ws_database::varchar,
      ws_updated_time::timestamp,
      pg_class_hold.relname::varchar,
      pg_class_hold.relindex::varchar);
    execute sql_update1;
  end loop;

  -- present performance stats on database as a whole
  select into database_hit_stats datname::varchar AS database,
    xact_commit AS commitz, xact_rollback AS rollbackz, blks_read AS dsk_reads, 
    blks_hit AS mem_reads,
    case 
      when (blks_read + blks_hit) = 0 then 0
      else round(((blks_hit / (blks_read + blks_hit)::float) * 100)::numeric,2)
    end AS pct_memory_hits
  from pg_stat_database where datname = ws_database;
  select * into ws_database_sz from pg_size_pretty(pg_database_size(ws_database_oid));
  if ws_print = yes then
    RAISE NOTICE ' ';
    RAISE NOTICE '      DATABASE = %     (%)', ws_database, ws_database_sz; 
    RAISE NOTICE '- - - - - - -   Overall Database Summary Statistics   - - - - - - -';
    RAISE NOTICE '          Commits   RllBcks   Inserts   Updates   Deletes'; 
    RAISE NOTICE '          %         %         %         %         %', 
      database_hit_stats.commitz, database_hit_stats.rollbackz, ws_inserts_accum,
      ws_updates_accum, ws_deletes_accum;
    RAISE NOTICE '          KnlReads  SeqScans  DskReads   MemReads % MemHits'; 
    RAISE NOTICE '          %         %         %          %          %',
      ws_net_kernal_reads, ws_sequential_scans_accum, database_hit_stats.dsk_reads, 
      database_hit_stats.mem_reads, database_hit_stats.pct_memory_hits;
  end if;
  sql_insert3 := insert_perf_db(ws_database::varchar,
    ws_updated_time::timestamp,
    ws_shared_buffers::int8,
    ws_work_mem::int8,
    ws_maint_work_mem::int8,
    ws_inserts_accum::int8,
    ws_updates_accum::int8,
    ws_deletes_accum::int8,
    ws_index_scans_accum::int8,
    ws_sequential_scans_accum::int8,
    database_hit_stats.commitz::int8,
    database_hit_stats.rollbackz::int8,
    database_hit_stats.dsk_reads::int8,
    database_hit_stats.mem_reads::int8,
    database_hit_stats.pct_memory_hits::numeric,
    ws_database_sz::varchar);
  execute sql_insert3;
  if ws_print = yes then
    RAISE NOTICE ' ';
  end if;
  select setting into ws_log_directory
    from pg_settings
    where name = 'log_directory';
  select setting into ws_log_directory
    from pg_settings
    where name = 'log_filename';
  select setting into ws_log_min_duration_statement
    from pg_settings
    where name = 'log_min_duration_statement';
  if FOUND then
    if ws_log_min_duration_statement <> '-1' then
      RAISE NOTICE 'Queries taking longer than % ms recorded in %', 
        ws_log_min_duration_statement, ws_log_directory;
    end if;
  end if;
end if;

RETURN 'OK';
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;