6 ms·

The counting seems really slow compared to MSSQL? I'm not familiar with the reference hardware used here, but just running a similar test on my desktop with sql

The counting seems really slow compared to MSSQL? I'm not familiar with the reference hardware used here, but just running a similar test on my desktop with sql server it can count distinct a million strings in under a second (with parallelisation, around 2.5s cpu time). Or am I missing the fact that these benchmarks are run on a reference spec which is comparatively old?

sb8244 10y ago
I'm not familiar with mssql, does it use mvcc? That is the reason PG counting is slow (really slow)
- throwawayReply 10y ago
  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.
  - sbuttgereit 10y ago
    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 performance problems we had were due to lock escalation; having come from the Oracle and PostgreSQL worlds, I was naive enough to have thought that lock escalation implementations like this were historical curiosities rather than something I'd actually run into... live and learn I guess.
    - nickpeterson 10y ago
      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.
      - sbuttgereit 10y ago
        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.
sb8244 10y ago
I'm not familiar with mssql, does it use mvcc? That is the reason PG counting is slow (really slow)

sb8244 10y ago

I'm not familiar with mssql, does it use mvcc? That is the reason PG counting is slow (really slow)

throwawayReply 10y ago
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.
- sbuttgereit 10y ago
  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 performance problems we had were due to lock escalation; having come from the Oracle and PostgreSQL worlds, I was naive enough to have thought that lock escalation implementations like this were historical curiosities rather than something I'd actually run into... live and learn I guess.
  - nickpeterson 10y ago
    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.
    - sbuttgereit 10y ago
      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.

sb8244 10y ago

I'm not familiar with mssql, does it use mvcc? That is the reason PG counting is slow (really slow)