![]() Curious to hear about your own results in the comments, or further ideas why this is so significant in PostgreSQL. No relevant difference, nor a clear winner Run 1, Statement 1 : 1.06874Ī significant, consistent difference of almost 10%: RUN 1, Statement 1: 1.00134Īgain, I’m surprised by the order of magnitude of this difference. No relevant difference, nor a clear winner: RUN STMT RELATIVE_TIME This removes any distraction that may be caused by interpreting actual execution times as we do not want to compare database products against each other. The times displayed are relative to the fastest run per database product. Each benchmark run repeated SELECT COUNT(*) FROM t or SELECT COUNT(1) FROM t 100 times on a 1M row table, and then the benchmark was repeated 5 times to mitigate any warmup penalties and be fair with respect to caching. The benchmark code can be found in the following gists: COUNT(*) was consistently faster by around 10% on 1M rows, that’s much more than I had expected Sometimes COUNT(1) was faster, sometimes COUNT(*) was faster, so all differences were only benchmark artifacts I ran a benchmark on the 4 most popular RDBMS, with these results: Okay, are you ready? The difference between count(*) and count(1) is a spin through this:īut for each row, so it's probably like tens of microseconds on a large grouping!- Vik Fearing September 15, 2019 Actors with their first_name containing A.Actors with their first_name ending with A.Actors with their first_name starting with A. ![]() We’ve already blogged about this technique in our previous post about aggregating several expressions in one single query. Now, we’re getting the correct result: actor_id|first_name |last_name |c |Īn even more powerful application of counting only non-null evaluations of an expression is counting only subsets of a group. Notice, we could count other things than the primary key, but with the primary key, we’re quite certain we don’t get any other “accidental” nulls in our groups, which we did not want to exclude from the count value. LEFT JOIN film_actor AS fa USING (actor_id) SELECT actor_id, a.first_name, a.last_name, count(film_id) AS c
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |