0

I'm working tuning a scientific database whose associated simulation is very insert intensive (i.e., run for a long time inserting data, execute summary query at the end). One of the tables is starting to cause some problems since the table size is 235 GB with index sizes of 261 GB, and the server only has 800 GB so we would like to free up a bit of space.

Currently there is one foreign key reference (integer data) that is stored as a clustered b-true. This has been good for the summary queries, but likely isn't helping the disk space issues.

Is there a more disk efficient way of storing this foreign key index? Would it make sense to switch over to a hash index instead of the b-tree index?

0

The referencing columns don't need to be indexed at all. If not, then some operations on the referenced table might be extremely slow (e.g. verifying that a row to be deleted on the referenced table has no referencing rows, or if it does than cascading actions to them) but if you never do those operations it wouldn't matter.

3
  • There are some insensitive queries that are run at the end that currently take a couple minutes (each) to run with the b-tree.
    – rjzii
    Nov 29 '20 at 16:23
  • I don't know what "insensitive" means here. If it is just a few of them, letting them do full table scans might be acceptable. Are you talking about index on the referencing column, or on the referenced column? The referenced column must be indexed, and it must be unique, which (I think) means it must be btree.
    – jjanes
    Nov 29 '20 at 17:05
  • "insensitive" means that I hadn't quite woken up when I wrote the message. That should have read intensive. The referenced column is part of a PK tuple (three part) and when I looked through the queries all of the are of the X = Y variety. Only range queries are on a much smaller table that is indexed for them using b-tree.
    – rjzii
    Nov 29 '20 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.