Thread: How Postgresql Compares... Count(*) and others

Last time I tested this on the big O ( early 8.0.x ) count(*) was always
fractionally quicker.... there was a body of thought at the time that said
count(1) or count(<field>) was quicker.... no idea where it came from.


I have not tried to see if it makes any difference in Postgresql...

regards

Mark

Mark kirkwood <markir@slingshot.co.nz> writes:
> I have not tried to see if it makes any difference in Postgresql...

Postgres converts count(*) to count(1) at the grammar stage (cf. gram.y,
about line 4817 in current sources).  So if you think you detect any
performance difference, you're surely hallucinating...

            regards, tom lane

On Sun, Jul 22, 2001 at 03:06:52PM +1200,
  Mark kirkwood <markir@slingshot.co.nz> wrote:
> Last time I tested this on the big O ( early 8.0.x ) count(*) was always
> fractionally quicker.... there was a body of thought at the time that said
> count(1) or count(<field>) was quicker.... no idea where it came from.

Note that count(<field>) and count(*) are not equivalent. count(<field>)
counts only rows where <field> isn't null.

On Sunday 22 July 2001 15:49, you wrote:
> Mark kirkwood <markir@slingshot.co.nz> writes:
> > I have not tried to see if it makes any difference in Postgresql...
>
> Postgres converts count(*) to count(1) at the grammar stage (cf. gram.y,
> about line 4817 in current sources).  So if you think you detect any
> performance difference, you're surely hallucinating...
>
>             regards, tom lane

It seemed appropriate to test my "experimental method" with this example :
I measured elapsed times for count(*), count(1) ....

I can (with some relief)  report elapsed times for both at 1m01, with a
variation of 1 s for both measurements ...

thanks for everyboys patience here...

Mark