pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of count(*) on large tables vs SQL Server
4+ messages / 4 participants
[nested] [flat]
* Performance of count(*) on large tables vs SQL Server
@ 2005-02-01 12:41 Andrew Mayo <ajmayo@kohuconsulting.com>
0 siblings, 2 replies; 4+ messages in thread
From: Andrew Mayo @ 2005-02-01 12:41 UTC (permalink / raw)
To: pgsql-performance
Doing some rather crude comparative performance tests
between PG 8.0.1 on Windows XP and SQL Server 2000, PG
whips SQL Server's ass on
insert into junk (select * from junk)
on a one column table defined as int.
If we start with a 1 row table and repeatedly execute
this command, PG can take the table from 500K rows to
1M rows in 20 seconds; SQL Server is at least twice as
slow.
BUT...
SQL Server can do
select count(*) on junk
in almost no time at all, probably because this query
can be optimised to go back and use catalogue
statistics.
PG, on the other hand, appears to do a full table scan
to answer this question, taking nearly 4 seconds to
process the query.
Doing an ANALYZE on the table and also VACUUM did not
seem to affect this.
Can PG find a table's row count more efficiently?.
This is not an unusual practice in commercial
applications which assume that count(*) with no WHERE
clause will be a cheap query - and use it to test if
a table is empty, for instance. (because for
Oracle/Sybase/SQL Server, count(*) is cheap).
(sure, I appreciate there are other ways of doing
this, but I am curious about the way PG works here).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of count(*) on large tables vs SQL Server
@ 2005-02-01 13:02 Shridhar Daithankar <ghodechhap@ghodechhap.net>
parent: Andrew Mayo <ajmayo@kohuconsulting.com>
1 sibling, 1 reply; 4+ messages in thread
From: Shridhar Daithankar @ 2005-02-01 13:02 UTC (permalink / raw)
To: Andrew Mayo <ajmayo@kohuconsulting.com>; +Cc: pgsql-performance
On Tuesday 01 Feb 2005 6:11 pm, Andrew Mayo wrote:
> PG, on the other hand, appears to do a full table scan
> to answer this question, taking nearly 4 seconds to
> process the query.
>
> Doing an ANALYZE on the table and also VACUUM did not
> seem to affect this.
>
> Can PG find a table's row count more efficiently?.
> This is not an unusual practice in commercial
> applications which assume that count(*) with no WHERE
> clause will be a cheap query - and use it to test if
> a table is empty, for instance. (because for
> Oracle/Sybase/SQL Server, count(*) is cheap).
First of all, such an assumption is no good. It should hit concurrency under
heavy load but I know people do use it.
For the specific question, after a vacuum analyze, you can use
select reltuples from pg_class where relname='Foo';
Remember, you will get different results between 'analyze' and 'vacuum
analyze', since later actually visit every page in the table and hence is
expected to be more accurate.
> (sure, I appreciate there are other ways of doing
> this, but I am curious about the way PG works here).
Answer is MVCC and PG's inability use index alone. This has been a FAQ for a
loong time.. Furthermore PG has custom aggregates to complicate the matter..
Most of the pg developers/users think that unqualified select count(*) is of
no use. You can search the archives for more details..
HTH
Shridhar
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of count(*) on large tables vs SQL Server
@ 2005-02-01 13:22 Stef <stef@ummon.com>
parent: Shridhar Daithankar <ghodechhap@ghodechhap.net>
0 siblings, 0 replies; 4+ messages in thread
From: Stef @ 2005-02-01 13:22 UTC (permalink / raw)
To: Shridhar Daithankar <ghodechhap@ghodechhap.net>; +Cc: Andrew Mayo <ajmayo@kohuconsulting.com>; pgsql-performance
Hello Andrew,
Everything that Shridhar says makes perfect
sense, and, speaking from experience in dealing with
this type of 'problem', everything you say does as
well. Such is life really :)
I would not be at -all- surprised if Sybase
and Oracle did query re-writing behind the scene's
to send un-defined count's to a temporary table which
holds the row count. For an example of such done in
postgreSQL (using triggers and a custom procedure)
look into the 'General Bits' newsletter. Specifically
http://www.varlena.com/varlena/GeneralBits/49.php
I know, giving a URL as an answer 'sucks', but,
well, it simply repeats my experience. Triggers and
Procedures.
Regards
Steph
On Tue, Feb 01, 2005 at 06:32:56PM +0530, Shridhar Daithankar wrote:
> On Tuesday 01 Feb 2005 6:11 pm, Andrew Mayo wrote:
> > PG, on the other hand, appears to do a full table scan
> > to answer this question, taking nearly 4 seconds to
> > process the query.
> >
> > Doing an ANALYZE on the table and also VACUUM did not
> > seem to affect this.
> >
> > Can PG find a table's row count more efficiently?.
> > This is not an unusual practice in commercial
> > applications which assume that count(*) with no WHERE
> > clause will be a cheap query - and use it to test if
> > a table is empty, for instance. (because for
> > Oracle/Sybase/SQL Server, count(*) is cheap).
>
> First of all, such an assumption is no good. It should hit concurrency under
> heavy load but I know people do use it.
>
> For the specific question, after a vacuum analyze, you can use
>
> select reltuples from pg_class where relname='Foo';
>
> Remember, you will get different results between 'analyze' and 'vacuum
> analyze', since later actually visit every page in the table and hence is
> expected to be more accurate.
>
> > (sure, I appreciate there are other ways of doing
> > this, but I am curious about the way PG works here).
>
> Answer is MVCC and PG's inability use index alone. This has been a FAQ for a
> loong time.. Furthermore PG has custom aggregates to complicate the matter..
>
> Most of the pg developers/users think that unqualified select count(*) is of
> no use. You can search the archives for more details..
>
> HTH
>
> Shridhar
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of count(*) on large tables vs SQL Server
@ 2005-02-01 13:36 PFC <lists@boutiquenumerique.com>
parent: Andrew Mayo <ajmayo@kohuconsulting.com>
1 sibling, 0 replies; 4+ messages in thread
From: PFC @ 2005-02-01 13:36 UTC (permalink / raw)
To: Andrew Mayo <ajmayo@kohuconsulting.com>; pgsql-performance
> clause will be a cheap query - and use it to test if
> a table is empty, for instance. (because for
> Oracle/Sybase/SQL Server, count(*) is cheap).
To test if a table is empty, use a SELECT EXISTS or whatever SELECT with
a LIMIT 1...
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-02-01 13:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-01 12:41 Performance of count(*) on large tables vs SQL Server Andrew Mayo <ajmayo@kohuconsulting.com>
2005-02-01 13:02 ` Shridhar Daithankar <ghodechhap@ghodechhap.net>
2005-02-01 13:22 ` Stef <stef@ummon.com>
2005-02-01 13:36 ` PFC <lists@boutiquenumerique.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