====================
Some PostgreSQL tips
====================

:Author: PimenTech
:Contact: root@_NOSPAM_pimentech.net
:Revision: $Revision: 1.17 $
:Date: $Date: 2010-09-09 09:53:24 $
:Tags: postgresql english


-------------
Indexes usage
-------------

Indexes usage, ordered by index size
====================================

::

  SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch, 
         pg_size_pretty(pg_relation_size(indexrelname)) 
  FROM pg_stat_all_indexes WHERE schemaname = 'public' ORDER BY pg_relation_size(indexrelname) DESC;

Sample output :

+----------------+---------------------------------+----------+--------------+---------------+---------------+
|    relname     |          indexrelname           | idx_scan | idx_tup_read | idx_tup_fetch | pg_size_pretty|
+================+=================================+==========+==============+===============+===============+
| bien_recherche | idx_localisation_restreinte_key |   373419 |      2083075 |          5940 | 4944 kB       |
+----------------+---------------------------------+----------+--------------+---------------+---------------+


Index / Sequential scans, by table
==================================

::

  SELECT relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch 
  FROM pg_stat_user_tables ORDER BY seq_scan desc;


Sample output :

+----------------------------------------+-----------+--------------+-----------+---------------+
|                relname                 | seq_scan  | seq_tup_read |  idx_scan | idx_tup_fetch |
+========================================+===========+==============+===========+===============+
| type_bien                              | 86912060  | 598583056    | 22212787  | 22212787      |
+----------------------------------------+-----------+--------------+-----------+---------------+
| profil_poste                           | 65319154  | 4835371896   | 573223920 | 638016937     |
+----------------------------------------+-----------+--------------+-----------+---------------+
| reseau                                 | 63096718  | 931283879    | 161639039 | 103311603     |
+----------------------------------------+-----------+--------------+-----------+---------------+
| commercial                             | 58965966  | 176897028    | 77413667  | 1642011       |
+----------------------------------------+-----------+--------------+-----------+---------------+
| type_activite_agenc                    | 45920212  | 459202100    | 2424098   | 2424098       |
+----------------------------------------+-----------+--------------+-----------+---------------+


GET all tables and columns
==========================

::

  SELECT pg_tables.tablename,columns.column_name 
  FROM pg_tables,information_schema.columns columns 
  WHERE pg_tables.tablename=columns.table_name AND pg_tables.schemaname='public' 
  ORDER by pg_tables.tablename;

--------------
PostgreSQL log
--------------

Longest queries
===============

First you have to activate postgreql queries log with, for example ::
  log_min_duration_statement = 1000
  log_statement = 'all'

Then, to see the 100 longest queries yo can do :

::

  grep "CEST LOG" /var/log/postgresql/postgresql-8.3-main.log|tail -1000|sort -n -k 7 | tail -100