Skip site navigation (1) Skip section navigation (2)

Re: Accessing database statistics

From: "Henshall, Stuart - WCP" <SHenshall(at)westcountrypublications(dot)co(dot)uk>
To: "'Duncan Adams (DNS)'" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>,"'Tony Griffiths(RA)'" <griffitt(at)cs(dot)man(dot)ac(dot)uk>,pgsql-novice(at)postgresql(dot)org
Subject: Re: Accessing database statistics
Date: 2002-05-30 13:20:34
Message-ID: E2870D8CE1CCD311BAF50008C71EDE8E01F748C8@MAIL_EXCHANGE (view raw or flat)
Thread:
Lists: pgsql-novice
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
> 

Responses

pgsql-novice by date

Next:From: Joshua b. JoreDate: 2002-05-30 14:10:57
Subject: Re: Accessing database statistics
Previous:From: Henshall, Stuart - WCPDate: 2002-05-30 12:52:21
Subject: Re: Enabling Auditing in Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group