4 ms·

This is what I do, since counts should cause minimal overhead (I've never had to deal with huge databases). But neither does the N+1 records trick which OP desc

by spurgu 4y ago

This is what I do, since counts should cause minimal overhead (I've never had to deal with huge databases). But neither does the N+1 records trick which OP describes, that's clever. :)

SonOfLilit 4y ago
Counts can cause significant overhead on even millions of rows.
- MereInterest 4y ago
  If I were to ask the number of even numbers greater than zero and less than 10 billion, you could very quickly give an exact answer. If I were to ask the exact number of prime numbers on that same range, it would take much longer to find the correct answer. In the same way, asking for counts may or may not be a quick answer, depending on both the table structure and the query being performed. Query the number of customers who started this year, on a table indexed by start date, and it's incredibly cheap. If that same table is only indexed by last name, then counts become more expensive. I'd say that the advice to return the counts has an implicit addendum to make sure that your database is appropriately indexed such that your typical queries are cheap to perform.
  - inferiorhuman 4y ago
    So I'm coming at this from a Postgres POV, IIRC MySQL can take some shortcuts for COUNT(*). Constraining the query will help but counting is expensive as a sequential table scan is always required. Meanwhile I can only think of one use case for pagination with exact row counts – a user facing application. If you're preallocating storage you can get by with an estimate. If you're consuming an API and want something from near the end you can flip the sort order.
    - nolevels 4y ago
      Not necessarily in postgres. If an index is available it may use that, though it will still have to refer to the visibility map to see if any row is available to the current transaction (cheap check), and if it can't determine from that, then and only then does it need to also refer to the heap / actual table (slower check).
      - inferiorhuman 4y ago
        Is that a recent change? Back in 2016 the Citus guys claimed that: There is no single universal row count that the database could cache, so it must scan through all rows counting how many are visible. Performance for an exact count grows linearly with table size. https://www.citusdata.com/blog/2016/10/12/count-performance/ https://www.citusdata.com/blog/2016/10/12/count-performance/
        nolevels 4y ago
        Apologies, I should've clarified. I just meant postgres doesn't necessarily have to scan the actual table to get the count in some cases if an index is available, not that it caches the actual count.
        inferiorhuman 4y ago
        So that's not the greatest quote but the article shows postgres having to fall back on a sequential scan for a naive COUNT(*). The author goes on to mention that as of 9.2 Postgres can do an index scan (in the context of SELECT DISTINCT): But now we come to a quirk, SELECT COUNT(DISTINCT n) FROM items will not use the index even though SELECT DISTINCT n does. As many blog posts mention (“one weird trick to make postgres 50x faster!”) you can guide the planner by rewriting count distinct as the count of a subquery To me it seems like you can trick the query planner into doing an index scan sometimes, but that it's a bit brittle. Has this improved much since?
        nolevels 4y ago
        I think the wiki explains it well https://wiki.postgresql.org/wiki/Index-only_scans#Is_.22count.28.2A.29.22_much_faster_now.3F https://wiki.postgresql.org/wiki/Index-only_scans#Is_.22coun... It says that index only scans can be used with predicates and less often without predicates, though in my experience I've seen the index often used even without predicates.
    - evanelias 4y ago
      It's slow and expensive in MySQL too, assuming you mean InnoDB storage engine. Recent versions of MySQL can parallelize a COUNT(*), but that only helps so much. The "shortcuts" you're thinking of may be from the MyISAM storage engine days. Since MyISAM doesn't have transactions or MVCC, it can maintain a row count for the table and just return that. But MyISAM is generally not used for the past decade (or more), as it just isn't really suitable for storing data that you care about. Storage engines with transactions cannot just store a row count per table because the accurate count always depends on your transaction's isolated snapshot.
- inferiorhuman 4y ago
  Sure, but that point you've really got to start thinking about architecture. Do you really need to paginate millions of rows and count them? Can you distribute the database and parallelize counting? Can you save counts somewhere else? Can you use estimates?
- spurgu 4y ago
  Thanks, that's good to know (will have to perform some tests at some point). I do have a few tables (namely logs) that will have millions of rows eventually over time. But there I've also been planning on rotating them whenever that becomes a problem, so I'm not overly concerned. Or experiment with OP's solution.
- deleted 4y ago
  [deleted]

by spurgu 4y ago

This is what I do, since counts should cause minimal overhead (I've never had to deal with huge databases). But neither does the N+1 records trick which OP describes, that's clever. :)

SonOfLilit 4y ago

Counts can cause significant overhead on even millions of rows.

MereInterest 4y ago
If I were to ask the number of even numbers greater than zero and less than 10 billion, you could very quickly give an exact answer. If I were to ask the exact number of prime numbers on that same range, it would take much longer to find the correct answer. In the same way, asking for counts may or may not be a quick answer, depending on both the table structure and the query being performed. Query the number of customers who started this year, on a table indexed by start date, and it's incredibly cheap. If that same table is only indexed by last name, then counts become more expensive. I'd say that the advice to return the counts has an implicit addendum to make sure that your database is appropriately indexed such that your typical queries are cheap to perform.
- inferiorhuman 4y ago
  So I'm coming at this from a Postgres POV, IIRC MySQL can take some shortcuts for COUNT(*). Constraining the query will help but counting is expensive as a sequential table scan is always required. Meanwhile I can only think of one use case for pagination with exact row counts – a user facing application. If you're preallocating storage you can get by with an estimate. If you're consuming an API and want something from near the end you can flip the sort order.
  - nolevels 4y ago
    Not necessarily in postgres. If an index is available it may use that, though it will still have to refer to the visibility map to see if any row is available to the current transaction (cheap check), and if it can't determine from that, then and only then does it need to also refer to the heap / actual table (slower check).
    - inferiorhuman 4y ago
      Is that a recent change? Back in 2016 the Citus guys claimed that: There is no single universal row count that the database could cache, so it must scan through all rows counting how many are visible. Performance for an exact count grows linearly with table size. https://www.citusdata.com/blog/2016/10/12/count-performance/ https://www.citusdata.com/blog/2016/10/12/count-performance/
      - nolevels 4y ago
        Apologies, I should've clarified. I just meant postgres doesn't necessarily have to scan the actual table to get the count in some cases if an index is available, not that it caches the actual count.
        inferiorhuman 4y ago
        So that's not the greatest quote but the article shows postgres having to fall back on a sequential scan for a naive COUNT(*). The author goes on to mention that as of 9.2 Postgres can do an index scan (in the context of SELECT DISTINCT): But now we come to a quirk, SELECT COUNT(DISTINCT n) FROM items will not use the index even though SELECT DISTINCT n does. As many blog posts mention (“one weird trick to make postgres 50x faster!”) you can guide the planner by rewriting count distinct as the count of a subquery To me it seems like you can trick the query planner into doing an index scan sometimes, but that it's a bit brittle. Has this improved much since?
        nolevels 4y ago
        I think the wiki explains it well https://wiki.postgresql.org/wiki/Index-only_scans#Is_.22count.28.2A.29.22_much_faster_now.3F https://wiki.postgresql.org/wiki/Index-only_scans#Is_.22coun... It says that index only scans can be used with predicates and less often without predicates, though in my experience I've seen the index often used even without predicates.
  - evanelias 4y ago
    It's slow and expensive in MySQL too, assuming you mean InnoDB storage engine. Recent versions of MySQL can parallelize a COUNT(*), but that only helps so much. The "shortcuts" you're thinking of may be from the MyISAM storage engine days. Since MyISAM doesn't have transactions or MVCC, it can maintain a row count for the table and just return that. But MyISAM is generally not used for the past decade (or more), as it just isn't really suitable for storing data that you care about. Storage engines with transactions cannot just store a row count per table because the accurate count always depends on your transaction's isolated snapshot.
inferiorhuman 4y ago
Sure, but that point you've really got to start thinking about architecture. Do you really need to paginate millions of rows and count them? Can you distribute the database and parallelize counting? Can you save counts somewhere else? Can you use estimates?
spurgu 4y ago
Thanks, that's good to know (will have to perform some tests at some point). I do have a few tables (namely logs) that will have millions of rows eventually over time. But there I've also been planning on rotating them whenever that becomes a problem, so I'm not overly concerned. Or experiment with OP's solution.
deleted 4y ago
[deleted]