Catégorie : sql

Finding unused index on postgresql 9

Just use this query on you database and find unwanted indexes :   SELECT t.tablename, indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size, pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size, CASE WHEN x.is_unique = 1 THEN 'Y' ELSE 'N' END AS UNIQUE, idx_scan AS number_of_scans,

Publié dans postgresql, sql Tagués avec : , ,

Postgresql : How to add an index on a computed column

Postgresql offers a great way to add computed column, but do you know there is a way to index those columns ?   CREATE INDEX your_table_ix ON your_table USING btree (computed_column_name(your_table.*)   Notice that the computed column has to be

Publié dans postgresql, sql Tagués avec : ,

reset pgagent jobs

Here is the brutal method to reset jobs from a restart fails of postgresql pgAgent delete from pgAgent.pga_jobagent; delete from pgAgent.pga_joblog; delete from pgAgent.pga_jobsteplog;

Publié dans sql Tagués avec : ,

How to install postgresql multicorn extension on debian 7.1

First we need pgxnclient sudo aptitude install pgxnclient Then, for building multicorn, we need several tools sudo aptitude install postgresql-server-dev-all make python-dev python-setuptools Then we can build the pgsql extension sudo pgxn install multicorn –testing –verbose Finally, you can install extension on your

Publié dans debian, linux, sql Tagués avec : , , ,