3 ms·

What I was doing is so trivial, you don't really need this information. This was my reference SQL query: select path, count(*) from posts group by path; (I

What I was doing is so trivial, you don't really need this information. This was my reference SQL query: select path, count(*) from posts group by path; (I don't have the exact Rethink query written down, but it was analogous to the SQL version.) You can demonstrate RethinkDB's performance issue with any largeish dataset by trying to group on a single field. The path column in this case has a cardinality of 94, and the whole dataset is about 1 million documents. Some rows are big, some not; each has metadata plus a JSON document. The Postgres table is around 3.1GB (1GB for the main table + a 2.1GB TOAST table). Postgres does a seqscan + hash aggregate in about 1500ms. It's been months since I did this, and I've since deleted RethinkDB and my test dataset.

SamReidHughes 12y ago
Are you sure the analogous RethinkDB query was using the index? Iirc it's not enough just to use the column name (or wasn't, I don't keep up).
- lobster_johnson 12y ago
  It wasn't using an index, but then Postgres wasn't, either. I don't think aggregating via B-tree index is a good idea; aggregation is inherently suited to sequential access. An index is useful only when the selectivity is very low.
  - SamReidHughes 12y ago
    If you wrote your query with group and count, with no index, then there would be problems with the performance. RethinkDB generally does not do query optimization, except in specific ways (mostly about distributing where the query is run), unless that's changed very recently. You can write that query so that it executes with appropriate memory usage with a map and reduce operation.
    - lobster_johnson 12y ago
      Do you think map/reduce would result in performance near what I get from Postgres?
      - rspeer 12y ago
        I haven't used RethinkDB, but I would assume the answer is no. Choosing to use map/reduce is basically a declaration that performance is your lowest priority.
        SamReidHughes 12y ago
        An optimally optimized query by Postgres would be effectively mapping and reducing.
        rspeer 12y ago
        And the point is that the converse is definitely not true. Postgres knows about the structure of your data and where it's located, and can do something reasonably optimal. A generic map/reduce algorithm will have to calculate the same thing as Postgres eventually, but it'll have tons of overhead. (Also, what is with the fad for running map/reduce in the core of the database? Why would this be a good idea? It was a terrible, performance-killing idea on both Mongo and Riak. Is RethinkDB just participating in this fad to be buzzword-compliant?)
        SamReidHughes 12y ago
        There is no relevant knowledge that Postgres has that RethinkDB lacks that lets it evaluate the query more efficiently (besides maybe a row layout with fixed offsets so that it doesn't haven't parse documents, but that's not relevant to the reported problem). A generic map reduce certainly would have more overhead, obviously, but not running-out-of-memory overhead reported above, just the overhead of merging big documents. The reason you run queries in "the core" of a database is because copying all the data outside the database and doing computations there would be far worse.
danielmewes 12y ago
Thanks for the info. I'll look into this. The fact that we are running out of memory suggests that we're doing something wrong for this query.
danielmewes 12y ago
As a second data point: I tried table.groupBy(function (x) { ... }).count() where the function maps the documents into one out of 32 groups (so that's less than your 94, but shouldn't make a giant difference... I just had this database around). Did that on both 1 million and a 25 million document table, and memory usage looked fine and very stable. This was on RethinkDB 2.0, and I might retry that on 1.16 later to see if I can reproduce it there. Do you remember if you had set an explicit cache size back when you were testing RethinkDB?
- lobster_johnson 12y ago
  Cool. Well, the process eventually crashed if I used the defaults. I had to give it a 6GB cache (I think, maybe it was more) for it to return anything. The process would actually allocate that much, too, so it's clear that it was effectively loading everything into memory.

SamReidHughes 12y ago

Are you sure the analogous RethinkDB query was using the index? Iirc it's not enough just to use the column name (or wasn't, I don't keep up).

lobster_johnson 12y ago
It wasn't using an index, but then Postgres wasn't, either. I don't think aggregating via B-tree index is a good idea; aggregation is inherently suited to sequential access. An index is useful only when the selectivity is very low.
- SamReidHughes 12y ago
  If you wrote your query with group and count, with no index, then there would be problems with the performance. RethinkDB generally does not do query optimization, except in specific ways (mostly about distributing where the query is run), unless that's changed very recently. You can write that query so that it executes with appropriate memory usage with a map and reduce operation.
  - lobster_johnson 12y ago
    Do you think map/reduce would result in performance near what I get from Postgres?
    - rspeer 12y ago
      I haven't used RethinkDB, but I would assume the answer is no. Choosing to use map/reduce is basically a declaration that performance is your lowest priority.
      - SamReidHughes 12y ago
        An optimally optimized query by Postgres would be effectively mapping and reducing.
        rspeer 12y ago
        And the point is that the converse is definitely not true. Postgres knows about the structure of your data and where it's located, and can do something reasonably optimal. A generic map/reduce algorithm will have to calculate the same thing as Postgres eventually, but it'll have tons of overhead. (Also, what is with the fad for running map/reduce in the core of the database? Why would this be a good idea? It was a terrible, performance-killing idea on both Mongo and Riak. Is RethinkDB just participating in this fad to be buzzword-compliant?)
        SamReidHughes 12y ago
        There is no relevant knowledge that Postgres has that RethinkDB lacks that lets it evaluate the query more efficiently (besides maybe a row layout with fixed offsets so that it doesn't haven't parse documents, but that's not relevant to the reported problem). A generic map reduce certainly would have more overhead, obviously, but not running-out-of-memory overhead reported above, just the overhead of merging big documents. The reason you run queries in "the core" of a database is because copying all the data outside the database and doing computations there would be far worse.

danielmewes 12y ago

Thanks for the info. I'll look into this. The fact that we are running out of memory suggests that we're doing something wrong for this query.

danielmewes 12y ago

As a second data point: I tried table.groupBy(function (x) { ... }).count() where the function maps the documents into one out of 32 groups (so that's less than your 94, but shouldn't make a giant difference... I just had this database around). Did that on both 1 million and a 25 million document table, and memory usage looked fine and very stable. This was on RethinkDB 2.0, and I might retry that on 1.16 later to see if I can reproduce it there. Do you remember if you had set an explicit cache size back when you were testing RethinkDB?

lobster_johnson 12y ago
Cool. Well, the process eventually crashed if I used the defaults. I had to give it a 6GB cache (I think, maybe it was more) for it to return anything. The process would actually allocate that much, too, so it's clear that it was effectively loading everything into memory.