Which is the opposite decision of other databases. So sometimes Postgres does make bad decisions...
Which is the opposite decision of other databases. So sometimes Postgres does make bad decisions...
count(⧆) just counts the tuples themselves, which is fast; it's like counting heap-allocated data structures by counting their pointers (which you're already walking), without dereferencing those pointers.
count(1) counts the result of evaluating the SQL expression "1" upon landing on each row, but still walks the same pointers to do so.
So, in terms of time complexity, they're roughly equivalent. Both data items (the tuple and the SQL constant expression) are already on the stack, ready to be directly computed upon.
Postgres's count(1) isn't slower than the one in any other DBMS. It's just their count(⧆)—at least the expression-evaluation part of it—which is more optimized than the one in other DBMSes. Nothing wrong with that, IMHO.
count( * ) is faster on Postgres than count(1). But both are fundementally slow because of MVCC. And count( * ) on postgres (the optimized one on Postgres) is much slower than count(1) on other databases (the optimzed one on other databases). So practically speaking, counting rows is slower on Postgres than on other databases.
That said, I love Postgres. I use it every day. There is some room for improvement and it does improve all the time. It is an amazing open source project. And I wouldn't care at all if they never optimize count(1)
Separately, there's an MVCC cost of walking the rows to filter them, and other DBMSes optimize walking rows for counting [usually causing both count(1) and count(⧆) to be faster], while Postgres does not do this optimization. (And, as stated in the article, in those DBMSes, this isn't a pure optimization per se, but is rather a trade-off, trading write speed for all INSERTs/DELETEs for read speed for this particular case.)
(† Technically, the filtering cost of count(⧆) hasn't been specifically optimized; the relative speed of filtering tuples for count(⧆) is an emergent property of the general fact that Postgres treats any mention of `⧆` as a reference to the row-tuple object itself. i.e. If `foo` is a table (x int, y text), then in actuality, `foo` is first created as a type [a pg_class] defined as the tuple (int, text); and then the table `foo` is defined as a relation persisting a rowset of `foo`-tuple-typed rows [in est making a table['s triggerable operations] each into a stored procedure with a `foo`-tuple-typed-rowset return type.] Then, the expressions `(SELECT ⧆ FROM foo)`, and `(SELECT f.⧆ FROM foo f)` both evaluate to rowsets type `foo`, which means that Postgres doesn't need to dereference the pointer to each `foo` heap tuple to build those rowsets. It only needs to dereference the pointers when it comes time to actually serialize and emit the row over the wire—which in case of a `count(⧆)` operation, never happens.)
See https://www.postgresql.org/message-id/CAN1FPGN1ynBj3m1DMszc9... and Tom Lane's followup for details.