I don't know if it's faster or not!
-
-
COUNT(name) would only count rows where name is not NULL, whereas COUNT(*) would count all rows. IDK which is faster but, generally, I would use COUNT(*)
1 reply 0 retweets 5 likes -
You can also use Count(1) . This will give you a count of all the rows .
1 reply 0 retweets 3 likes -
Replying to @pkumarn19 @danilopiazza82 and
I don't know how it works for other RDBMS, but for
#PostgreSQL, it is a myth. Count(*) won't be slower that count(name) or count(1). The planner will come with exactly the same plan.3 replies 0 retweets 6 likes -
Replying to @l_avrot @pkumarn19 and
It will even be slightly faster because
@PostgreSQL is kind of dumb here and will check for each row that 1 is still not null.3 replies 0 retweets 6 likes -
Replying to @pg_xocolatl @l_avrot and
I suspect "slightly" really means infinitesimally faster as the real time is in scanning the table and checking for visibility
1 reply 0 retweets 0 likes -
Replying to @dave_cramer @l_avrot and
I have not benchmarked it but I assume that would be true. It's a quick test.
1 reply 0 retweets 0 likes -
Replying to @pg_xocolatl @l_avrot and
I'd be interested to know if you could measure it, I suspect it is less than the noise
2 replies 0 retweets 2 likes -
Replying to @dave_cramer @l_avrot and
Okay, are you ready? The difference between count(*) and count(1) is a spin through this: for (i = 1; i <= numTransInputs; i++) { if (fcinfo->args[i].isnull) return; } But for each row, so it's probably like tens of microseconds on a large grouping!
1 reply 0 retweets 9 likes -
Replying to @pg_xocolatl @dave_cramer and
okidoki, I benchmarked this, counting 1M rows many times. TL;DR: - MySQL: No difference - Oracle: No difference - PostgreSQL: Significant difference (surprisingly significant!) - SQL Server: No difference Details here:https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/ …
3 replies 10 retweets 20 likes
Historical note: PG used to optimize count(*) to count(1), so they were the same speed. Now, though, count(*) is treated as if it were count(), i.e. no params at all. Either way, the function just increments a counter; but the params if any are first checked for nullity
-
-
Replying to @RhodiumToad @pg_xocolatl and
Very interesting, thanks for sharing!
1 reply 0 retweets 0 likes -
Replying to @lukaseder @RhodiumToad and
Now it make sense. Since CPU spectre migrate, some indirect pointers are slower than ever. It matter if you do a lot. Try again with turn off those migrate features from OS
0 replies 0 retweets 0 likes
End of conversation
New conversation -
Loading seems to be taking a while.
Twitter may be over capacity or experiencing a momentary hiccup. Try again or visit Twitter Status for more information.