pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stef <stef@ummon.com>
To: Shridhar Daithankar <ghodechhap@ghodechhap.net>
Cc: Andrew Mayo <ajmayo@kohuconsulting.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of count(*) on large tables vs SQL Server
Date: Tue, 1 Feb 2005 08:22:50 -0500
Message-ID: <20050201132250.GF32625@survivor.hades> (raw)
In-Reply-To: <200502011832.56333.ghodechhap@ghodechhap.net>
References: <20050201124143.43977.qmail@web206.biz.mail.re2.yahoo.com>
	<200502011832.56333.ghodechhap@ghodechhap.net>


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
> 

view thread (4+ messages)  latest in thread

Message-ID: <20050201132250.GF32625@survivor.hades>
Permalink:  ../20050201132250.GF32625@survivor.hades/
Also on:    postgresql.org/message-id/20050201132250.GF32625@survivor.hades

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: stef@ummon.com, ghodechhap@ghodechhap.net, ajmayo@kohuconsulting.com
  Subject: Re: Performance of count(*) on large tables vs SQL Server
  In-Reply-To: <20050201132250.GF32625@survivor.hades>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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