pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhich one is faster
9+ messages / 5 participants
[nested] [flat]
* which one is faster
@ 2010-10-26 10:56 AI Rumman <rummandba@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: AI Rumman @ 2010-10-26 10:56 UTC (permalink / raw)
To: pgsql-performance
Which one is faster?
select count(*) from talble
or
select count(id) from table
where id is the primary key.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: which one is faster
@ 2010-10-26 10:59 Szymon Guz <mabewlun@gmail.com>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Szymon Guz @ 2010-10-26 10:59 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: which one is faster
@ 2010-10-26 11:59 Marcin Mirosław <marcin@mejor.pl>
parent: Szymon Guz <mabewlun@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Marcin Mirosław @ 2010-10-26 11:59 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: which one is faster
@ 2010-10-26 12:05 Szymon Guz <mabewlun@gmail.com>
parent: Marcin Mirosław <marcin@mejor.pl>
0 siblings, 1 reply; 9+ messages in thread
From: Szymon Guz @ 2010-10-26 12:05 UTC (permalink / raw)
To: Marcin Mirosław <marcin@mejor.pl>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: which one is faster
@ 2010-10-26 12:08 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Szymon Guz <mabewlun@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-10-26 12:08 UTC (permalink / raw)
To: Szymon Guz <mabewlun@gmail.com>; +Cc: Marcin Mirosław <marcin@mejor.pl>; pgsql-performance
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;
;]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: which one is faster
@ 2010-10-26 12:16 Szymon Guz <mabewlun@gmail.com>
parent: Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Szymon Guz @ 2010-10-26 12:16 UTC (permalink / raw)
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>; +Cc: Marcin Mirosław <marcin@mejor.pl>; pgsql-performance
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?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: which one is faster
@ 2010-10-26 12:20 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Szymon Guz <mabewlun@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-10-26 12:20 UTC (permalink / raw)
To: Szymon Guz <mabewlun@gmail.com>; +Cc: Marcin Mirosław <marcin@mejor.pl>; pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: which one is faster
@ 2010-10-26 12:23 Szymon Guz <mabewlun@gmail.com>
parent: Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Szymon Guz @ 2010-10-26 12:23 UTC (permalink / raw)
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>; +Cc: Marcin Mirosław <marcin@mejor.pl>; pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: which one is faster
@ 2010-10-26 12:32 Mladen Gogala <mladen.gogala@vmsinfo.com>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Mladen Gogala @ 2010-10-26 12:32 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-10-26 12:32 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-26 10:56 which one is faster AI Rumman <rummandba@gmail.com>
2010-10-26 10:59 ` Szymon Guz <mabewlun@gmail.com>
2010-10-26 11:59 ` Marcin Mirosław <marcin@mejor.pl>
2010-10-26 12:05 ` Szymon Guz <mabewlun@gmail.com>
2010-10-26 12:08 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-10-26 12:16 ` Szymon Guz <mabewlun@gmail.com>
2010-10-26 12:20 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-10-26 12:23 ` Szymon Guz <mabewlun@gmail.com>
2010-10-26 12:32 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox