Re: Need Advice In Improving Database Disc Usage

On Tue, Feb 2, 2010 at 11:00 PM, Yan Cheng Cheok <yccheok(at)yahoo(dot)com> wrote:
> (1) Is there any need for me to create idx_fk_measurement_type_id and idx_fk_measurement_unit_id
>
> The above 2 are the index for measurement's foreign key. However, these 2 foreign key are only used in JOIN statement, but never used in WHERE statement. So, if I remove the 2 index, will I still achieve the same query performance?

For the following situation

create table a (
a_id PRIMARY KEY,
baz INTEGER
);

create table b (
b_id PRIMARY KEY,
a_id INTEGER REFERENCES a(a_id) ON DELETE CASCADE
);

you only want to have an index on b.a_id IF you delete a_id from table
a regularly. That is, when the reverse of the FK is tested during the
delete, you want it to be fast to find and delete the referring rows
as well (the CASCADE). If you never or rarely do such a delete, and
can live with doing a sequence scan on table b for such deletes, then
you don't need to waste space and time with the index.

>
> (2) How can I increase the compression ratio for my database?

Whatever compression happens is automatic.

Sound like you may need to investigate how often your indexes are used
and perhaps prune some of them. That and be sure you run vacuum
regularly to keep the tables from bloating too much. Have you done
bulk deletes and re-loads of your DB?