Re: Accessing database statistics

From: "Tony Griffiths(RA)" <griffitt(at)cs(dot)man(dot)ac(dot)uk>
To: "Joshua b(dot) Jore" <josh(at)greentechnologist(dot)org>
Cc: "Henshall, Stuart - WCP" <SHenshall(at)westcountrypublications(dot)co(dot)uk>, "'Duncan Adams (DNS)'" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Accessing database statistics
Date: 2002-05-30 14:33:05
Message-ID: 3CF63821.4060805@cs.man.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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
>>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Joel Burton 2002-05-30 15:02:31 Re: Enabling Auditing in Postgres
Previous Message Joshua b. Jore 2002-05-30 14:10:57 Re: Accessing database statistics