No post body was provided.
Postgres Count Performance
31–34 of 34 posts
Re: Postgres Count Performance
#32Earlier quoted context omitted.
I'm not an expert, this is my layman's understands. It has different isolation levels, some involving snapshots and some not. I think most concurrency issues are (by default) dealt with by locks, which start at row level and can escalate to page and table level (with significant slowdown seen when lock escalation happens in contentious places). But that only has an effect if it's under write.
This is historically correct, but I believe later versions of mssql server now default to their mvcc implementation. I think this switch was circa 2005; not 100% sure. I managed an enterprise applications group that primarily used mssql for data in 2007. I can't recall which mvcc implementation they use. Our servers were MSSQL 2000 and I remember being a bit more than surprised when the DBAs told me the root of the p…
I believe it still has to be chosen, and the MS term is 'Read Committed Snapshot'. Everything is a tradeoff, read committed snapshot makes your tempdb busier (since it is involved in versioning), and balloons up the default size of a row in a table. With RCS you end up adding 14 bytes of overhead to each row, and which otherwise wouldn't be there.
So imagine a table with a single integer column and 1 billion rows. Instead of the 9 byte per row overhead (7 bytes for row metadata and 2 bytes for the page offset), you instead have 23 bytes, plus the 4 bytes to hold the int32. Without RCS on, that row would only have a 9 byte overhead and the rowsize would be 13 bytes.
so 1billion * 27bytes = 25.14 GB 1billion * 13bytes = 12.11 GB
There are some other performance tradeoffs (walking row version values, pagesplits when updating records without rowversions after converting the database, et cetera).
Ultimately, MVCC is great for contention, but it stinks if you're trying to efficiently pack in data.
For an alternative perspective, I sometimes bemoan the size of tables in postgres because of the mandatory overhead and versioning.
Re: Postgres Count Performance
#33> On the other hand count(1) takes an argument and PostgreSQL has to check at every row to see that ts argument, 1, is indeed still not NULL. I expect that's the same for all function calls like this? Surely pg has a concept of constants and doesn't needlessly re-check parameters?
It checks that the first column of each row is not null, that's what the '1' stands for.
Re: Postgres Count Performance
#34Earlier quoted context omitted.
This is historically correct, but I believe later versions of mssql server now default to their mvcc implementation. I think this switch was circa 2005; not 100% sure. I managed an enterprise applications group that primarily used mssql for data in 2007. I can't recall which mvcc implementation they use. Our servers were MSSQL 2000 and I remember being a bit more than surprised when the DBAs told me the root of the p…
I believe it still has to be chosen, and the MS term is 'Read Committed Snapshot'. Everything is a tradeoff, read committed snapshot makes your tempdb busier (since it is involved in versioning), and balloons up the default size of a row in a table. With RCS you end up adding 14 bytes of overhead to each row, and which otherwise wouldn't be there. So imagine a table with a single integer column and 1 billion rows. In…
Agreed. Table bloat is no fun either. Though, I would say under the most common workloads, it's easier to manage table bloat than it is lock escalation... I can pick my battleground for table bloat whereas lock escalation is immediate.
Oracle's MVCC method doesn't have that problem... but then you get the imfamous ORA-01555, "Snapshot too Old" from time to time.
Oh well, no perfect worlds I suppose.