Re: Accessing database statistics - Mailing list pgsql-novice

OK, here's an answer to my own question. I tried:

select reltuples from pg_class where relname = 'person';

and this returned what I wanted. Note that running 'vacuum' first produces the exact answer, whereas failing to do this returns a much larger figure. This seems a better option for my needs than having a redundant field in every table. An alternative to Joshua's reply cold be to have his rule updating a separate table_stats relation, with a foreign key back to the relevant table, but as this seems only to be repeating what the system tables do anyway, I'll maybe follow the system tables route.

Thanks to all for their prompt replies.,

Tony

Joshua b. Jore wrote:

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 1
303 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.adams@vcontractor.co.za]
Sent: 30 May 2002 13:37
To: 'Tony Griffiths(RA)'; pgsql-novice@postgresql.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

Dunc
an Adams (DNS) wrote:

for the first part u might try

select count(*) from <table>;

-----Original Message-----
From: Tony Griffiths(RA) [mailto:griffitt@cs.man.ac.uk]
Sent: Thursday, May 30, 2002 2:01 PM
To: pgsql-novice@postgresql.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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html