Real Software Forums

I have a table currently that adds up all the parts I have used to make products. I have a qty figure in there and I sum that. However the problem is at the moment I have to consume qtys of parts each time I build a product, but i would prefer to consume the parts 1 at a time. That would create many more records so I would need to sum a much larger data set to make that work because instead of a qty of 100 parts consumed, there would not be 100 separate records. But it would me closer to whats happening when I build products so it would be better from an operations point of view.

How many records can PosgreSQL sum without slowing down? I could wind up with millions of records if I use a separate record for each one.

Only one software tool needs those summaries, but it has to do it at launch, so the longer the sum takes the longer the tool will take to launch. I have a few thousand part types as well so I am doing a sum for each part type.

Grant

I'm getting 1.6s to sum 1.2m rows on a Mac mini server with an SSD. This is a bigint column, not a regular 4-byte int column.

Your times will vary depending on the CPU, disk, server load, optimizations, and network latency.

Not really sure how you're dong this
I'm assuming that each "product" has entries for "used 1 part of type X" and it if requires a hundred then there's 100 record like that ?
And what you're doing is summing up all the parts that have been used ?

This is a one liner as far as I can tell from what little you've given

select part, sum(qty) from partsUsed group by part

or something like that

with proper indexes this should go very quickly

However it MAY also be worth having a table that is updated each time a record is added to this table that just keeps the running totals so you Don't have to do sums across millions of records (just dont write it in client code - do it as a trigger on the server side of things so it works regardless of what adds a record to the parts used list)

Thanks and I did have running totals tables but for some reason I found they were becoming inaccurate and I could never work out why. I used transactions so that any time I updated or added a record to the main table I also had to do the sum table or it would not do both. I also made sure I sent an update to the summing table to do the update based on what it had so if more than one client did it at the same time it should be ok. So I never worked out why I was drifting out.

So I run a replace into each day and updated all the tables based on a sum so even though that took a little time, it was ok because it was run at night time. But when I moved the database to Posgresql I could not do replace into anymore and never managed to make anything else work. But I found postgesql was so fast I did not need to using the summing ables so I just coded this out.

But perhaps its time to do it again. My problem is how to do it right so I don't drift out and become inaccurate?

Grant