Better count performance with SELECT COUNT(*) than with SELECT COUNT(id)

[Huluti]

Hello!

Small question about counts performance with doctrine and postgresql.

Doctrine doesn't permit to do:
->select('COUNT(*)') => [Syntax Error] line 0, col 13: Error: Expected Literal, got '*'

Only ->select('COUNT(l)') or ->select('COUNT(l.id)')

So the final query is SELECT COUNT(l.id) ...

But I have an example where "*" seems to be much better than "l.id":

explain analyze SELECT COUNT(l.id) FROM link l WHERE l.team_id = 10190 AND l.folder_id = 298
=>

Aggregate  (cost=4891.02..4891.03 rows=1 width=8) (actual time=57.480..57.481 rows=1 loops=1)
  ->  Index Scan using idx_36ac99f1296cd8ae162cb942 on link l  (cost=0.43..4878.54 rows=4994 width=4) (actual time=0.026..49.088 rows=98337 loops=1)
        Index Cond: ((team_id = 10190) AND (folder_id = 298))
Planning Time: 0.164 ms
Execution Time: 57.552 ms 

explain analyze SELECT COUNT(*) FROM link l WHERE l.team_id = 10190 AND l.folder_id = 298
=>

Aggregate  (cost=319.56..319.57 rows=1 width=8) (actual time=14.743..14.744 rows=1 loops=1)
  ->  Index Only Scan using idx_36ac99f1296cd8ae162cb942 on link l  (cost=0.43..307.07 rows=4994 width=0) (actual time=0.021..10.194 rows=98339 loops=1)
        Index Cond: ((team_id = 10190) AND (folder_id = 298))
        Heap Fetches: 4841
Planning Time: 0.139 ms
Execution Time: 14.776 ms 

Do you know if it's normal? Does there is a way to use * with doctrine ?

ChatGPT (i know not 100% sure) says this:

🔍 Why is COUNT() Faster Than COUNT(l.id)?
1️⃣ Index-Only Scan vs. Index Scan
COUNT() allows PostgreSQL to use an Index-Only Scan.
COUNT(l.id) forces PostgreSQL to do an Index Scan.
💡 Why?
COUNT(*) counts rows and does not care about NULLs.
COUNT(l.id) requires checking that l.id is not NULL.
PostgreSQL assumes id is never NULL (if it's a primary key), but COUNT(column) still makes it fall back to an Index Scan.

pgexplain links:

Query 1 => https://www.pgexplain.dev/plan/44bf4e9a-5813-46ba-a39a-333d61e5c400
Query 2 = https://www.pgexplain.dev/plan/cab820cc-686c-4a3c-ac46-65fd620d6efb

Thanks in advance for your help!