Some PostgreSQL tips

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

Commentaires

Comments

blog comments powered by Disqus