0

What would be an efficient way of preventing sequential scan if data is partitioned by list, when it comes to updating multiple partitions with a condition that is not referring to the partition key?

Pseudo-schema:
My table contains
ID, GroupName, IndividualName, SomeComputedValue, SomeType, UpdatedAt

  • the partition key is GroupName.
  • IndividualName is a search key.
  • SomeComputedValue is the value that will be updated across different groups.
  • SomeType is an additional search parameter

I know Searching via GroupName would be faster, but what if my data end up requiring to search for IndividualName plus SomeType?
Is there anything wrong if I end up requiring to update SomeComputedValue across 10 to 100 partitions, with 1 to 10 million rows each?
Should an index be used or avoided?

My search would be something like IndividualName, SomeType in the update statement. But after that most of the time it will be a GroupName search for select and no more update.

I feel like the index would be more detrimental.

1 Answer 1

1

If the index would only be used for a single UPDATE and never again after that, it is probably a good idea not to create it.

The UPDATE will then run for a long time, but if that is a one-time occurrence and you can make sure that there are no other big data modifications going on at the same time (which could lead to deadlocks or table bloat), that should be acceptable.

After all, creating an index also takes a long time.

4
  • Thank you that cleared things up a lot i find it odd that my system performed worser with than index than without.
    – devede
    Nov 6, 2020 at 6:42
  • i guess now i just need to make sure i can point the specific search to the right partition i guess a reference table before actually searching in pseudo table. some sort of maintaining list to know it exist in the certain partition without forcing it into sequential scan
    – devede
    Nov 6, 2020 at 6:51
  • Can't you simply add a WHERE condition that restricts the UPDATE to a single partition and work partition by partition? Nov 6, 2020 at 6:56
  • yes i can put it basically a where something in('parititonA,'paritionB') thank you for suggesting
    – devede
    Nov 6, 2020 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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