Postgres Pro
Downloads
Home   >   mailing lists

Thread: which one is faster

which one is faster

From
AI Rumman
Date:
Which one is faster?
select count(*) from talble
or
select count(id) from table
where id is the primary key.

Re: which one is faster

From
Szymon Guz
Date:


On 26 October 2010 12:56, AI Rumman <rummandba@gmail.com> wrote:
Which one is faster?
select count(*) from talble
or
select count(id) from table
where id is the primary key.


Check the query plan, both queries are the same.

regards
Szymon

Re: which one is faster

From
Marcin Mirosław
Date:
W dniu 26.10.2010 12:59, Szymon Guz pisze:
> both queries are the same.

IMHO they aren't the same, but they returns the same value in this case.
I mean count(field) doesn't count NULL values, count(*) does it.
I'm writing this only for note:)
Regards

Re: which one is faster

From
Szymon Guz
Date:


2010/10/26 Marcin Mirosław <marcin@mejor.pl>
W dniu 26.10.2010 12:59, Szymon Guz pisze:
> both queries are the same.

IMHO they aren't the same, but they returns the same value in this case.
I mean count(field) doesn't count NULL values, count(*) does it.
I'm writing this only for note:)
Regards


Yup, indeed. I omitted that note, as it was written that the field is primary key :).

regards
Szymon 

Re: which one is faster

From
Grzegorz Jaśkiewicz
Date:
implementation wise, count(*) is faster. Very easy to test:

SELECT COUNT(*) FROM generate_series(1,100) a, generate_series(1,1000) b;

SELECT COUNT(a) FROM generate_series(1,100) a, generate_series(1,1000) b;


;]

Re: which one is faster

From
Szymon Guz
Date:
2010/10/26 Grzegorz Jaśkiewicz <gryzman@gmail.com>
implementation wise, count(*) is faster. Very easy to test:

SELECT COUNT(*) FROM generate_series(1,100) a, generate_series(1,1000) b;

SELECT COUNT(a) FROM generate_series(1,100) a, generate_series(1,1000) b;


;]

Well, strange. Why is that slower?


Re: which one is faster

From
Grzegorz Jaśkiewicz
Date:
2010/10/26 Szymon Guz <mabewlun@gmail.com>:
>
> Well, strange. Why is that slower?

To answer that fully, you would need to see the implementation.
suffice to say,

count(a) does:

if (a <> NULL)
{
  count++;
}

and count(*) does:

  count++;



--
GJ

Re: which one is faster

From
Szymon Guz
Date:


2010/10/26 Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010/10/26 Szymon Guz <mabewlun@gmail.com>:
>
> Well, strange. Why is that slower?

To answer that fully, you would need to see the implementation.
suffice to say,

count(a) does:

if (a <> NULL)
{
 count++;
}

and count(*) does:

 count++;



Yup, I was afraid of that, even if there is not null on the column... but I think usually nobody notices the difference with count.

regards
Szymon

Re: which one is faster

From
Mladen Gogala
Date:
On 10/26/2010 6:56 AM, AI Rumman wrote:
> Which one is faster?
> select count(*) from talble
> or
> select count(id) from table
> where id is the primary key.
PostgreSQL doesn't utilize the access methods known as "FULL INDEX SCAN"
and "FAST FULL INDEX SCAN", so the optimizer will generate the
sequential scan in both cases. In other words, PostgreSQL will read the
entire table when counting, no matter what.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com