How does the database count rows?

Question

PostgreSQL

So tell or show in the code.
Interested in COUNT() implementation

Answer 1

Yes, how is COUNT() implemented?

Depends on:
a) what will be inside COUNT
b) what will be in WHERE (and whether it will be at all);
c) implementation in a specific DBMS;
Example 1: if you make a COUNT with WHERE on a non-indexed field, then you cannot do without a table scan. In addition, if the field inside COUNT() can be NULL, then the DBMS will also have to throw out nulls, because COUNT does not take them into account.
Example 2: if you make a COUNT on the primary key without any filters, then the DBMS will probably just return the number of rows from its service structures to you, because primary keys are a) unique; b) cannot be NULL in most of today's DBMS.
Conclusion: SQL is a high-level declarative language that shifts algorithmic decision making to the DBMS scheduler, which gives a lot of room for optimizations in specific implementations. The best recipe is to plan a SPECIFIC query and see what it takes to execute it.