Mois : octobre 2013

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 : ,