Re: [SQLObject] PostgreSQL: costly count

[Frank B. <fb...@fo...>]

Hallo,
Sidnei da Silva hat gesagt: // Sidnei da Silva wrote:

> | >>>
> | 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.
> | <<<
> | 
> | pg_class is a mysterious table indeed, but it's got lots of good magic 
> | in it.  (I'm not even sure if this is the right magic, but worth a try)
> 
> Humm... It seems to be the right one, but how can I measure which one
> is faster? In my 11k table, both are blazingly fast :)

Wow, this runs at the speed of light:

fbar=> explain select reltuples from pg_class where relname = 'normal';
NOTICE:  QUERY PLAN:

Seq Scan on pg_class  (cost=0.00..4.49 rows=1 width=4)

EXPLAIN
fbar=> explain select count(*)from normal;
NOTICE:  QUERY PLAN:

Aggregate  (cost=13239.34..13239.34 rows=1 width=0)
  ->  Seq Scan on normal  (cost=0.00..13098.07 rows=56507 width=0)

EXPLAIN

Doing both selects by hand, the one with pg_class returns practically
immediatly, the standard count has a duration of about 3.4 seconds, as
far as my rhythm feel tells me (but I'm a musician, so that's quite
accurate ;)

Now, how to put this to use?

ciao
-- 
 Frank Barknecht                               _ ______footils.org__