SELECT schemaname, relname,
n_tup_upd,n_tup_hot_upd,
case when n_tup_upd > 0
then ((n_tup_hot_upd::numeric/n_tup_upd::numeric)*100.0)::numeric(5,2)
else NULL
end as hot_ratio
FROM pg_stat_all_tables;
schemaname | relname | n_tup_upd | n_tup_hot_upd | hot_ratio
------------+---------+-----------+---------------+-----------
public | table1 | 6 | 6 | 100.00
public | table2 | 2551200 | 2549474 | 99.93
Et si l'on souhaite ajouter à l'affichage le réglage actuel du fillfactor, c'est possible. Mais il faut se référer au catalogue système, où pg_class référence le schéma sous relnamespace qui est un OID de la table système pg_namespace, laquelle publie le nom du schéma sous la colonne nspname, retrouvée sous le nom schemaname dans les tables de stats...
SELECT t.schemaname, t.relname, c.reloptions,
t.n_tup_upd, t.n_tup_hot_upd,
case when n_tup_upd > 0
then ((n_tup_hot_upd::numeric/n_tup_upd::numeric)*100.0)::numeric(5,2)
else NULL
end as hot_ratio
FROM pg_stat_all_tables t
join (pg_class c join pg_namespace n on c.relnamespace = n.oid)
on n.nspname = t.schemaname and c.relname = t.relname
schemaname | relname | reloptions | n_tup_upd | n_tup_hot_upd | hot_ratio
------------+---------+-----------------+-----------+---------------+-----------
public | table1 | {fillfactor=50} | 1585920 | 1585246 | 99.96
public | table2 | {fillfactor=50} | 2504880 | 2503154 | 99.93
On dirait que les espoirs théoriques sont plutôt bien vérifiés en pratique... on peut donc continuer de suivre la documentation les yeux fermés^Wgrands ouverts ;)
Merci PostgreSQL !Bonjour,
La version 1.6.0 de pgadmin sous Windows ne permet pas d'utiliser par défaut les outils de sauvegarde et de restauration de PostgreSQL à savoir pg_dump.exe et pg_restore.exe. La solution à ce défaut est de rajouter dans la variable PATH de Windows le chemin d'accès vers le répertoire de pgadmin à savoir par défaut C:\Program Files\pgAdmin III\1.6. Après un redémarrage de votre machine, vous pourrez de nouveau sauvegarder et restaurer vos bases de données.
Merci.
Trouvé sur un des forums, cette astuce qui mérite de figurer dans le cookbook.
Pour supprimer toutes les fonctions d'un schéma, on peut utiliser la commande suivante, en shell :
psql -U MON_USER MA_BASE -t -P format=unaligned -c "\df MON_SCHEMA.*"|awk -F '|' '{if (NF > 0 ) print "drop function "$2"("$4") cascade;";}' | psql -U MON_USER MA_BASE
Le cascade permet de gérer la présence d'une fonction d'agrégation.
Il est utile pour le bon fonctionnement de la commande de mettre le mot de passe de l'utilisateur dans la variable d'environnement PGPASSWORD.
Merci à jxemo pour cette astuce et à Sparky pour la piste.
La requête suivante permet d'afficher la taille des tables de TOAST de votre système, pour celles dont la taille est supérieure à 0 octets. On utilise une sous-requête dans la clause FROM (cf. queries-table-expressions) afin de récupérer les informations brutes voulues, puis on les met en forme dans le SELECT en utilisant entre autres pg_size_pretty.
SELECT a, n, pg_relation_size(t), pg_size_pretty(pg_relation_size(t))FROM (SELECT c.relname, c.reltoastrelid, d.relname
FROM pg_class c JOIN pg_class d ON c.reltoastrelid = d.oid
) as x(a, t, n)WHERE t > 0 and pg_relation_size(t) > 0
ORDER BY 3 DESC;
Et voici un exemple d'utilisation :
psql amarok < /home/dim/PostgreSQL/cookbook/size.toast.sql
a | n | pg_relation_size | pg_size_pretty
------------+---------------+------------------+----------------
pg_rewrite | pg_toast_2618 | 139264 | 136 kB
(1 ligne)
On a besoin parfois d'avoir rapidement la taille sur disque d'une table et le nombre d'enregistrements qu'elle contient. Pour cela, on peut utiliser la fonction pg_total_relation_size('nom de table') qui permet de rapatrier la taille totale de la table sur disque, index inclus. Si on ne veut pas connaître la taille prise par les index, il suffit d'utiliser la fonction pg_relation_size('nom de table'). La soustraction des deux permet de connaître la taille des index!
Tout cela nous permet de réaliser une vue fort sympathique pour l'administrateur de base de données. Attention à lancer un ANALYZE avant tout requêtage de cette vue:
drop view vue_stats;
create view vue_stats as
SELECT
c.relname as nom,
c.reltuples::bigint as tuples,
pg_total_relation_size(c.relname) as volume_total,
pg_relation_size(c.relname) as volume_donnees,
pg_total_relation_size(c.relname)-pg_relation_size(c.relname) as volume_index
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Merci à klando (Cédric) pour cet ajout, fort sympathique: il permet d'avoir les tailles des objets lisibles sous la forme humaine:
drop view vue_stats_pretty ;
create view vue_stats_pretty as
SELECT
nom,
tuples,
pg_size_pretty(volume_total) as volume_total,
pg_size_pretty(volume_donnees) as volume_donnees,
pg_size_pretty(volume_index) as volume_index
FROM vue_stats;
Enjoy!
--
Jean-Paul ARGUDO
http://dalibo.com | http://dalibo.org
Il est possible de trouver le nom de l'objet associé à un OID en passant par la conversion regclass. Par exemple, pour connaître le nom de l'objet dont l'OID est 2613 :
guillaume=# select 2613::regclass;
regclass
----------------
pg_largeobject
(1 ligne)
L'OID 2613 correspond donc à la table système pg_largeobject.