pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
count(*) slow on large tables
30+ messages / 18 participants
[nested] [flat]

* count(*) slow on large tables
@ 2003-10-02 19:15  Dror Matalon <dror@zapatec.com>
  0 siblings, 2 replies; 30+ messages in thread

From: Dror Matalon @ 2003-10-02 19:15 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I have a somewhat large table, 3 million rows, 1 Gig on disk,  and growing. Doing a
count(*) takes around 40 seconds.

Looks like the count(*) fetches the table from disk and goes through it.
Made me wonder, why the optimizer doesn't just choose the smallest index
which in my case is around 60 Megs and goes through it, which it could
do in a fraction of the time.

Dror


-- 
Dror Matalon
Zapatec Inc 
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-02 19:36  Tomasz Myrta <jasiek@klaster.net>
  parent: Dror Matalon <dror@zapatec.com>
  1 sibling, 0 replies; 30+ messages in thread

From: Tomasz Myrta @ 2003-10-02 19:36 UTC (permalink / raw)
  To: Dror Matalon <dror@zapatec.com>; +Cc: pgsql-performance

> Hi,
> 
> I have a somewhat large table, 3 million rows, 1 Gig on disk,  and growing. Doing a
> count(*) takes around 40 seconds.
> 
> Looks like the count(*) fetches the table from disk and goes through it.
> Made me wonder, why the optimizer doesn't just choose the smallest index
> which in my case is around 60 Megs and goes through it, which it could
> do in a fraction of the time.
> 
> Dror

Just like other aggregate functions, count(*) won't use indexes when 
counting whole table.

Regards,
Tomasz Myrta




^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-02 19:39  Bruno Wolff III <bruno@wolff.to>
  parent: Dror Matalon <dror@zapatec.com>
  1 sibling, 2 replies; 30+ messages in thread

From: Bruno Wolff III @ 2003-10-02 19:39 UTC (permalink / raw)
  To: Dror Matalon <dror@zapatec.com>; +Cc: pgsql-performance

On Thu, Oct 02, 2003 at 12:15:47 -0700,
  Dror Matalon <dror@zapatec.com> wrote:
> Hi,
> 
> I have a somewhat large table, 3 million rows, 1 Gig on disk,  and growing. Doing a
> count(*) takes around 40 seconds.
> 
> Looks like the count(*) fetches the table from disk and goes through it.
> Made me wonder, why the optimizer doesn't just choose the smallest index
> which in my case is around 60 Megs and goes through it, which it could
> do in a fraction of the time.

Because it can't tell from the index if a tuple is visible to the current
transaction and would still have to hit the table to check this. So that
performance would be a lot worse instead of better.



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-02 19:58  Bruno Wolff III <bruno@wolff.to>
  parent: Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 30+ messages in thread

From: Bruno Wolff III @ 2003-10-02 19:58 UTC (permalink / raw)
  To: Dror Matalon <dror@zapatec.com>; +Cc: pgsql-performance

On Thu, Oct 02, 2003 at 12:46:45 -0700,
  Dror Matalon <dror@zapatec.com> wrote:

Please keep replies copied to the list.

> When would it happen that a tuple be invisible to the current
> transaction? Are we talking about permissions?

They could be tuples that were changed by a transaction that hasn't committed
or in the case of serializable isolation, a transaction that committed after
the current transaction started.

> 
> On Thu, Oct 02, 2003 at 02:39:05PM -0500, Bruno Wolff III wrote:
> > On Thu, Oct 02, 2003 at 12:15:47 -0700,
> >   Dror Matalon <dror@zapatec.com> wrote:
> > > Hi,
> > > 
> > > I have a somewhat large table, 3 million rows, 1 Gig on disk,  and growing. Doing a
> > > count(*) takes around 40 seconds.
> > > 
> > > Looks like the count(*) fetches the table from disk and goes through it.
> > > Made me wonder, why the optimizer doesn't just choose the smallest index
> > > which in my case is around 60 Megs and goes through it, which it could
> > > do in a fraction of the time.
> > 
> > Because it can't tell from the index if a tuple is visible to the current
> > transaction and would still have to hit the table to check this. So that
> > performance would be a lot worse instead of better.
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> > 
> >                http://www.postgresql.org/docs/faqs/FAQ.html
> 
> -- 
> Dror Matalon, President
> Zapatec Inc 
> 1700 MLK Way
> Berkeley, CA 94709
> http://www.zapatec.com



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-02 21:29  Jean-Luc Lachance <jllachan@nsd.ca>
  parent: Bruno Wolff III <bruno@wolff.to>
  1 sibling, 1 reply; 30+ messages in thread

From: Jean-Luc Lachance @ 2003-10-02 21:29 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: Dror Matalon <dror@zapatec.com>; pgsql-performance

That's one of the draw back of MVCC.  
I once suggested that the transaction number and other house keeping
info be included in the index, but was told to forget it...
It would solve once and for all the issue of seq_scan vs index_scan.
It would simplify the aggregate problem.


Bruno Wolff III wrote:
> 
> On Thu, Oct 02, 2003 at 12:15:47 -0700,
>   Dror Matalon <dror@zapatec.com> wrote:
> > Hi,
> >
> > I have a somewhat large table, 3 million rows, 1 Gig on disk,  and growing. Doing a
> > count(*) takes around 40 seconds.
> >
> > Looks like the count(*) fetches the table from disk and goes through it.
> > Made me wonder, why the optimizer doesn't just choose the smallest index
> > which in my case is around 60 Megs and goes through it, which it could
> > do in a fraction of the time.
> 
> Because it can't tell from the index if a tuple is visible to the current
> transaction and would still have to hit the table to check this. So that
> performance would be a lot worse instead of better.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-02 21:57  Christopher Browne <cbbrowne@libertyrms.info>
  parent: Jean-Luc Lachance <jllachan@nsd.ca>
  0 siblings, 4 replies; 30+ messages in thread

From: Christopher Browne @ 2003-10-02 21:57 UTC (permalink / raw)
  To: pgsql-performance

jllachan@nsd.ca (Jean-Luc Lachance) writes:
> That's one of the draw back of MVCC.  
> I once suggested that the transaction number and other house keeping
> info be included in the index, but was told to forget it...
> It would solve once and for all the issue of seq_scan vs index_scan.
> It would simplify the aggregate problem.

It would only simplify _one_ case, namely the case where someone cares
about the cardinality of a relation, and it would do that at
_considerable_ cost.

A while back I outlined how this would have to be done, and for it to
be done efficiently, it would be anything BUT simple.  

It would be very hairy to implement it correctly, and all this would
cover is the single case of "SELECT COUNT(*) FROM SOME_TABLE;"

If you had a single WHERE clause attached, you would have to revert to
walking through the tuples looking for the ones that are live and
committed, which is true for any DBMS.

And it still begs the same question, of why the result of this query
would be particularly meaningful to anyone.  I don't see the
usefulness; I don't see the value of going to the considerable effort
of "fixing" this purported problem.
-- 
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/;
Christopher Browne
(416) 646 3304 x124 (land)



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-02 22:33  Dror Matalon <dror@zapatec.com>
  parent: Christopher Browne <cbbrowne@libertyrms.info>
  3 siblings, 1 reply; 30+ messages in thread

From: Dror Matalon @ 2003-10-02 22:33 UTC (permalink / raw)
  To: pgsql-performance


I don't have an opinion on how hard it would be to implement the
tracking in the indexes, but "select count(*) from some table" is, in my
experience, a query that people tend to run quite often. 
One of the databases that I've used, I believe it was Informix, had that
info cached so that it always new how many rows there were in any
table. It was quite useful.


On Thu, Oct 02, 2003 at 05:57:30PM -0400, Christopher Browne wrote:
> jllachan@nsd.ca (Jean-Luc Lachance) writes:
> > That's one of the draw back of MVCC.  
> > I once suggested that the transaction number and other house keeping
> > info be included in the index, but was told to forget it...
> > It would solve once and for all the issue of seq_scan vs index_scan.
> > It would simplify the aggregate problem.
> 
> It would only simplify _one_ case, namely the case where someone cares
> about the cardinality of a relation, and it would do that at
> _considerable_ cost.
> 
> A while back I outlined how this would have to be done, and for it to
> be done efficiently, it would be anything BUT simple.  
> 
> It would be very hairy to implement it correctly, and all this would
> cover is the single case of "SELECT COUNT(*) FROM SOME_TABLE;"
> 
> If you had a single WHERE clause attached, you would have to revert to
> walking through the tuples looking for the ones that are live and
> committed, which is true for any DBMS.
> 
> And it still begs the same question, of why the result of this query
> would be particularly meaningful to anyone.  I don't see the
> usefulness; I don't see the value of going to the considerable effort
> of "fixing" this purported problem.
> -- 
> let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
> <http://dev6.int.libertyrms.com/;
> Christopher Browne
> (416) 646 3304 x124 (land)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
Dror Matalon, President
Zapatec Inc 
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 02:08  Christopher Browne <cbbrowne@acm.org>
  parent: Dror Matalon <dror@zapatec.com>
  0 siblings, 2 replies; 30+ messages in thread

From: Christopher Browne @ 2003-10-03 02:08 UTC (permalink / raw)
  To: pgsql-performance

The world rejoiced as dror@zapatec.com (Dror Matalon) wrote:
> I don't have an opinion on how hard it would be to implement the
> tracking in the indexes, but "select count(*) from some table" is, in my
> experience, a query that people tend to run quite often. 
> One of the databases that I've used, I believe it was Informix, had that
> info cached so that it always new how many rows there were in any
> table. It was quite useful.

I can't imagine why the raw number of tuples in a relation would be
expected to necessarily be terribly useful.

I'm involved with managing Internet domains, and it's only when people
are being pretty clueless that anyone imagines that "select count(*)
from domains;" would be of any use to anyone.  There are enough "test
domains" and "inactive domains" and other such things that the raw
number of "things in the table" aren't really of much use.

- I _do_ care how many pages a table occupies, to some extent, as that
determines whether it will fit in my disk space or not, but that's not
COUNT(*).

- I might care about auditing the exact numbers of records in order to
be assured that a data conversion process was done correctly.  But in
that case, I want to do something a whole *lot* more detailed than
mere COUNT(*).

I'm playing "devil's advocate" here, to some extent.  But
realistically, there is good reason to be skeptical of the merits of
using SELECT COUNT(*) FROM TABLE for much of anything.

Furthermore, the relation that you query mightn't be a physical
"table."  It might be a more virtual VIEW, and if that's the case,
bets are even MORE off.  If you go with the common dictum of "good
design" that users don't directly access tables, but go through VIEWs,
users may have no way to get at SELECT COUNT(*) FROM TABLE.
-- 
output = reverse("ac.notelrac.teneerf" "@" "454aa")
http://www.ntlug.org/~cbbrowne/finances.html
Rules  of  the  Evil  Overlord  #74.   "When  I  create  a  multimedia
presentation of my plan designed  so that my five-year-old advisor can
easily  understand the  details, I  will not  label the  disk "Project
Overlord" and leave it lying on top of my desk."
<http://www.eviloverlord.com/;



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 04:27  Dror Matalon <dror@zapatec.com>
  parent: Christopher Browne <cbbrowne@acm.org>
  1 sibling, 2 replies; 30+ messages in thread

From: Dror Matalon @ 2003-10-03 04:27 UTC (permalink / raw)
  To: pgsql-performance


I smell a religious war in the aii:-). 
Can you go several days in a row without doing select count(*) on any
of your tables? 

I suspect that this is somewhat a domain specific issue. In some areas
you don't need to know the total number of rows in your tables, in
others you do. 

I also suspect that you're right, that end user applications don't use
this information as often as DBAs would. On the other hand, it seems
whenever you want to optimize your app (something relevant to this list),
one of the things you do need to know is the number of rows in your
table.

Dror

On Thu, Oct 02, 2003 at 10:08:18PM -0400, Christopher Browne wrote:
> The world rejoiced as dror@zapatec.com (Dror Matalon) wrote:
> > I don't have an opinion on how hard it would be to implement the
> > tracking in the indexes, but "select count(*) from some table" is, in my
> > experience, a query that people tend to run quite often. 
> > One of the databases that I've used, I believe it was Informix, had that
> > info cached so that it always new how many rows there were in any
> > table. It was quite useful.
> 
> I can't imagine why the raw number of tuples in a relation would be
> expected to necessarily be terribly useful.
> 
> I'm involved with managing Internet domains, and it's only when people
> are being pretty clueless that anyone imagines that "select count(*)
> from domains;" would be of any use to anyone.  There are enough "test
> domains" and "inactive domains" and other such things that the raw
> number of "things in the table" aren't really of much use.
> 
> - I _do_ care how many pages a table occupies, to some extent, as that
> determines whether it will fit in my disk space or not, but that's not
> COUNT(*).
> 
> - I might care about auditing the exact numbers of records in order to
> be assured that a data conversion process was done correctly.  But in
> that case, I want to do something a whole *lot* more detailed than
> mere COUNT(*).
> 
> I'm playing "devil's advocate" here, to some extent.  But
> realistically, there is good reason to be skeptical of the merits of
> using SELECT COUNT(*) FROM TABLE for much of anything.
> 
> Furthermore, the relation that you query mightn't be a physical
> "table."  It might be a more virtual VIEW, and if that's the case,
> bets are even MORE off.  If you go with the common dictum of "good
> design" that users don't directly access tables, but go through VIEWs,
> users may have no way to get at SELECT COUNT(*) FROM TABLE.
> -- 
> output = reverse("ac.notelrac.teneerf" "@" "454aa")
> http://www.ntlug.org/~cbbrowne/finances.html
> Rules  of  the  Evil  Overlord  #74.   "When  I  create  a  multimedia
> presentation of my plan designed  so that my five-year-old advisor can
> easily  understand the  details, I  will not  label the  disk "Project
> Overlord" and leave it lying on top of my desk."
> <http://www.eviloverlord.com/;
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
Dror Matalon, President
Zapatec Inc 
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 05:13  Greg Stark <gsstark@mit.edu>
  parent: Christopher Browne <cbbrowne@libertyrms.info>
  3 siblings, 0 replies; 30+ messages in thread

From: Greg Stark @ 2003-10-03 05:13 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@libertyrms.info>; +Cc: pgsql-performance


Christopher Browne <cbbrowne@libertyrms.info> writes:

> It would be very hairy to implement it correctly, and all this would
> cover is the single case of "SELECT COUNT(*) FROM SOME_TABLE;"
> 
> If you had a single WHERE clause attached, you would have to revert to
> walking through the tuples looking for the ones that are live and
> committed, which is true for any DBMS.

Well it would be handy for a few other cases as well. 

1 It would be useful for the case where you have a partial index with a
  matching where clause. The optimizer already considers using such indexes
  but it has to pay the cost of the tuple lookup, which is substantial.

2 It would be useful for the very common queries of the form 
  WHERE x IN (select id from foo where some_indexed_expression)

  (Or the various equivalent forms including outer joins that test to see if
  the matching record was found and don't retrieve any other columns in the
  select list.)

3 It would be useful for many-many relationships where the intermediate table
  has only the two primary key columns being joined. If you create a
  multi-column index on the two columns it shouldn't need to look up the
  tuple. This would be effectively be nearly equivalent to an "index organized
  table".


4 It would be useful for just about all the referential integrity queries...


I don't mean to say this is definitely a good thing. The tradeoff in
complexity and time to maintain the index pages would be large. But don't
dismiss it as purely a count(*) optimization hack.

I know Oracle is capable of it and it can speed up your query a lot when you
remove that last unnecessary column from a join table allowing oracle to skip
the step of reading the table.

-- 
greg




^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 06:22  Gregory S. Williamson <gsw@globexplorer.com>
  0 siblings, 0 replies; 30+ messages in thread

From: Gregory S. Williamson @ 2003-10-03 06:22 UTC (permalink / raw)
  To: Dror Matalon <dror@zapatec.com>; pgsql-performance


I can tell you that this is one of the first thing applications' programmers and IT managers notice. It can slightly tarnish postgres' image when it takes it many long seconds to do what other databases can do in a snap. The "whys and wherefores" can be hard to get across once they see the comparative numbers.

When I use Informix "dbaccess" it has a "status" which will tell me the row count of a table virtually instantly -- it can be locked out by a user with an exclusive lock so its not entirely independant of the table (like a stored value in one of the system catalog tables).

This is not to say Informix is "right" and Postgres is "wrong" ... but it  is something that virtually any newcomer will run into head long, with resulting bruises and contusions, not to mention confusion.

At the very least this needs to be VERY clearly explained right up front, along with some of the possible work-arounds, depending on what one is really after with this info.

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:	Dror Matalon [mailto:dror@zapatec.com]
Sent:	Thu 10/2/2003 9:27 PM
To:	pgsql-performance@postgresql.org
Cc:	
Subject:	Re: [PERFORM] count(*) slow on large tables


I smell a religious war in the aii:-). 
Can you go several days in a row without doing select count(*) on any
of your tables? 

I suspect that this is somewhat a domain specific issue. In some areas
you don't need to know the total number of rows in your tables, in
others you do. 

I also suspect that you're right, that end user applications don't use
this information as often as DBAs would. On the other hand, it seems
whenever you want to optimize your app (something relevant to this list),
one of the things you do need to know is the number of rows in your
table.

Dror

On Thu, Oct 02, 2003 at 10:08:18PM -0400, Christopher Browne wrote:
> The world rejoiced as dror@zapatec.com (Dror Matalon) wrote:
> > I don't have an opinion on how hard it would be to implement the
> > tracking in the indexes, but "select count(*) from some table" is, in my
> > experience, a query that people tend to run quite often. 
> > One of the databases that I've used, I believe it was Informix, had that
> > info cached so that it always new how many rows there were in any
> > table. It was quite useful.
> 
> I can't imagine why the raw number of tuples in a relation would be
> expected to necessarily be terribly useful.
> 
> I'm involved with managing Internet domains, and it's only when people
> are being pretty clueless that anyone imagines that "select count(*)
> from domains;" would be of any use to anyone.  There are enough "test
> domains" and "inactive domains" and other such things that the raw
> number of "things in the table" aren't really of much use.
> 
> - I _do_ care how many pages a table occupies, to some extent, as that
> determines whether it will fit in my disk space or not, but that's not
> COUNT(*).
> 
> - I might care about auditing the exact numbers of records in order to
> be assured that a data conversion process was done correctly.  But in
> that case, I want to do something a whole *lot* more detailed than
> mere COUNT(*).
> 
> I'm playing "devil's advocate" here, to some extent.  But
> realistically, there is good reason to be skeptical of the merits of
> using SELECT COUNT(*) FROM TABLE for much of anything.
> 
> Furthermore, the relation that you query mightn't be a physical
> "table."  It might be a more virtual VIEW, and if that's the case,
> bets are even MORE off.  If you go with the common dictum of "good
> design" that users don't directly access tables, but go through VIEWs,
> users may have no way to get at SELECT COUNT(*) FROM TABLE.
> -- 
> output = reverse("ac.notelrac.teneerf" "@" "454aa")
> http://www.ntlug.org/~cbbrowne/finances.html
> Rules  of  the  Evil  Overlord  #74.   "When  I  create  a  multimedia
> presentation of my plan designed  so that my five-year-old advisor can
> easily  understand the  details, I  will not  label the  disk "Project
> Overlord" and leave it lying on top of my desk."
> <http://www.eviloverlord.com/;
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
Dror Matalon, President
Zapatec Inc 
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 06:29  Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  parent: Dror Matalon <dror@zapatec.com>
  1 sibling, 1 reply; 30+ messages in thread

From: Shridhar Daithankar @ 2003-10-03 06:29 UTC (permalink / raw)
  To: Dror Matalon <dror@zapatec.com>; +Cc: pgsql-performance

Dror Matalon wrote:

> I smell a religious war in the aii:-). 
> Can you go several days in a row without doing select count(*) on any
> of your tables? 
> 
> I suspect that this is somewhat a domain specific issue. In some areas
> you don't need to know the total number of rows in your tables, in
> others you do. 

If I were you, I would have an autovacuum daemon running and rather than doing 
select count(*), I would look at stats generated by vacuums. They give 
approximate number of tuples and it should be good enough it is accurate within 
a percent.

Just another approach of achieving same thing.. Don't be religious about running 
a qeury from SQL prompt. That's it..

  Shridhar




^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 11:37  Christopher Browne <cbbrowne@acm.org>
  parent: Dror Matalon <dror@zapatec.com>
  1 sibling, 0 replies; 30+ messages in thread

From: Christopher Browne @ 2003-10-03 11:37 UTC (permalink / raw)
  To: pgsql-performance

Oops! dror@zapatec.com (Dror Matalon) was seen spray-painting on a wall:
> I smell a religious war in the aii:-). 
> Can you go several days in a row without doing select count(*) on any
> of your tables? 

I would be more likely, personally, to run "VACUUM VERBOSE ANALYZE",
which has useful side-effects :-).

> I suspect that this is somewhat a domain specific issue. In some
> areas you don't need to know the total number of rows in your
> tables, in others you do.

"Relationship tables," which don't contain data in their own right,
but which, instead, link together records in other tables, are likely
to have particularly useless COUNT(*)'s.

> I also suspect that you're right, that end user applications don't
> use this information as often as DBAs would. On the other hand, it
> seems whenever you want to optimize your app (something relevant to
> this list), one of the things you do need to know is the number of
> rows in your table.

Ah, but in the case of optimization, there's little need for
"transactionally safe, MVCC-managed, known-to-be-exact" values.
Approximations are plenty good enough to get the right plan.

Furthermore, it's not the number of rows that is most important when
optimizing queries; the number of pages are more relevant to the
matter, as that's what the database is slinging around.
-- 
(reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa"))
http://www3.sympatico.ca/cbbrowne/multiplexor.html
Rules of  the Evil Overlord #134. "If  I am escaping in  a large truck
and the hero is pursuing me in  a small Italian sports car, I will not
wait for the hero to pull up along side of me and try to force him off
the road  as he attempts to climb  aboard. Instead I will  slam on the
brakes  when he's  directly behind  me.  (A  rudimentary  knowledge of
physics can prove quite useful.)" <http://www.eviloverlord.com/;



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 12:36  Jeff <threshar@torgo.978.org>
  parent: Christopher Browne <cbbrowne@acm.org>
  1 sibling, 1 reply; 30+ messages in thread

From: Jeff @ 2003-10-03 12:36 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance

On Thu, 2 Oct 2003, Christopher Browne wrote:

> I can't imagine why the raw number of tuples in a relation would be
> expected to necessarily be terribly useful.
>

We use stuff like that for reporting queries.

example:
On our message boards each post is a row.  The powers that be like to know
how many posts there are total (In addition to 'today')-
select count(*) from posts is how it has been
done on our informix db.  With our port to PG I instead select reltuples
pg_class.

I know when I login to a new db (or unknown to me db) the first thing I do
is look at tables and see what sort of data there is.. but in code I'd
rarely do that.

I know some monitoring things around here also do a select count(*) on
sometable to ensure it is growing, but like you said, this is easily done
with the number of pages as well.

yes. Informix caches this data. I believe Oracle does too.

Mysql with InnoDB does the same thing PG does. (MyISAM caches it)

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/





^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 15:48  Jean-Luc Lachance <jllachan@nsd.ca>
  parent: Christopher Browne <cbbrowne@libertyrms.info>
  3 siblings, 1 reply; 30+ messages in thread

From: Jean-Luc Lachance @ 2003-10-03 15:48 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@libertyrms.info>; +Cc: pgsql-performance

Well I can think of many more case where it would be usefull:

SELECT COUNT(DISTINCT x) FROM ...
SELECT COUNT(*) FROM ... WHERE x = ?


Also having transaction number (visibility) would tip the balance more
toward index_scan than seq_scan because you do not have to look up
visibility in the data file. We all know this has been an issue many
times.
Having a different index file structure when the index is not UNIQUE
would help too.
The last page of a non unique index could hold more stats.



Christopher Browne wrote:
> 
> jllachan@nsd.ca (Jean-Luc Lachance) writes:
> > That's one of the draw back of MVCC.
> > I once suggested that the transaction number and other house keeping
> > info be included in the index, but was told to forget it...
> > It would solve once and for all the issue of seq_scan vs index_scan.
> > It would simplify the aggregate problem.
> 
> It would only simplify _one_ case, namely the case where someone cares
> about the cardinality of a relation, and it would do that at
> _considerable_ cost.
> 
> A while back I outlined how this would have to be done, and for it to
> be done efficiently, it would be anything BUT simple.
> 
> It would be very hairy to implement it correctly, and all this would
> cover is the single case of "SELECT COUNT(*) FROM SOME_TABLE;"
> 
> If you had a single WHERE clause attached, you would have to revert to
> walking through the tuples looking for the ones that are live and
> committed, which is true for any DBMS.
> 
> And it still begs the same question, of why the result of this query
> would be particularly meaningful to anyone.  I don't see the
> usefulness; I don't see the value of going to the considerable effort
> of "fixing" this purported problem.
> --
> let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
> <http://dev6.int.libertyrms.com/;
> Christopher Browne
> (416) 646 3304 x124 (land)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 16:50  Hilary Forbes <hforbes@dmr.co.uk>
  0 siblings, 0 replies; 30+ messages in thread

From: Hilary Forbes @ 2003-10-03 16:50 UTC (permalink / raw)
  To: Gregory S. Williamson <gsw@globexplorer.com>; +Cc: pgsql-performance

We frequently need to know the number of tuples in a table although sometimes we do have WHERE status='X' for example but this often doesn't guarantee an indexed scan. And yes, my reasons are the same - reporting figures eg number of bookings made since the system was introduced.   Have you tried doing

SELECT count(pkey)

rather than count(*)

where pkey is the primary key (assuming you have a single field that is a primary key or a unique indexed key).  This is MUCH faster in my experience.  If you don't have such an animal, I'd seriously suggesting putting in a serial number and recreate the table with that as the primary key.

The vacuuming bit is not accurate enough for us in many instances.  Also a count can be easily fed into other programs/web pages etc without having to parse the vacuum output.

Hilary

At 23:22 02/10/2003 -0700, you wrote:

>I can tell you that this is one of the first thing applications' programmers and IT managers notice. It can slightly tarnish postgres' image when it takes it many long seconds to do what other databases can do in a snap. The "whys and wherefores" can be hard to get across once they see the comparative numbers.
>
>When I use Informix "dbaccess" it has a "status" which will tell me the row count of a table virtually instantly -- it can be locked out by a user with an exclusive lock so its not entirely independant of the table (like a stored value in one of the system catalog tables).
>
>This is not to say Informix is "right" and Postgres is "wrong" ... but it  is something that virtually any newcomer will run into head long, with resulting bruises and contusions, not to mention confusion.
>
>At the very least this needs to be VERY clearly explained right up front, along with some of the possible work-arounds, depending on what one is really after with this info.
>
>Greg Williamson
>DBA
>GlobeXplorer LLC
>
>-----Original Message-----
>From:   Dror Matalon [mailto:dror@zapatec.com]
>Sent:   Thu 10/2/2003 9:27 PM
>To:     pgsql-performance@postgresql.org
>Cc:     
>Subject:        Re: [PERFORM] count(*) slow on large tables
>
>
>I smell a religious war in the aii:-). 
>Can you go several days in a row without doing select count(*) on any
>of your tables? 
>
>I suspect that this is somewhat a domain specific issue. In some areas
>you don't need to know the total number of rows in your tables, in
>others you do. 
>
>I also suspect that you're right, that end user applications don't use
>this information as often as DBAs would. On the other hand, it seems
>whenever you want to optimize your app (something relevant to this list),
>one of the things you do need to know is the number of rows in your
>table.
>
>Dror
>
>On Thu, Oct 02, 2003 at 10:08:18PM -0400, Christopher Browne wrote:
>> The world rejoiced as dror@zapatec.com (Dror Matalon) wrote:
>> > I don't have an opinion on how hard it would be to implement the
>> > tracking in the indexes, but "select count(*) from some table" is, in my
>> > experience, a query that people tend to run quite often. 
>> > One of the databases that I've used, I believe it was Informix, had that
>> > info cached so that it always new how many rows there were in any
>> > table. It was quite useful.
>> 
>> I can't imagine why the raw number of tuples in a relation would be
>> expected to necessarily be terribly useful.
>> 
>> I'm involved with managing Internet domains, and it's only when people
>> are being pretty clueless that anyone imagines that "select count(*)
>> from domains;" would be of any use to anyone.  There are enough "test
>> domains" and "inactive domains" and other such things that the raw
>> number of "things in the table" aren't really of much use.
>> 
>> - I _do_ care how many pages a table occupies, to some extent, as that
>> determines whether it will fit in my disk space or not, but that's not
>> COUNT(*).
>> 
>> - I might care about auditing the exact numbers of records in order to
>> be assured that a data conversion process was done correctly.  But in
>> that case, I want to do something a whole *lot* more detailed than
>> mere COUNT(*).
>> 
>> I'm playing "devil's advocate" here, to some extent.  But
>> realistically, there is good reason to be skeptical of the merits of
>> using SELECT COUNT(*) FROM TABLE for much of anything.
>> 
>> Furthermore, the relation that you query mightn't be a physical
>> "table."  It might be a more virtual VIEW, and if that's the case,
>> bets are even MORE off.  If you go with the common dictum of "good
>> design" that users don't directly access tables, but go through VIEWs,
>> users may have no way to get at SELECT COUNT(*) FROM TABLE.
>> -- 
>> output = reverse("ac.notelrac.teneerf" "@" "454aa")
>> http://www.ntlug.org/~cbbrowne/finances.html
>> Rules  of  the  Evil  Overlord  #74.   "When  I  create  a  multimedia
>> presentation of my plan designed  so that my five-year-old advisor can
>> easily  understand the  details, I  will not  label the  disk "Project
>> Overlord" and leave it lying on top of my desk."
>> <http://www.eviloverlord.com/;
>> 
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>
>-- 
>Dror Matalon, President
>Zapatec Inc 
>1700 MLK Way
>Berkeley, CA 94709
>http://www.zapatec.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster 


Hilary Forbes
-------------
DMR Computer Limited:   http://www.dmr.co.uk/
Direct line:  01689 889950
Switchboard:  (44) 1689 860000  Fax: (44) 1689 860330
E-mail:  hforbes@dmr.co.uk

**********************************************************




^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-03 21:55  Christopher Browne <cbbrowne@acm.org>
  parent: Jean-Luc Lachance <jllachan@nsd.ca>
  0 siblings, 0 replies; 30+ messages in thread

From: Christopher Browne @ 2003-10-03 21:55 UTC (permalink / raw)
  To: pgsql-performance

In the last exciting episode, jllachan@nsd.ca (Jean-Luc Lachance) wrote:
> Well I can think of many more case where it would be usefull:
>
> SELECT COUNT(DISTINCT x) FROM ...
> SELECT COUNT(*) FROM ... WHERE x = ?

Those are precisely the cases that the "other databases" ALSO fall
down on.

Maintaining those sorts of statistics would lead [in _ANY_ database;
PostgreSQL has no disadvantage in this] to needing for each and every
update to update a whole host of statistic values.

It would be fairly reasonable to have a trigger, in PostgreSQL, to
manage this sort of information.  It would not be outrageously
difficult to substantially improve performance of queries, at the
considerable cost that each and every update would have to update a
statistics table.

If you're doing a whole lot of these sorts of queries, then it is a
reasonable idea to create appropriate triggers for the (probably very
few) tables where you are doing these counts.

But the notion that this should automatically be applied to all tables
always is a dangerous one.  It would make update performance Suck
Badly, because the extra statistical updates would be quite expensive.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','cbbrowne.com').
http://www3.sympatico.ca/cbbrowne/multiplexor.html
I'm sorry Dave, I can't let you do that.
Why don't you lie down and take a stress pill?



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-04 09:37  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Jeff <threshar@torgo.978.org>
  0 siblings, 0 replies; 30+ messages in thread

From: Christopher Kings-Lynne @ 2003-10-04 09:37 UTC (permalink / raw)
  To: Jeff <threshar@torgo.978.org>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance


> On our message boards each post is a row.  The powers that be like to know
> how many posts there are total (In addition to 'today')-
> select count(*) from posts is how it has been
> done on our informix db.  With our port to PG I instead select reltuples
> pg_class.

We have exactly the same situation, except we just added a 'num_replies' 
field to each thread and a 'num_posts' field to each forum, so that 
getting that information out is a very fast operation.  Because, of 
course, there are hundreds of times more reads of that information than 
writes...

Chris




^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-04 15:56  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Christopher Browne <cbbrowne@libertyrms.info>
  3 siblings, 1 reply; 30+ messages in thread

From: Bruce Momjian @ 2003-10-04 15:56 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@libertyrms.info>; +Cc: pgsql-performance

Christopher Browne wrote:
> jllachan@nsd.ca (Jean-Luc Lachance) writes:
> > That's one of the draw back of MVCC.  
> > I once suggested that the transaction number and other house keeping
> > info be included in the index, but was told to forget it...
> > It would solve once and for all the issue of seq_scan vs index_scan.
> > It would simplify the aggregate problem.
> 
> It would only simplify _one_ case, namely the case where someone cares
> about the cardinality of a relation, and it would do that at
> _considerable_ cost.
> 
> A while back I outlined how this would have to be done, and for it to
> be done efficiently, it would be anything BUT simple.  
> 
> It would be very hairy to implement it correctly, and all this would
> cover is the single case of "SELECT COUNT(*) FROM SOME_TABLE;"

We do have a TODO item:

	* Consider using MVCC to cache count(*) queries with no WHERE clause

The idea is to cache a recent count of the table, then have
insert/delete add +/- records to the count.  A COUNT(*) would get the
main cached record plus any visible +/- records.  This would allow the
count to return the proper value depending on the visibility of the
requesting transaction, and it would require _no_ heap or index scan.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-04 16:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 2 replies; 30+ messages in thread

From: Tom Lane @ 2003-10-04 16:49 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We do have a TODO item:
> 	* Consider using MVCC to cache count(*) queries with no WHERE clause

> The idea is to cache a recent count of the table, then have
> insert/delete add +/- records to the count.  A COUNT(*) would get the
> main cached record plus any visible +/- records.  This would allow the
> count to return the proper value depending on the visibility of the
> requesting transaction, and it would require _no_ heap or index scan.

... and it would give the wrong answers.  Unless the cache is somehow
snapshot-aware, so that it can know which other transactions should be
included in your count.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-04 17:48  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 30+ messages in thread

From: Bruce Momjian @ 2003-10-04 17:48 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > We do have a TODO item:
> > 	* Consider using MVCC to cache count(*) queries with no WHERE clause
> 
> > The idea is to cache a recent count of the table, then have
> > insert/delete add +/- records to the count.  A COUNT(*) would get the
> > main cached record plus any visible +/- records.  This would allow the
> > count to return the proper value depending on the visibility of the
> > requesting transaction, and it would require _no_ heap or index scan.
> 
> ... and it would give the wrong answers.  Unless the cache is somehow
> snapshot-aware, so that it can know which other transactions should be
> included in your count.

The cache is an ordinary table, with xid's on every row.  I meant it
would require no index/heap scans of the large table --- it would still
require a scan of the "count" table.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-04 17:51  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 30+ messages in thread

From: Tom Lane @ 2003-10-04 17:51 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> ... and it would give the wrong answers.  Unless the cache is somehow
>> snapshot-aware, so that it can know which other transactions should be
>> included in your count.

> The cache is an ordinary table, with xid's on every row.  I meant it
> would require no index/heap scans of the large table --- it would still
> require a scan of the "count" table.

Oh, that idea.  Yeah, I think we had concluded it might work.  You'd
better make the TODO item link to that discussion, because there's sure
been plenty of discussion of ideas that wouldn't work.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-04 18:19  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 3 replies; 30+ messages in thread

From: Bruce Momjian @ 2003-10-04 18:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> ... and it would give the wrong answers.  Unless the cache is somehow
> >> snapshot-aware, so that it can know which other transactions should be
> >> included in your count.
> 
> > The cache is an ordinary table, with xid's on every row.  I meant it
> > would require no index/heap scans of the large table --- it would still
> > require a scan of the "count" table.
> 
> Oh, that idea.  Yeah, I think we had concluded it might work.  You'd
> better make the TODO item link to that discussion, because there's sure
> been plenty of discussion of ideas that wouldn't work.

OK, I beefed up the TODO:

	* Use a fixed row count and a +/- count with MVCC visibility rules
	  to allow fast COUNT(*) queries with no WHERE clause(?)

I can always give the details if someone asks.  It doesn't seem complex
enough for a separate TODO.detail item.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-04 18:34  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 0 replies; 30+ messages in thread

From: Tom Lane @ 2003-10-04 18:34 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> It doesn't seem complex enough for a separate TODO.detail item.

I thought it was, if only because it is so easy to think of wrong
implementations.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-04 23:33  Christopher Browne <cbbrowne@acm.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 30+ messages in thread

From: Christopher Browne @ 2003-10-04 23:33 UTC (permalink / raw)
  To: pgsql-performance

Quoth tgl@sss.pgh.pa.us (Tom Lane):
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> We do have a TODO item:
>> 	* Consider using MVCC to cache count(*) queries with no WHERE clause
>
>> The idea is to cache a recent count of the table, then have
>> insert/delete add +/- records to the count.  A COUNT(*) would get the
>> main cached record plus any visible +/- records.  This would allow the
>> count to return the proper value depending on the visibility of the
>> requesting transaction, and it would require _no_ heap or index scan.
>
> ... and it would give the wrong answers.  Unless the cache is somehow
> snapshot-aware, so that it can know which other transactions should be
> included in your count.

[That's an excellent summary that Bruce did of what came out of the
previous discussion...]

If this "cache" was a table, itself, the visibility of its records
should be identical to that of the visibility of the "real" records.
+/- records would become visible when the transaction COMMITed, at the
very same time the source records became visible.

I thought, at one point, that it would be a slick idea for "record
compression" to take place automatically; when you do a COUNT(*), the
process would include compressing multiple records down to one.
Unfortunately, that turns out to be Tremendously Evil if the same
COUNT(*) were being concurrently processed in multiple transactions.
Both would repeat much the same work, and this would ultimately lead
to one of the transactions aborting.  [I recently saw this effect
occur, um, a few times...]

For this not to have Evil Effects on unsuspecting transactions, we
would instead require some process analagous to VACUUM, where a single
transaction would be used to compress the "counts table" down to one
record per table.  Being independent of "user transactions," it could
safely compress the data without injuring unsuspecting transactions.

But in most cases, the cost of this would be pretty prohibitive.
Every transaction that adds a record to a table leads to a record
being added to table "pg_exact_row_counts".  If transactions typically
involve adding ONE row to any given table, this effectively doubles
the update traffic.  Ouch.  That means that in a _real_
implementation, it would make sense to pick and choose the tables that
would be so managed.

In my earlier arguing of "You don't really want that!", while I may
have been guilty of engaging in a _little_ hyperbole, I was certainly
_not_ being facetious overall.  At work, we tell the developers "avoid
doing COUNT(*) inside ordinary transactions!", and that is certainly
NOT facetious comment.  I recall a case a while back where system
performance was getting brutalized by a lurking COUNT(*).  (Combined
with some other pathological behaviour, of course!)  And note that
this wasn't a query that the TODO item could address; it was of the
form "SELECT COUNT(*) FROM SOME_TABLE WHERE OWNER = VALUE;"

As you have commented elsewhere in the thread, much of the time, the
point of asking for COUNT(*) is often to get some idea of table size,
where the precise number isn't terribly important in comparison with
getting general magnitude.  Improving the ability to get approximate
values would be of some value.

I would further argue that "SELECT COUNT(*) FROM TABLE" isn't
particularly useful even when precision _is_ important.  If I'm
working on reports that would be used to reconcile things, the queries
I use are a whole lot more involved than that simple form.  It is far
more likely that I'm using a GROUP BY.

It is legitimate to get wishful and imagine that it would be nice if
we could get the value of that query "instantaneously."  It is also
legitimate to think that the effort required to implement that might
be better used on improving other things.
-- 
(reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa"))
http://www3.sympatico.ca/cbbrowne/
"very few people approach me in real life and insist on proving they
are drooling idiots."  -- Erik Naggum, comp.lang.lisp



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-05 18:57  Josh Berkus <josh@agliodbs.com>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 1 reply; 30+ messages in thread

From: Josh Berkus @ 2003-10-05 18:57 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance

Bruce,

> OK, I beefed up the TODO:
>
> 	* Use a fixed row count and a +/- count with MVCC visibility rules
> 	  to allow fast COUNT(*) queries with no WHERE clause(?)
>
> I can always give the details if someone asks.  It doesn't seem complex
> enough for a separate TODO.detail item.

Hmmm ... this doesn't seem effort-worthy to me.   How often does anyone do 
COUNT with no where clause, except GUIs that give you a record count?  (of 
course, as always, if someone wants to code it, feel free ...)

And for those GUIs, wouldn't it be 97% as good to run an ANALYZE and give the 
approximate record counts for large tables?

As for counts with a WHERE clause, this is obviously up to the user.  Joe 
Conway and I tested using a C trigger to track some COUNT ... GROUP BY values 
for large tables based on additive numbers.   It worked fairly well for 
accuracy, but the performance penalty on data writes was significant ... 8% 
to 25% penalty for UPDATES, depending on the frequency and batch size (> 
frequency > batch size -->  > penalty)

It's possible that this could be improved through some mechanism more tightly 
integrated with the source code.   However,the coding effort would be 
significant ( 12-20 hours ) and it's possible that there would be no 
improvement, which is why we didn't do it.

We also discussed an asynchronous aggregates collector that would work 
something like the statistics collector, and keep pre-programmmed aggregate 
data, updating during "low-activity" periods.  This would significantly 
reduce the performance penalty, but at the cost of accuracy ... that is, a 
1%-5% variance on high-activity tables would be unavoidable, and all cached 
aggregates would have to be recalculated on database restart, significantly 
slowing down startup.   Again, we felt that the effort-result payoff was not 
worthwhile.

Overall, I think the stuff we already have planned ... the hash aggregates in 
7.4 and Tom's suggestion of adding an indexable flag to pg_aggs ... are far 
more likely to yeild useful fruit than any caching plan.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-05 19:11  Rod Taylor <rbt@rbt.ca>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 30+ messages in thread

From: Rod Taylor @ 2003-10-05 19:11 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Tom Lane <tgl@sss.pgh.pa.us>; Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance

> And for those GUIs, wouldn't it be 97% as good to run an ANALYZE and give the 
> approximate record counts for large tables?

Interfaces which run a COUNT(*) like that are broken by design. They
fail to consider the table may really be a view which of course could
not be cached with results like that and may take days to load a full
result set (we had some pretty large views in an old billing system).

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-06 06:06  Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 1 reply; 30+ messages in thread

From: Shridhar Daithankar @ 2003-10-06 06:06 UTC (permalink / raw)
  To: pgsql-performance

Bruce Momjian wrote:
> OK, I beefed up the TODO:
> 
> 	* Use a fixed row count and a +/- count with MVCC visibility rules
> 	  to allow fast COUNT(*) queries with no WHERE clause(?)
> 
> I can always give the details if someone asks.  It doesn't seem complex
> enough for a separate TODO.detail item.

May I propose alternate approach for this optimisation?

- Postgresql allows to maintain user defined variables in shared memory.
- These variables obey transactions but do not get written to disk at all.
- There should be a facility to detect whether such a variable is initialized or 
not.

How it will help? This is in addition to trigger proposal that came up earlier. 
With  triggers it's not possible to make values visible across backends unless 
trigger updates a table, which eventually leads to vacuum/dead tuples problem.

1. User creates a trigger to check updates/inserts for certain conditions.
2. It updates the count as and when required.
3. If the trigger detects the count is not initialized, it would issue the same 
query first time. There is no avoiding this issue.

Besides providing facility of resident variables could be used imaginatively as 
well.

Does this make sense? IMO this is more generalised approach over all.

Just a thought.

  Shridhar






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-06 15:08  Harald Fuchs <nospam@sap.com>
  parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 0 replies; 30+ messages in thread

From: Harald Fuchs @ 2003-10-06 15:08 UTC (permalink / raw)
  To: pgsql-performance

In article <3F7D172E.3060107@persistent.co.in>,
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:

> Dror Matalon wrote:
>> I smell a religious war in the aii:-). Can you go several days in a
>> row without doing select count(*) on any
>> of your tables? I suspect that this is somewhat a domain specific
>> issue. In some areas
>> you don't need to know the total number of rows in your tables, in
>> others you do.

> If I were you, I would have an autovacuum daemon running and rather
> than doing select count(*), I would look at stats generated by
> vacuums. They give approximate number of tuples and it should be good
> enough it is accurate within a percent.

The stats might indeed be a good estimate presumed there were not many
changes since the last VACUUM.  But how about a variant of COUNT(*)
using an index?  It would not be quite exact since it might contain
tuples not visible in the current transaction, but it might be a much
better estimate than the stats.




^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: count(*) slow on large tables
@ 2003-10-06 17:01  Sean Chittenden <sean@chittenden.org>
  parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 0 replies; 30+ messages in thread

From: Sean Chittenden @ 2003-10-06 17:01 UTC (permalink / raw)
  To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; +Cc: pgsql-performance

> How it will help? This is in addition to trigger proposal that came
> up earlier. With triggers it's not possible to make values visible
> across backends unless trigger updates a table, which eventually
> leads to vacuum/dead tuples problem.
> 
> 1. User creates a trigger to check updates/inserts for certain conditions.
> 2. It updates the count as and when required.
> 3. If the trigger detects the count is not initialized, it would issue the 
> same query first time. There is no avoiding this issue.
> 
> Besides providing facility of resident variables could be used
> imaginatively as well.
> 
> Does this make sense? IMO this is more generalised approach over all.

I do this _VERY_ frequently in my databases, only I have my stored
procs do the aggregate in a predefined MVCC table that's always there.
Here's a denormalized version for public consumption/thought:

CREATE TABLE global.dba_aggregate_cache (
  dbl TEXT NOT NULL,        -- The database location, doesn't need to be
                            -- qualified (ex: schema.table.col)
  op TEXT NOT NULL,         -- The operation, SUM, COUNT, etc.
  qual TEXT,                -- Any kind of conditional, such as a where clause
  val_int INT,              -- Whatever the value is, of type INT
  val_bigint BIGINT,        -- Whatever the value is, of type BIGINT
  val_text TEXT,            -- Whatever the value is, of type TEXT
  val_bytea BYTEA,          -- Whatever the value is, of type BYTEA
);
CREATE UNIQUE INDEX dba_aggregate_cache_dbl_op_udx ON global.dba_aggregate_cache(dbl,op);

Then, I use a function to retrieve this value instead of a SELECT
COUNT(*).

SELECT public.cache_count('dbl','qual');  -- In this case, the op is COUNT
SELECT public.cache_count('dbl');         -- Returns the COUNT for the table listed in the dbl

Then, I create 4 or 5 functions (depends on the op I'm performing):

1) A private function that _doesn't_ run as security definer, that
   populates the global.dba_aggregate_cache row if it's empty.
2) A STABLE function for SELECTs, if the row doesn't exist, then it
   calls function #1 to populate its existence.
3) A STABLE function for INSERTs, if the row doesn't exist, then it
   calls function #1 to populate its existence, then adds the
   necessary bits to make it accurate.
4) A STABLE function for DELETEs, if the row doesn't exist, then it
   calls function #1 to populate its existence, then deletes the
   necessary bits to make it accurate.
5) A STABLE function for UPDATEs, if the row doesn't exist, then it
   calls function #1 to populate its existence, then updates the
   necessary bits to make it accurate.  It's not uncommon for me to
   not have an UPDATE function/trigger.

Create triggers for functions 2-5, and test away.  It's MVCC,
searching through a table that's INDEX'ed for a single row is
obviously vastly faster than a seqscan/aggregate.  If I need any kind
of an aggregate to be fast, I use this system with a derivation of the
above table.  The problem with it being that I have to retrain others
to use cache_count(), or some other function instead of using
COUNT(*).

That said, it'd be nice if there were a way to tell PostgreSQL to do
the above for you and teach COUNT(*), SUM(*), or other aggregates to
use an MVCC backed cache similar to the above.  If people want their
COUNT's to be fast, then they have to live with the INSERT, UPDATE,
DELETE cost.  The above doesn't work with anything complex such as
join's, but it's certainly a start and I think satisfies everyone's
gripes other than the tuple churn that _does_ happen (*nudge nudge*,
pg_autovacuum could be integrated into the backend to handle this).
Those worried about performance, the pages that are constantly being
recycled would likely stay in disk cache (PG or the OS).  There's
still some commit overhead, but still... no need to over optimize by
requiring the table to be stored in the out dated, slow, and over used
shm (also, *nudge nudge*).

Anyway, let me throw that out there as a solution that I use and it
works quite well.  I didn't explain the use of the qual column, but I
think those who grasp the above way of handling things probably grok
how to use the qual column in a dynamically executed query.

CREATE AGGREGATE CACHE anyone?

-sc

-- 
Sean Chittenden



^ permalink  raw  reply  [nested|flat] 30+ messages in thread


end of thread, other threads:[~2003-10-06 17:01 UTC | newest]

Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-02 19:15 count(*) slow on large tables Dror Matalon <dror@zapatec.com>
2003-10-02 19:36 ` Tomasz Myrta <jasiek@klaster.net>
2003-10-02 19:39 ` Bruno Wolff III <bruno@wolff.to>
2003-10-02 19:58   ` Bruno Wolff III <bruno@wolff.to>
2003-10-02 21:29   ` Jean-Luc Lachance <jllachan@nsd.ca>
2003-10-02 21:57     ` Christopher Browne <cbbrowne@libertyrms.info>
2003-10-02 22:33       ` Dror Matalon <dror@zapatec.com>
2003-10-03 02:08         ` Christopher Browne <cbbrowne@acm.org>
2003-10-03 04:27           ` Dror Matalon <dror@zapatec.com>
2003-10-03 06:29             ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-10-06 15:08               ` Harald Fuchs <nospam@sap.com>
2003-10-03 11:37             ` Christopher Browne <cbbrowne@acm.org>
2003-10-03 12:36           ` Jeff <threshar@torgo.978.org>
2003-10-04 09:37             ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-10-03 05:13       ` Greg Stark <gsstark@mit.edu>
2003-10-03 15:48       ` Jean-Luc Lachance <jllachan@nsd.ca>
2003-10-03 21:55         ` Christopher Browne <cbbrowne@acm.org>
2003-10-04 15:56       ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-04 16:49         ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-04 17:48           ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-04 17:51             ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-04 18:19               ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-04 18:34                 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-05 18:57                 ` Josh Berkus <josh@agliodbs.com>
2003-10-05 19:11                   ` Rod Taylor <rbt@rbt.ca>
2003-10-06 06:06                 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-10-06 17:01                   ` Sean Chittenden <sean@chittenden.org>
2003-10-04 23:33           ` Christopher Browne <cbbrowne@acm.org>
2003-10-03 06:22 Re: count(*) slow on large tables Gregory S. Williamson <gsw@globexplorer.com>
2003-10-03 16:50 Re: count(*) slow on large tables Hilary Forbes <hforbes@dmr.co.uk>

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