pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Faster 'select count(*) from table' ?
2+ messages / 2 participants
[nested] [flat]

* Faster 'select count(*) from table' ?
@ 2003-02-25 22:44  Matt Mello <alien@spaceship.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Matt Mello @ 2003-02-25 22:44 UTC (permalink / raw)
  To: pgsql-performance

Does anyone know if there is a fast way to find out how many records are 
in a table?

"Select count(*) from table" is very slow.

I would think that PG would keep up with the number of undeleted rows on 
a realtime basis.  Is that the case?  If so, how would I query it?

Hope this is the correct list for this question.

Thanks!

-- 
Matt Mello





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

* Re: Faster 'select count(*) from table' ?
@ 2003-02-25 23:25  scott.marlowe <scott.marlowe@ihs.com>
  parent: Matt Mello <alien@spaceship.com>
  0 siblings, 0 replies; 2+ messages in thread

From: scott.marlowe @ 2003-02-25 23:25 UTC (permalink / raw)
  To: Matt Mello <alien@spaceship.com>; +Cc: pgsql-performance

On Tue, 25 Feb 2003, Matt Mello wrote:

> Does anyone know if there is a fast way to find out how many records are 
> in a table?
> 
> "Select count(*) from table" is very slow.
> 
> I would think that PG would keep up with the number of undeleted rows on 
> a realtime basis.  Is that the case?  If so, how would I query it?

Sorry, it doesn't, and it's one of the areas that having an MVCC style 
database costs you.  Also, if postgresql kept up with this automatically, 
it would have an overhead for each table, but how often do you use it on 
ALL your tables?  Most the time, folks use count(*) on a few tables only, 
and it would be a waste to have a seperate counting mechanism for all 
tables when you'd only need it for a few.

The general mailing list has several postings in the last 12 months about 
how to setup a trigger to a single row table that keeps the current 
count(*) of the master table.

If you need a rough count, you can get one from the statistics gathered by 
analyze in the pg_* tables.




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


end of thread, other threads:[~2003-02-25 23:25 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-25 22:44 Faster 'select count(*) from table' ? Matt Mello <alien@spaceship.com>
2003-02-25 23:25 ` scott.marlowe <scott.marlowe@ihs.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