4 ms·

i got downvoted in another thread for saying nosql has a lot of advantages in reducing work like not having to worry about carefully creating indexes when joini

i got downvoted in another thread for saying nosql has a lot of advantages in reducing work like not having to worry about carefully creating indexes when joining billion row tables. someone replied you dont need indexes. i replied I dont know how you plan on running joins on billion row tables in postgress without indexes. got downvoted again. I bet they are still waiting for that join....

parasubvert 9y ago
Whether you need indexes really depends on what you’re trying to do. Full table scans are more efficient if you are doing analytics on most of those rows in a time series. If you need 1 row in a billion, yes, you need an index.
- autokad 9y ago
  if your doing a join on two size N tables, your O(n) cost for each record is N, thus you are doing N^2 lookups. basically reading the entire DB into memory each record.
  - parasubvert 9y ago
    No. Worst case a nested join of two tables M and N would be O(MN). But most real databases would have a merge or hash join which could bring this down to O(M+N) if the tables are already stored in a sorted indexed order. It is also rare the cardinality of these databases will be similar in an analytical query - usually you have one time series “fact table” and a bunch of dimensions to enrich that data. If I’m doing analytics on a time series this gets even better with partition pruning and hash joins or bitmap indexes. And if I have a columnar database, that blows up this whole complexity argument. My point is that, layout and indexes should never be assumed to be one size fits all. Keep in mind if I’m doing analytics I want to bring my time series data into memory at least as a stream, as I need to calculate / filter / transform the records. Not everything is about rendering a page on a website.
orf 9y ago
If you're dealing with billion row tables and using mongodb you are way out of your league. Good luck.
- autokad 9y ago
  mongodb is not the only nosql db :) I use hive/hbase
acdha 9y ago
From the sounds of it, you were downvoted for not recognizing this as a textbook scenario with decades of practice and engine improvements devoted to allowing you to balance query performance against your server budget. Interestingly enough, many of those techniques are common in the NoSQL world as well — a billion records is enough to require thinking about data flow anywhere — but the difference is that you have to deploy them more frequently.

parasubvert 9y ago

Whether you need indexes really depends on what you’re trying to do. Full table scans are more efficient if you are doing analytics on most of those rows in a time series. If you need 1 row in a billion, yes, you need an index.

autokad 9y ago
if your doing a join on two size N tables, your O(n) cost for each record is N, thus you are doing N^2 lookups. basically reading the entire DB into memory each record.
- parasubvert 9y ago
  No. Worst case a nested join of two tables M and N would be O(MN). But most real databases would have a merge or hash join which could bring this down to O(M+N) if the tables are already stored in a sorted indexed order. It is also rare the cardinality of these databases will be similar in an analytical query - usually you have one time series “fact table” and a bunch of dimensions to enrich that data. If I’m doing analytics on a time series this gets even better with partition pruning and hash joins or bitmap indexes. And if I have a columnar database, that blows up this whole complexity argument. My point is that, layout and indexes should never be assumed to be one size fits all. Keep in mind if I’m doing analytics I want to bring my time series data into memory at least as a stream, as I need to calculate / filter / transform the records. Not everything is about rendering a page on a website.

orf 9y ago

If you're dealing with billion row tables and using mongodb you are way out of your league. Good luck.

autokad 9y ago
mongodb is not the only nosql db :) I use hive/hbase

acdha 9y ago

From the sounds of it, you were downvoted for not recognizing this as a textbook scenario with decades of practice and engine improvements devoted to allowing you to balance query performance against your server budget. Interestingly enough, many of those techniques are common in the NoSQL world as well — a billion records is enough to require thinking about data flow anywhere — but the difference is that you have to deploy them more frequently.