Re: Accessing database statistics

Even faster than a trigger (you avoid the overhead of calling a function)
you can use rules to manage this. I do something similar for versioning:

-- tracking rows here
CREATE TABLE OrgPeople (
....
);

CREATE TABLE TableCount (
TableName NAME NOT NULL,
-- use INT8 if that's needed
TableCount INTEGER NOT NULL
);
INSERT INTO TableCount (TableName,TableCount) VALUES
('OrgPeople'::name,0::integer);

CREATE RULE OrgPeopleIns AS ON INSERT TO OrgPeople DO
UPDATE TableCount SET Count = Count + 1 WHERE TableName =
'orgpeople'::name;
CREATE RULE OrgPeopleDel AS ON DELETE TO OrgPeople DO
UPDATE TableCount SET Count = Count - 1 WHERE TableName =
'orgpeople'::name;

Joshua b. Jore ; http://www.greentechnologist.org ; 1121 1233 1311 200
1201 1302 1211 200 1201 1303 200 1300 1233 1313 1211 1302 1212 1311 1230
200 1201 1303 200 1321 1233 1311 1302 200 1211 1232 1211 1231 1321 200
1310 1220 1221 1232 1223 1303 200 1321 1233 1311 200 1201 1302 1211 232
200 1112 1233 1310 1211 200 1013 1302 1211 1211 1232 201 22

On Thu, 30 May 2002, Henshall, Stuart - WCP wrote:

> count(*) does a scan of the appropriate table.
> If you really need a fast count you could try having a trigger update a row
> in another
> table to +1 every time a row is inserted and -1 every time a row deleted.
> However this
> could lead to uneeded contention. It is also worth considering the case of a
> yet to be
> commited transaction that has inserted/deleted having a different count to a
> one that
> does not. The triggers should handle this correctly, but not tried it
> myself.
> hth,
> - Stuart
>
> > -----Original Message-----
> > From: Duncan Adams (DNS) [mailto:duncan(dot)adams(at)vcontractor(dot)co(dot)za]
> > Sent: 30 May 2002 13:37
> > To: 'Tony Griffiths(RA)'; pgsql-novice(at)postgresql(dot)org
> > Subject: Re: [NOVICE] Accessing database statistics
> >
> >
> > o yes i forgot u could try to see
> >
> > EXPLAIN select count(*) from <table>;
> >
> > but i still think tom is u'r best bet for this.
> >
> > Ok, this all depends on how postgresql does this query. If it (behind
> > the scenes) does a call to a system table which holds a field for the
> > count of each table then fine - nice and efficient. However
> > if this does
> > a scan of the appropriate table and counts the number of tuples then
> > returns this figure, then this is a really expensive operation, and I
> > need this to be fast.
> >
> > Tony
> >
> > Duncan Adams (DNS) wrote:
> >
> > >for the first part u might try
> > >
> > >select count(*) from <table>;
> > >
> > >-----Original Message-----
> > >From: Tony Griffiths(RA) [mailto:griffitt(at)cs(dot)man(dot)ac(dot)uk]
> > >Sent: Thursday, May 30, 2002 2:01 PM
> > >To: pgsql-novice(at)postgresql(dot)org
> > >Subject: [NOVICE] Accessing database statistics
> > >
> > >
> > >Hi,
> > >I need to write a query that returns the number of rows
> > currently stored
> > >in a table. I presume that I issue a query against the
> > system tables,
> > >but don't know which one(s) to do this against. So a couple
> > of questions:
> > >
> > >1) With specific reference to my problem, how do I do this?
> > >2) More generally, is there any where that gives detailed
> > descriptions
> > >of the system tables?
> > >
> > >Many thanks,
> > >
> > >
> > >Tony
> > >
> > >
> > >---------------------------(end of
> > broadcast)---------------------------
> > >TIP 6: Have you searched our list archives?
> > >
> > >http://archives.postgresql.org
> > >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>