########################################################################################################### PGStatspack version 0.3 by frits.hoogland@interaccess.nl ########################################################################################################### Snapshot information Begin snapshot : snapid | ts | description --------+----------------------------+------------- 1 | 2009-10-18 21:39:32.364913 | scan-before (1 row) End snapshot : snapid | ts | description --------+----------------------------+------------- 2 | 2009-10-18 21:40:08.695257 | scan-after (1 row) Seconds in snapshot: 36.3303440213203 Database version version -------------------------------------------------------------------------------------------- PostgreSQL 8.3.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.3) (1 row) current_database | dbsize ------------------+-------- fuzzy-new | 790 MB (1 row) Database statistics database | tps | hitrate | lio_ps | pio_ps | rollbk_ps ----------------+------+---------+----------+----------+----------- pgstatspack | 0.22 | 65.00 | 62244.63 | 21195.78 | 0.00 postgres | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 template0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 template1 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 (4 rows) Top 20 tables ordered by high table to index read ratio table | system_read_pct | table_read_pct | index_read_pct -------------------------+-----------------+----------------+---------------- public.pgstatspack_test | 99 | 100 | 0 pg_catalog.pg_am | 0 | 100 | 0 pg_catalog.pg_index | 0 | 98 | 1 pg_catalog.pg_class | 0 | 98 | 1 pg_catalog.pg_database | 0 | 95 | 4 pg_catalog.pg_namespace | 0 | 88 | 11 pg_catalog.pg_type | 0 | 0 | 100 pg_catalog.pg_rewrite | 0 | 0 | 100 pg_catalog.pg_authid | 0 | 0 | 100 pg_catalog.pg_aggregate | 0 | 0 | 100 pg_catalog.pg_trigger | 0 | 0 | 100 pg_catalog.pg_amproc | 0 | 0 | 100 pg_catalog.pg_operator | 0 | 0 | 100 pg_catalog.pg_opclass | 0 | 0 | 100 pg_toast.pg_toast_2618 | 0 | 0 | 100 pg_catalog.pg_amop | 0 | 0 | 100 pg_catalog.pg_proc | 0 | 0 | 100 pg_catalog.pg_attribute | 0 | 0 | 100 pg_catalog.pg_statistic | 0 | 0 | 100 pg_catalog.pg_cast | 0 | 0 | 100 (20 rows) Top 20 tables ordered by inserts table | table_inserts --------------------------------------------+--------------- public.pgstatspack_indexes | 168 public.pgstatspack_tables | 129 public.pgstatspack_settings | 35 pg_catalog.pg_statistic | 25 public.pgstatspack_sequences | 24 public.pgstatspack_database | 11 public.pgstatspack_snap | 1 information_schema.sql_features | 0 information_schema.sql_implementation_info | 0 information_schema.sql_languages | 0 information_schema.sql_packages | 0 information_schema.sql_parts | 0 information_schema.sql_sizing | 0 information_schema.sql_sizing_profiles | 0 pg_catalog.pg_aggregate | 0 pg_catalog.pg_am | 0 pg_catalog.pg_amop | 0 pg_catalog.pg_amproc | 0 pg_catalog.pg_attrdef | 0 pg_catalog.pg_attribute | 0 (20 rows) Top 20 tables ordered by updates table | table_updates --------------------------------------------+--------------- information_schema.sql_features | 0 information_schema.sql_implementation_info | 0 information_schema.sql_languages | 0 information_schema.sql_packages | 0 information_schema.sql_parts | 0 information_schema.sql_sizing | 0 information_schema.sql_sizing_profiles | 0 pg_catalog.pg_aggregate | 0 pg_catalog.pg_am | 0 pg_catalog.pg_amop | 0 pg_catalog.pg_amproc | 0 pg_catalog.pg_attrdef | 0 pg_catalog.pg_attribute | 0 pg_catalog.pg_authid | 0 pg_catalog.pg_auth_members | 0 pg_catalog.pg_autovacuum | 0 pg_catalog.pg_cast | 0 pg_catalog.pg_class | 0 pg_catalog.pg_constraint | 0 pg_catalog.pg_conversion | 0 (20 rows) Top 20 tables ordered by deletes table | table_deletes --------------------------------------------+--------------- information_schema.sql_features | 0 information_schema.sql_implementation_info | 0 information_schema.sql_languages | 0 information_schema.sql_packages | 0 information_schema.sql_parts | 0 information_schema.sql_sizing | 0 information_schema.sql_sizing_profiles | 0 pg_catalog.pg_aggregate | 0 pg_catalog.pg_am | 0 pg_catalog.pg_amop | 0 pg_catalog.pg_amproc | 0 pg_catalog.pg_attrdef | 0 pg_catalog.pg_attribute | 0 pg_catalog.pg_authid | 0 pg_catalog.pg_auth_members | 0 pg_catalog.pg_autovacuum | 0 pg_catalog.pg_cast | 0 pg_catalog.pg_class | 0 pg_catalog.pg_constraint | 0 pg_catalog.pg_conversion | 0 (20 rows) Tables ordered by percentage of tuples scanned table | rows_read_pct | tab_hitrate | idx_hitrate | tab_read | tab_hit | idx_read | idx_hit -------------------------+---------------+-------------+-------------+----------+---------+----------+--------- public.pgstatspack_test | 99 | 65 | 0 | 769837 | 1489737 | 0 | 0 pg_catalog.pg_index | 0 | 72 | 50 | 5 | 16 | 6 | 7 pg_catalog.pg_amop | 0 | 82 | 71 | 3 | 19 | 8 | 23 pg_catalog.pg_namespace | 0 | 70 | 50 | 2 | 7 | 2 | 3 pg_catalog.pg_proc | 0 | 65 | 88 | 14 | 28 | 9 | 75 pg_catalog.pg_attribute | 0 | 82 | 94 | 18 | 92 | 11 | 204 pg_catalog.pg_database | 0 | 25 | 0 | 2 | 1 | 2 | 0 pg_catalog.pg_am | 0 | 0 | 0 | 1 | 0 | 0 | 0 pg_catalog.pg_statistic | 0 | 71 | 94 | 10 | 28 | 6 | 119 pg_catalog.pg_class | 0 | 88 | 91 | 18 | 146 | 10 | 113 pg_catalog.pg_cast | 0 | 71 | 93 | 3 | 10 | 3 | 58 pg_catalog.pg_type | 0 | 63 | 78 | 7 | 14 | 10 | 41 pg_catalog.pg_rewrite | 0 | 28 | 71 | 4 | 2 | 1 | 5 pg_catalog.pg_authid | 0 | 0 | 0 | 1 | 0 | 2 | 0 pg_catalog.pg_aggregate | 0 | 0 | 0 | 1 | 0 | 2 | 0 pg_catalog.pg_trigger | 0 | 50 | 44 | 1 | 2 | 4 | 4 pg_catalog.pg_amproc | 0 | 57 | 50 | 2 | 4 | 3 | 4 pg_catalog.pg_operator | 0 | 52 | 76 | 10 | 12 | 8 | 29 pg_catalog.pg_opclass | 0 | 78 | 36 | 3 | 15 | 6 | 4 pg_toast.pg_toast_2618 | 0 | 42 | 50 | 3 | 3 | 2 | 3 (20 rows) Indexes ordered by scans index | table | scans | tup_read | tup_fetch | idx_blks_read | idx_blks_hit --------------------------------------------+--------------------------+-------+----------+-----------+---------------+-------------- pg_catalog.pg_autovacuum_vacrelid_index | pg_catalog.pg_autovacuum | 131 | 0 | 0 | 1 | 130 pg_catalog.pg_attribute_relid_attnum_index | pg_catalog.pg_attribute | 107 | 243 | 243 | 11 | 204 pg_catalog.pg_cast_source_target_index | pg_catalog.pg_cast | 60 | 13 | 13 | 3 | 58 pg_catalog.pg_class_oid_index | pg_catalog.pg_class | 41 | 41 | 41 | 7 | 76 pg_catalog.pg_proc_oid_index | pg_catalog.pg_proc | 39 | 39 | 39 | 6 | 72 pg_catalog.pg_statistic_relid_att_index | pg_catalog.pg_statistic | 37 | 9 | 9 | 6 | 119 pg_catalog.pg_class_relname_nsp_index | pg_catalog.pg_class | 20 | 13 | 13 | 3 | 37 pg_catalog.pg_type_oid_index | pg_catalog.pg_type | 19 | 18 | 18 | 6 | 33 pg_catalog.pg_operator_oid_index | pg_catalog.pg_operator | 13 | 13 | 13 | 5 | 22 pg_catalog.pg_amop_fam_strat_index | pg_catalog.pg_amop | 10 | 18 | 18 | 5 | 16 pg_catalog.pg_amop_opr_fam_index | pg_catalog.pg_amop | 9 | 12 | 12 | 3 | 7 pg_catalog.pg_index_indrelid_index | pg_catalog.pg_index | 7 | 9 | 9 | 3 | 5 pg_catalog.pg_amproc_fam_proc_index | pg_catalog.pg_amproc | 6 | 6 | 6 | 3 | 4 pg_catalog.pg_type_typname_nsp_index | pg_catalog.pg_type | 6 | 3 | 3 | 4 | 8 pg_catalog.pg_rewrite_rel_rulename_index | pg_catalog.pg_rewrite | 6 | 6 | 6 | 1 | 5 pg_catalog.pg_operator_oprname_l_r_n_index | pg_catalog.pg_operator | 5 | 12 | 12 | 3 | 7 pg_catalog.pg_index_indexrelid_index | pg_catalog.pg_index | 4 | 4 | 4 | 3 | 2 pg_toast.pg_toast_2618_index | pg_toast.pg_toast_2618 | 4 | 12 | 12 | 2 | 3 pg_catalog.pg_namespace_oid_index | pg_catalog.pg_namespace | 4 | 4 | 4 | 2 | 3 pg_catalog.pg_opclass_oid_index | pg_catalog.pg_opclass | 4 | 4 | 4 | 3 | 2 pg_catalog.pg_proc_proname_args_nsp_index | pg_catalog.pg_proc | 3 | 3 | 3 | 3 | 3 pg_catalog.pg_trigger_tgrelid_tgname_index | pg_catalog.pg_trigger | 3 | 3 | 3 | 4 | 4 pg_catalog.pg_opclass_am_name_nsp_index | pg_catalog.pg_opclass | 2 | 80 | 80 | 3 | 2 pg_catalog.pg_database_oid_index | pg_catalog.pg_database | 1 | 1 | 1 | 2 | 0 pg_catalog.pg_authid_oid_index | pg_catalog.pg_authid | 1 | 1 | 1 | 2 | 0 pg_catalog.pg_aggregate_fnoid_index | pg_catalog.pg_aggregate | 1 | 1 | 1 | 2 | 0 (26 rows) Sequences ordered by blks_read schema | name | blks_read | blks_hit --------+------+-----------+---------- (0 rows) Parameters name | start_setting | stop_setting | source ------------------------------+---------------------------------------------------+---------------------------------------------------+---------------------- autovacuum | on | on | configuration file autovacuum_vacuum_cost_delay | 10 | 10 | configuration file autovacuum_vacuum_cost_limit | 500 | 500 | configuration file checkpoint_segments | 12 | 12 | configuration file config_file | /home/tomas/postgres/8.3.6/data/postgresql.conf | /home/tomas/postgres/8.3.6/data/postgresql.conf | override data_directory | /home/tomas/postgres/8.3.6/data | /home/tomas/postgres/8.3.6/data | override DateStyle | ISO, DMY | ISO, DMY | configuration file default_text_search_config | pg_catalog.simple | pg_catalog.simple | configuration file effective_cache_size | 131072 | 131072 | configuration file enable_bitmapscan | off | off | session enable_indexscan | off | off | session hba_file | /home/tomas/postgres/8.3.6/data/pg_hba.conf | /home/tomas/postgres/8.3.6/data/pg_hba.conf | override ident_file | /home/tomas/postgres/8.3.6/data/pg_ident.conf | /home/tomas/postgres/8.3.6/data/pg_ident.conf | override lc_collate | cs_CZ.UTF-8 | cs_CZ.UTF-8 | override lc_ctype | cs_CZ.UTF-8 | cs_CZ.UTF-8 | override lc_messages | cs_CZ.UTF-8 | cs_CZ.UTF-8 | configuration file lc_monetary | cs_CZ.UTF-8 | cs_CZ.UTF-8 | configuration file lc_numeric | cs_CZ.UTF-8 | cs_CZ.UTF-8 | configuration file lc_time | cs_CZ.UTF-8 | cs_CZ.UTF-8 | configuration file log_line_prefix | %t %d | %t %d | configuration file log_statement_stats | on | on | configuration file log_timezone | Europe/Prague | Europe/Prague | command line max_connections | 100 | 100 | configuration file max_fsm_pages | 153600 | 153600 | configuration file max_stack_depth | 2048 | 2048 | environment variable server_encoding | UTF8 | UTF8 | override shared_buffers | 3072 | 3072 | configuration file TimeZone | Europe/Prague | Europe/Prague | command line timezone_abbreviations | Default | Default | command line track_activities | on | on | configuration file track_counts | on | on | configuration file transaction_isolation | read committed | read committed | override transaction_read_only | off | off | override update_process_title | on | on | configuration file wal_buffers | 8 | 8 | configuration file (35 rows)