Archive

Archive for the ‘Postgresql’ Category

Find unused indexes in Postgresql

July 28th, 2010 No comments

SELECT *, (pg_relation_size(indexrelname))
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelname) DESC, idx_scan ASC

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]
Categories: Postgresql Tags:

Selecting random single row on large table

July 13th, 2010 No comments

This assumes that id is your unique SERIAL field, and that {table}_id_seq is the field for the next value.

select * from {table}
where id >= (
select floor(random() * (
select last_value
from {table}_id_seq )) ) order by id asc limit 1

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]
Categories: Postgresql Tags: