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

* select count(*) on large tables
@ 2004-04-08 09:43 Cosimo Streppone <cosimo@streppone.it>
  2004-04-08 10:54 ` Re: select count(*) on large tables Dennis Bjorklund <db@zigo.dhs.org>
  2004-04-08 14:09 ` Re: select count(*) on large tables Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Cosimo Streppone @ 2004-04-08 09:43 UTC (permalink / raw)
  To: pgsql-performance

Hello,

I've followed the last discussion about the particular case of
"select count(*)"s on large tables being somewhat slow.

I've seen also this issue already on the todo list, so I know
it is not a simple question.
This problem arises for me on very large tables, which I mean
starting from 1 million rows and above.

The alternative solution I tried, that has an optimal
speed up, unfortunately is not a way out, and it is based
on "EXPLAIN SELECT count(*)" output parsing, which
is obviously *not* reliable.

The times always get better doing a vacuum (and eventually
reindex) of the table, and they slowly lower again.

Is there an estimate time for this issue to be resolved?
Can I help in some way (code, test cases, ...)?

-- 
Cosimo




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

* Re: select count(*) on large tables
  2004-04-08 09:43 select count(*) on large tables Cosimo Streppone <cosimo@streppone.it>
@ 2004-04-08 10:54 ` Dennis Bjorklund <db@zigo.dhs.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Dennis Bjorklund @ 2004-04-08 10:54 UTC (permalink / raw)
  To: Cosimo Streppone <cosimo@streppone.it>; +Cc: pgsql-performance

On Thu, 8 Apr 2004, Cosimo Streppone wrote:

> The alternative solution I tried, that has an optimal
> speed up, unfortunately is not a way out, and it is based
> on "EXPLAIN SELECT count(*)" output parsing, which
> is obviously *not* reliable.

Try this to get the estimate:

   SELECT relname, reltuples from pg_class order by relname;

> The times always get better doing a vacuum (and eventually
> reindex) of the table, and they slowly lower again.

Yes, the estimate is updated by the analyze.

> Is there an estimate time for this issue to be resolved?

It's not so easy to "fix". The naive fixes makes other operations slower,
most notably makes things less concurrent which is bad since it wont scale 
as good for many users then.

You can always keep the count yourself and have some triggers that update 
the count on each insert and delete on the table. It will of course make 
all inserts and deletes slower, but if you count all rows often maybe it's 
worth it. Most people do not need to count all rows in a table anyway. You 
usually count all rows such as this and that (some condition).

-- 
/Dennis Björklund




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

* Re: select count(*) on large tables
  2004-04-08 09:43 select count(*) on large tables Cosimo Streppone <cosimo@streppone.it>
@ 2004-04-08 14:09 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2004-04-08 14:09 UTC (permalink / raw)
  To: Cosimo Streppone <cosimo@streppone.it>; +Cc: pgsql-performance

Cosimo Streppone <cosimo@streppone.it> writes:
> Is there an estimate time for this issue to be resolved?

Approximately never.  It's a fundamental feature of Postgres' design.

As noted by Dennis, you can look at the pg_class statistics if a recent
estimate is good enough, or you can build user-level tracking tools if
you'd rather have fast count(*) than concurrent update capability.  But
don't sit around waiting for the developers to "fix this bug", because
it isn't a bug and it isn't going to be fixed.

			regards, tom lane



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


end of thread, other threads:[~2004-04-08 14:09 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-08 09:43 select count(*) on large tables Cosimo Streppone <cosimo@streppone.it>
2004-04-08 10:54 ` Dennis Bjorklund <db@zigo.dhs.org>
2004-04-08 14:09 ` Tom Lane <tgl@sss.pgh.pa.us>

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