4 ms·

I'm not sure I buy your argument. It is certainly true that with MVCC "there is more than one legitimate answer the question of COUNT", but that's true for any

I'm not sure I buy your argument. It is certainly true that with MVCC "there is more than one legitimate answer the question of COUNT", but that's true for any query, not just COUNT. Given that we can maintain transactional correctness for the data, it's not at all clear to me why the same mechanisms cannot be used to maintain transactional correctness for the metadata.

ralph 14y ago
I agree. Given count(star) is a common need you'd think there would be multiple count(star) results in the metadata for the table, each with a generation associated with it.
- fusiongyro 14y ago
  It's a short, easy query to write. I'm not convinced it's a big need. So, your proposal is basically this: make a metadata table. On every insert or delete from the table, update the metadata table accordingly. MVCC will make sure anybody reading from it will get the right answer. Is that about right?
- jeltz 14y ago
  I do not see how this would be implemented without introducing extra locking which would harm concurrency, which would remove some of the performance advantage of having MVCC. Now I see it could be worth taking that penalty on specified tables, jsut like you do for indexes. And some databases do support this in a more general form: materialized views.
  - fusiongyro 14y ago
    I think you could fake this proposal with triggers. It might be worth seeing what the additional cost is.
    - scotty79 14y ago
      Proper solution (not fake with triggers) should be at most as costly as having partial index on the table.
fusiongyro 14y ago
Yes, but we're not surprised when those other queries have to read the table. For some reason it's surprising with COUNT. I maintain this is not a technical problem so much as an intuition problem. Explain how you're going to maintain this metadata in more depth. If it can be done in an MVCC manner (i.e. without introducing locks) then it should be explored. How's it going to work?
- seunosewa 14y ago
  We can just maintain multiple versions of the row count.
  - fusiongyro 14y ago
    It might be worth seeing what the cost of this would be in practice. I suspect if the answer were this easy the developers would have just done it by now, but you never know.

ralph 14y ago

I agree. Given count(star) is a common need you'd think there would be multiple count(star) results in the metadata for the table, each with a generation associated with it.

fusiongyro 14y ago
It's a short, easy query to write. I'm not convinced it's a big need. So, your proposal is basically this: make a metadata table. On every insert or delete from the table, update the metadata table accordingly. MVCC will make sure anybody reading from it will get the right answer. Is that about right?
jeltz 14y ago
I do not see how this would be implemented without introducing extra locking which would harm concurrency, which would remove some of the performance advantage of having MVCC. Now I see it could be worth taking that penalty on specified tables, jsut like you do for indexes. And some databases do support this in a more general form: materialized views.
- fusiongyro 14y ago
  I think you could fake this proposal with triggers. It might be worth seeing what the additional cost is.
  - scotty79 14y ago
    Proper solution (not fake with triggers) should be at most as costly as having partial index on the table.

fusiongyro 14y ago

Yes, but we're not surprised when those other queries have to read the table. For some reason it's surprising with COUNT. I maintain this is not a technical problem so much as an intuition problem. Explain how you're going to maintain this metadata in more depth. If it can be done in an MVCC manner (i.e. without introducing locks) then it should be explored. How's it going to work?

seunosewa 14y ago
We can just maintain multiple versions of the row count.
- fusiongyro 14y ago
  It might be worth seeing what the cost of this would be in practice. I suspect if the answer were this easy the developers would have just done it by now, but you never know.