On Tue, Feb 2, 2010 at 11:00 PM, Yan Cheng Cheok <yccheok@xxxxxxxxx> 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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
