Thread: Accessing database statistics

Accessing database statistics

From

"Tony Griffiths(RA)"

Date:

30 May 2002, 11:02:11

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

Re: Accessing database statistics

From

"Duncan Adams (DNS)"

Date:

30 May 2002, 11:07:41

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

Re: Accessing database statistics

From

John Taylor

Date:

30 May 2002, 11:28:21

On Thursday 30 May 2002 13:00, Tony Griffiths(RA) wrote:
> 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?

select count(*) from mytable;

> 2) More generally, is there any where that gives detailed descriptions
> of the system tables?

I always take the view that they are for use by the system, and not for me.
I haven't needed system tables yet.

JohnT

Re: Accessing database statistics

From

"Duncan Adams (DNS)"

Date:

30 May 2002, 11:39:29

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@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
>

Re: Accessing database statistics

From

"Tony Griffiths(RA)"

Date:

30 May 2002, 11:52:55

I tried the explain route, and it looks like the query acts by doing a
complete scan of the table, so not very efficient. Output of the EXPLAIN
was:

 EXPLAIN select count(*) from person;
NOTICE:  QUERY PLAN:

Aggregate  (cost=22.50..22.50 rows=1 width=0)
  ->  Seq Scan on person  (cost=0.00..20.00 rows=1000 width=0)

EXPLAIN

looks like I need to consult the system tables.

Tony


Duncan Adams (DNS) wrote:

>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@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
>>

Re: Accessing database statistics

From

"Tony Griffiths(RA)"

Date:

30 May 2002, 12:16:45

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@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 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>

Re: Accessing database statistics

From

"Henshall, Stuart - WCP"

Date:

30 May 2002, 12:26:38

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
>
> Duncan 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
>

Re: Accessing database statistics

From

"Joshua b. Jore"

Date:

30 May 2002, 12:55:39

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.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
> >
> > Duncan 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
> >
>

Re: Accessing database statistics

From

"Tony Griffiths(RA)"

Date:

30 May 2002, 13:36:44

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