4 ms·

You could ask for a count of records and divide by the page size.

neoglow 4y ago
Counts become incredibly slow on some databases like Postgres. It needs to do a sequential scan, which results in slow(er) performance on even a ten- thousands of rows. When operating at scale, this is very, very slow.
- kadoban 4y ago
  This seems unlikely to depend on which database software. It could easily depend on what indexes exist for the query.
- fabian2k 4y ago
  From what I understand it's not slow if it can use an index, but the criteria for that are rather hard to understand as a normal user. If you vacuum often enough and the visibility map is current, it doesn't need a full table scan.
- deleted 4y ago
  [deleted]
- avinassh 4y ago
  Isn't count slow on most databases? Either it ends up in table scan or index scan. But if the index is huge, then that will also take a lot of time. I wonder how do Big Co solve this counting problem.
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).
        4y ago
deleted 4y ago
[deleted]

Counts become incredibly slow on some databases like Postgres. It needs to do a sequential scan, which results in slow(er) performance on even a ten- thousands of rows. When operating at scale, this is very, very slow.

kadoban 4y ago
This seems unlikely to depend on which database software. It could easily depend on what indexes exist for the query.
fabian2k 4y ago
From what I understand it's not slow if it can use an index, but the criteria for that are rather hard to understand as a normal user. If you vacuum often enough and the visibility map is current, it doesn't need a full table scan.
deleted 4y ago
[deleted]
avinassh 4y ago
Isn't count slow on most databases? Either it ends up in table scan or index scan. But if the index is huge, then that will also take a lot of time. I wonder how do Big Co solve this counting problem.

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).
      - 4y ago

deleted 4y ago

[deleted]